Here are the steps: 1. Make sure your budgeted numbers for each budget from Part 1 are accurate based on your instructor's feedback. 2. Starting with the sales budget, enter formulas in the appropriate cells to multiply the sales information by month and to add the sales across to get the total quarter column and row totals. When you insert the formulas, you should get the same accurate budget numbers from step 1. 3. Taking the Sales in Units from the sales budget for each month, use formulas to transfer that information to the row located in the first row of the production budget. Now, take the appropriate cells within the production budget and, using formulas, get the quarterly totals. You should have formulas to get column and row totals. 4. Once you have created the Excel formulas for the production budget, then the direct materials budget, direct labor budget, manufacturing overhead budget, selling budget, and administrative budgets can be linked using a similar process from steps 2 and 3. Within each budget, you will have formulas to multiple totals and formulas adding information to get overall totals. Each of these budgets should have formulas to calculate column and row totals. 5. Once you have inserted all the formulas in the first 6 budgets (steps 2, 3, 4), you must insert formulas into the cost of goods manufactured budget and the budgeted income statement. When you complete the linked formulas for the cost of goods manufactured budget and the budgeted income statement, then those budgets should be completed. Your goal is to link all the budgets together so if any number in the sales budget is changed, all seven budgets after the sales budget will also change: all the way to the budgeted income statement. 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 February.....8,800 units March 9,700 units April .10,100 units May ................11,200 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 S7 per labor hour. F Manufacturing Overhead Costs Budget For the Quarter Ending March 2021 3 1 5 Month January February March Quarter 6 7 Variable Overhead Costs Per unit 8 Factory Supplies 9 Employee Benefits 10 Inspection 11 Maintance and Repair 12 Utilities Total Variable Overhead 13 Costs 14 15 Fixed Overhead Costs 16 Depreciation Machinery 17 Factory Rent 18 Supervision 19 Maintenance and Repair 20 Other overhead expenses 21 Total Fixed Overhead Costs 22 Total Manufctuing 22 Overhead Casts 24 D Selling & Administrative Budget For the Quarter Ending March 2021 Month January February March Quarter Variable S & A Costs Per unit Delivery Expenses Sales Commission Accounting 1 Other Administrative Expenses Total Variable Selling & 2. Administrative Expenses 13 14 Fixed S & A Expenses S Sales Salaries 16 Depreciation (office equipment) 17 Taxes & Insurance Total Fixed Selling & 18 Administrative Expenses 19 Total Selling & Administrative 20 Expenses 21 22 23 Total 1 McMillan Toys 2 Cost of Goods Manufactured 3 For the Quarter Ending March 31 4 5 6 Direct Materials Cost 7 Direct Labor Cost 3 Variable Manufacturing Overhead Cost 9 Fixed Manufacturing Overhead Cost 20 Total Manufacturing Costs 1 2 Costs of Goods Manufactured Per Unit 13 14 15 16 17 18 19 20 McMillan Toys Cost of Goods Manufactured For the Quarter Ending March 31 Sales Revenue Less: Cost of Goods Sold Gross Profit Less: Selling and Administrative Expenses Income From Operations Less: Income Tax Expense 1 Net Income 2 3 4 5 6 17 18 19 20 Variable Overhead Costs (per unit) Fixed Overhead Costs Per month Factory supplies: Employee benefits Inspection Maintenance & repair Utilities $0.10 $0.30 $0.25 $0.20 $0.25 Depreciation (machinery) Factory Rent Supervision Maintenance & repair Other overhead expenses $1,200 $3,100 $1,800 $1,400 $900 Selling and administrative costs are as follows: Variable Selling & Administrative Costs (per unit) Fixed Selling & Administrative Costs Per Month Delivery Expenses Sales Commissions Accounting Other Administrative expenses $0.25 $0.30 $0.20 $0.15 Sales Salaries Depreciation (office equipment) Taxes & Insurance $3,100 $1,550 $1,900