Question

Instruction: Complete a Master Budget in Excel using the following assumptions and template using Excel formulas

SHOW ME ALL THE FORMULAS PLEASE! THAT'S THE MOST IMPORTANT PART. THANK YOU. I've already done the last 3. Please check.

Sales Budget Expected sales volume: 4,100 units in the first quarter with 500-unit increases in each succeeding quarter. Sales price: $65 per unit. Production Budget Company can meet future sales needs with an ending inventory of 20% of next quarter's budgeted sales volume. Direct Materials Budget Company maintains an ending inventory of raw materials equal to 10% of the next quarter's production requirements. The manufacture of each unit requires 2 pounds of raw materials, and the expected cost per pound is $4. Assume that the desired ending direct materials amount is 1,200 pounds for the fourth quarter of 2020. Direct Labor Budget Two hours of direct labor are required to produce each unit of finished goods. The anticipated hourly wage rate is $8. Manufacturing OH Budget Company expects variable costs to fluctuate with production volume on the basis of the following rates per direct labor hour: indirect materials $1.15, indirect labor $1.40, utilities $0.45, and maintenance $0.25. Supervisory salaries are $18,000 per quarter. Depreciation is $3,600 per quarter. Property taxes for the year are $44,100, paid evenly over each quarter. Flat maintenance fee of $4,500 is paid each quarter. Selling and Admin Expense Budget Variable expense rates per unit of sales are sales commissions $2 and freight-out $1. Variable

Answer 1

1. Sales Budget -

Sales Budget Quarter 1 Quarter 2 Quarter 3 Quarter 4 4,100 4,600 5,100 5,600 $ 65 $ 65 $ 65 $ 65 $ Budget Sales Units Selling Price Year 19,400 65 Total Sales $ 266,500 $ 299,000 $331,500 $364,000 $1,261,000

2. Schedule of Expected Cash Collection -

$ Accounts Receivable Quarter 1 Quarter 2 Quarter 3 Quarter 4 Schedule of Expected Cash Collection Quarter 1 Quarter 2 Quarter 3 Quarter 4 Year $ 70,000 70,000 $ 159,900 $ 106,600 $ 266,500 $ 179,400 $ 119,600 $ 299,000 $ 198,900 $ 132,600 $ 331,500 $ 218,400 $ 218,400 Total Cash Collection $ 229,900 $ 286,000 $ 318,500 $ 351,000 $1,185,400

3. Production Budget -

Budget Sales Units Ending Inventory Total Need (-) Beginning Inventory Production Budget Quarter 1 Quarter 2 Quarter 3 Quarter 4 4,100 4,600 5,100 5,600 920 1,020 1,120 1,220 5,020 5,620 6,220 6,820 820 920 1,020 1,120 Year 19,400 1,220 20,620 820 Production Requirement 4,200 4,700 5,200 5,700 19,800

4. Direct Material Budget -

Production Requirement Cost per Pound Required Total Material Required (+) Ending Material Total Needs (-) Beginning Material Total Direct Material Purchase Cost per Pound Direct Material Budget Quarter 1 Quarter 2 Quarter 3 Quarter 4 4,200 4,700 5,200 5,700 2 2 2 2 8,400 9,400 10,400 11,400 940 1,040 1,140 1,200 9,340 10,440 11,540 12,600 840 940 1,040 1,140 8,500 9,500 10,500 11,460 $ 4 $ 4 $ 4 $ 4 $ Year 19,800 2 39,600 1,200 40,800 840 39,960 4 Cost of Direct Material Purchase $ 34,000 $ 38,000 $ 42,000 $ 45,840 $ 159,840

5. Schedule of Expected Cash Payment to Direct Material -

Accounts Payable Quarter 1 Quarter 2 Quarter 3 Quarter 4 Schedule of Expected Cash Payment to Direct Material Quarter 1 Quarter 2 Quarter 3 Quarter 4 Year $ 11,700 $ 11,700 $ 17,000 $17,000 $ 34,000 $ 19,000 $ 19,000 $ 38,000 $ 21,000 $ 21,000 $ 42,000 $ 22,920 $ 22,920 Total Cash Payments $ 28,700$ 36,000 $40,000 $43,920 $ 148,620

6. Schedule of Expected Cash Payment to Direct Labour -

Schedule of Expected Cash Payment to Direct Labour Quarter 1 Quarter 2 Quarter 3 Quarter 4 Production Requirement 4,200 4,700 5,200 5,700 Hours Required each unit 2 2 2 2 Direct labour required 8,400 9,400 10,400 11,400 Cost per Hour $ 8 $ 8 $ 8 $ 8 $ Year 19,800 2 39,600 8 Cash Payment of Direct Labour $ 67,200 $ 75,200 $ 83,200 $ 91,200 $ 316,800

