You also gathered the following variable and fixed overhead and selling and administrative costs: Variable Overhead Costs (per unit) Fixed Overhead Costs Per month Factory supplies: $0.10 Depreciation (machinery) $1,200 Employee benefits $0.30 Factory Rent $3,100 Inspection $0.25 Supervision $1,800 Maintenance & repair $0.20 Maintenance & repair $1,400 Utilities $0.25 Other overhead expenses $900 Selling and administrative costs are as follows: Variable Selling & Administrative Costs Fixed Selling & Administrative Costs Per Month (per unit) Delivery Expenses $0.25 Sales Salaries $3,100 Sales Commissions $0.30 Depreciation (office equipment) | $1,550 Accounting $0.20 Taxes & Insurance $1,900 Other Administrative expenses | $0.15 Michael Dorsey agrees with your recommendation and gave you the project to prepare the master budget for the first quarter of the upcoming year along with a budgeted income statement. He reminded you that a complete master budget has the following budgets included: Sales Budget, Production Budget, Direct Materials Purchases Budget, Direct Labor Budget, Mamifacturing Overhead Budget, Selling & Administrative Expense Bucdget, and Cost of Goods Manufactured Budget. He also requested a budgeted income statement and informed you that the income tax expense is 30% You have been working with various departments to gather financial data to assemble all eight budgets into a master budget using Excel so that you can connect all the budgets together. Budget Data: Each department provided you with the following information: Each unit is sold for $12 per unit. Expected sales (in units) are as follows: January ......8,400 units April .............. 10,100 units February ....8,800 units May ....ccoeneee. 11,200 units March......... 9,700 units The production manager's policy is that each month's ending finished goods inventory is 25% of the next month's budgeted sales. The purchasing manager informed you that each unit takes 3 pounds of direct materials at a cost of $1.50 dollars per pound. The purchasing manager would like to have 20% of next quarter's budgeted production needs for ending direct materials inventory. The labor manager determined that each unit will take 0.2 labor hour to manufacture at a cost of $7 per A B C D E F G H K M N O P Q R S UT Month January February March Quarter Sales in units 8.400 8,800 9,700 26,900 Plus desired units of ending finished 8 goods inventory* 2200 2,425 2,525 2525 March ending balance is 25% of April's Sales budget 9 Desired total Units 10,600 11,225 12,225 29,425 Sales Budget'!B12+(25%*Sales Budget'!B13)-'Production Budget'!D8)*3)*20% Less desired units of beginning finished 10 goods inventory (2,200) (2,425) Whereis the beginning budget for January?> 11 12 Total Units to Produce 10,600 13,425 14,650 29,425 Incorrect 13 14 *Desired Ending Finished Goods Inventory 15 16 Budgeted Production Needs for First Quarter 29,425 When entering the beginning inventory, remember that the prior month's ending inventory will equal the next month's beginning inventory. The beginning inventory for January needs to be calculated using your understanding of the 19 desired ending inventory. 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37D31 viXVAy A B C D E F G H K L M N O P McMillan Toys W N P Sales Budget For the Quarter Ending March 31 Sales Budget'!B12+(25%*Sales Budget'!B13)-'Production Budget'!D8)*3)*20% Month January February March Quarter Sales in Units 8,400 8,800 9,700 26,900 Selling Price per Unit $ 12.00 $ 12.00 $ 12.00 $ 12.00 Total Sales $ 100,800 $ 105,600 $ 116,400 322,800 10 11 12 Budgeted Sales in Units for Apt 10,100 13 Budgeted Sales in Units for Ma 11,200 14 15 16 17 18 19 20 21 22 23 24 25 26