Question

Baggy Company has the following information related to the production of handbags for the 2nd quarter of 2017:

• Budgeted sales volume April 150 bags • Budgeted sales volume May 175 bags • Budgeted sales volume June 160 bags • Selling price per bag $45 • Cost of leather per yard $7 • Leather per bag 1.5 yards • Cost of direct labor per hour $10 • Direct labor per bag 0.5 hour • Manufacturing overhead cost per bag $5.50 • Desired ending inventory for bags is 20% of current month’s sales units • Desired ending inventory for leather is 10% of current month’s Direct material yards required for production • Ending inventory of leather at March 31 was 18 yards (use as Beginning inventory for April) • Ending inventory of bags at March 31 was 25 bags (use as Beginning inventory for April) • Variable selling and admin cost per bag $5 • Fixed selling and admin cost per month $1,550 • Income tax rate $25%

• Using the data above, put together the Master Budget (use the format provided on page 2 of instructions) in Excel from a new workbook for Baggy Company – include:

o Sales Budget (for April, May, June and the entire 2nd quarter of 2017) o Production Budget (for April, May, June and the entire 2nd quarter of 2017) o Direct Materials Budget (for April, May, June and the entire 2nd quarter of 2017) o Direct Labor Budget (for April, May, June and the entire 2nd quarter of 2017) o Budgeted Income Statement (for 2nd Quarter only) ? First sheet of workbook should contain data section ? Set up each budget in its own worksheet and link cells to data sheet or to previous budget where appropriate (Example: Budgeted Sales Units from Sales Budget should be linked to Production Budget) ? All numbers should have appropriate formatting o Data sheet and all budgets should be set up so that one change to the data section will automatically recalculate Net Income • Format each worksheet to be visually organized and appealing and print to one page • Add your name to the bottom right-hand footer of each sheet • Rename your completed Excel file to include your first initial and last name at the end of the file name (example: Acc 255 Graded Assignment #10 L. Akeo) • Submit your completed Excel file back to the Assignment in Laulima by the due date indicated in the assignment.

Grading Rubric Requirements Maximum Points Budgets are in good form 10 Budget totals are accurately calculated using appropriate formulas in Excel 15 All sheets are linked from data sheet to Income Statement 15 Each sheet prints to one page and is visually organized and appealing 5 Add your name to each footer and to file name 5 Total points 50 Sales budget Budgeted sales units x selling price per bag = Total budgeted sales dollars Hint: Total sales for the entire 2nd quarter should come out to $21,825. Production Budget Budgeted sales units + Desired ending inventory of bags (20% of current months’ sales units) (-) Beginning inventory (20% of last month’s sales units) = Required production units (bags) Hint: Required production units for the entire 2nd quarter should come out to 492 units (bags). Direct Materials Budget Required production units (bags) x Direct material yards per bag = Direct material yards required for production + Desired ending inventory (10% of current month’s Direct material yards required for production) (-) Beginning inventory (10% of last month’s Direct material yards required for production) = Required purchases of direct material yards x Material cost per yard = Total cost of direct material purchases Hint: Total cost of direct material purchases for the entire 2nd quarter should come out to $5,204.85. Direct Labor Budget Required production units x DL hours per bag Required DL hours x Standard DL cost per hour Total DL cost

Hint: Total direct labor cost for the entire 2nd quarter should come out to $2,460. Budgeted Income Statement Total sales Less: COGS [(Total cost per unit* x total units sold for the quarter) = Gross profit Less: Selling and admin (Variable cost per bag x total bags sold) + Fixed costs for the quarter = Income before taxes Less: Income tax expense = Net income *To get Total cost per unit = [(Cost of leather per yd x yds needed per bag) + (DL cost per hr x DL hrs per bag) + Manufacturing OH cost per bag] Hint: Net income should come out to $3,423.75.

Answer 1

1. Baggy Company

Sales Budget

For the quarter ended June 30:

	April	May	June	Quarter
Budgeted Sales in Units	150	175	160	485
Sales Price per Unit	$ 45	$ 45	$ 45	$ 45
Budgeted Sales Revenue	$ 6,750	$ 7,875	$ 7,200	$ 21,825

2. Production Budget:

	April	May	June	Quarter
Budgeted Sales in Units	150	175	160	485
Desired ending inventory	30	35	32	32
Total Needs	180	210	192	517
Less: Beginning Inventory	25	30	35	25
Budgeted Production in Units	155	180	157	492

3. Direct Materials Budget:

	April	May	June	Quarter
Leather required in production ( 1.5 yards per bag)	232.5	270	235.5	738
Desired ending inventory	23.25	27	23.55	23.55
Total direct material needed	255.75	297	259.05	761.55
Less: Beginning inventory	18	23.25	27	18
Budgeted Purchases in yards	237.75	273.75	232.05	743.55
Cost per yard	$ 7	$ 7	$ 7	$ 7
Cost of Direct Materials Purchases	1,664.25	1,916.25	1,624.35	5,204.85

4. Direct Labor Budget:

	April	May	June	Quarter
Labour Hours needed in production	77.5	90	78.5	246
Labor hour rate	$ 10	$ 10	$ 10	$ 10
Budgeted Labor Cost	$ 775	$ 900	$ 785	$ 2,460

5. Baggy Company

Budgeted Income Statement

For the quarter ended June 30, 2017

	$
Sales	21,825
Cost of Goods Sold	10,185
Gross Profit	11,640
Selling and Administrative Expenses ( 485 x $ 5 + $ 1,550 x 3)	7,075
Income before taxes	4,565
Income tax expense ( 25 %)	1,141.25
Net Income	3,423.75