7. Schedule of Expected Cash Payment to Manufacturing OH -

Schedule of Expected Cash Payment to Manufacturing OH Quarter 1 Quarter 2 Quarter 3 Quarter 4 Year Indirect Material $ 9,660 $ 10,810 $ 11,960 $ 13,110$ 45,540 Indirect Labour $ 11,760 $ 13,160 $ 14,560 $ 15,960 $ 55,440 Utilities $ 3,780 $ 4,230 $ 4,680 $ 5,130 $ 17,820 Maintenance $ 2,100 $ 2,350 $ 2,600 $ 2,850 $ 9,900 Supervisory Salary $ 18,000 $ 18,000 $ 18,000 $ 18,000 $ 72,000 Property Taxes $ 44,100 $ 44,100 $ 44,100 $ 44,100 $ 176,400 Flat Maintenance Fees $ 4,500 $ 4,500 $ 4,500 $ 4,500 $ 18,000 Payment to Manufacturing OH $ 93,900 $ 97,150 $ 100,400 $ 103,650 $ 395,100

8. Schedule of Expected Cash Payment to Selling and Admin Exp. -

Schedule of Expected Cash Payment to Selling and Admin Exp. Quarter 1 Quarter 2 Quarter 3 Quarter 4 Year Sales Commission $ 8,200 $ 9,200 $ 10,200 $ 11,200 $ 38,800 Freight Out $ 4,100 $4,600 $ 5,100 $ 5,600 $ 19,400 Advertising Expense $ 14,000 $ 14,000 $ 14,000 $ 14,000 $ 56,000 Sales Salaries $ 10,000 $10,000 $10,000 $10,000 $ 40,000 Office Manager Salary $ 7,500 $ 7,500 $ 7,500 $ 7,500 $ 30,000 Property Taxes $ 1,250 $ 1,250 $ 1,250 $ 1,250 $ 5,000 Payment to Selling and Admin Exp. $ 45,050 $ 46,550 $ 48,050 $ 49,550 $ 189,200

Working Notes -

Working Notes Quarter 1 Quarter 2 Quarter 3 Quarter 4 Quarter 1 6,100 Budget Sales Units Selling Price 4,100 65 $ 4,600 65$ 5,100 65 $ 5,600 65 $ $ 65 Total Sales $ 266,500 $ 299,000 $ 331,500 $ 364,000 $396,500 20% Ending Inventory Beginning Inventory 920 820 1,020 920 1,120 1,020 1,220 1,120 Production 4,200 4,700 5,200 5,700 Raw Material Required 2 8,400 9,400 10,400 11,400 10% Ending Material Beginning Material 940 840 1,040 940 1,140 1,040 1,200 1,140 11,460 Direct Material Purchase Cost per Pound 8,500 4 $ 9,500 4 $ 10,500 4 $ $ 4 Cost of Material $ 34,000$ 38,000 $ 42,000 $ 45,840 Direct Labour Hours Required each unit Wages per Hour IN 8,400 8 $ 9,400 8 $ 10,400 8 $ 11,400 8 $ Direct Labour Cost $ 67,200 $ 75,200 $ 83,200 $ 91,200 Manufacturing OH Indirect Material Indirect Labour Utilities Maintenance $ 1.15$ 9,660 $ 10,810 $ 11,960 $ 13,110 $ 1.40 $ 11,760 $ 13,160 $ 14,560 $ 15,960 $ 0.45 $ 3,780 $ 4,230$ 4,680$ 4,680 $ 5,130 $ 0.25 0.25 $ 2,100 $ 2,350 $ 2,600 $ 2,850 Supervisory Salary Depreciation Property Taxes Flat Maintenance Fees $ 18,000 $ 18,000 $ 18,000 $ 18,000 $ 3,600$ 3,600$ 3,600 $ 3,600 $ 44,100 $ 44,100 $ 44,100 $ 44,100 $ 4,500 $ 4,500 $ 4,500 $ 4,500 2 1 Selling and Admin Exp Sales Commission Freight Out Advertising Expense Sales Salaries Office Manager Salary Depreciation for Office Equipments Property Taxes $ 8,200 $ 9,200 $ 10,200 $ 11,200 $ 4,100 $ 4,600 $ 5,100 $ 5,600 $ 14,000 $ 14,000 $ 14,000 $ 14,000 $ 10,000 $10,000 $10,000 $ 10,000 $ 7,500 $ 7,500 $ 7,500 $ 7,500 $ 1,400 $ 1,400 $ 1,400 $ 1,400 $ 1,250 $ 1,250 $ 1,250 $ 1,250 5000 Additional Items: Interest Expense Income Taxes $ 150 $ 10,000