Question

Purpose: The purpose of this project is to help you practice and reinforce the steps necessary to complete a Master Budget, as discussed in Chapter 24. It will also help increase your Excel skills, a program which is often used in business. Task: Complete a Master Budget in Excel using the following assumptions and template using Excel formulas: Master Budget Assumptions Bonus.pdf a Master Budget Bonus TEMPLATE.xlsx Criteria for success: In order to earn all 10 bonus points for this project, you should: 1. Turn in a completed Master Budget in Excel file format. 2. Use formulas in Excel for alt calculations. 3. Budgets should be correct and result in a Balance Sheet that is in balance.

PLEASE HELP! The master budget must be completed using formulas. there must be a sales budget, production budget, Direct Materials budget, Direct labor budget, manufacturing overhead budget, selling and admin budget, budgeted income statement, Schedule of exp collections, sch of exp payments, sch of exp payments DM, cash budget, and budgeted balance sheet. please include the formulas you used.

Answer 1

72 Sales budget 73 74 Particulars 1st quarter 2nd Quarter 3rd Quarter 4th Quarter Total 1st quarter 75 76 6,100 77 Budgeted Unit Sales (A) Budgeted Unit price (B) Budgeted Total Dollars (A*B) 4,100 65 266,500 4,600 65 299,000 5,100 65 331,500 5,600 65 364,000 19,400 65 1,261,000 78 79 80 Production budget 81 82 Particulars 1st quarter 2nd Quarter 3rd Quarter 4th Quarter Total 1st quarter 83 84 6,100 85 86 Budgeted Unit Sales Add Desired Ending inventory @ 20% of next quarter Total Etimated Units Required Less finished Goods Beginning Inventory Units to be produced 4,100 920 5,020 820 4,200 4,600 1,020 5,620 920 4,700 5,100 1,120 6,220 1,020 5,200 5,600 1,220 6,820 1,120 5,700 19,400 1,220 20,620 820 19,800 87 88 89

90 Direct materials purchase budgets 91 92 93 94 95 96 Particulars Units to be produced Per Unit Material needed in production Add Desired Ending inventory of raw Material Total Estimated material Required Less Beginning Inventory of raw material Material to be purchased Cost Per Unit Cost of Material to be purchased 1st quarter 4,200 2 8,400 940 9,340 840 8,500 4 34,000 2nd Quarter 3rd Quarter 4,700 5,200 2 2 9,400 10,400 1,040 1,140 10,440 11,540 940 1,040 9,500 10,500 4 38,000 42,000 4th Quarter 5,700 2 11,400 1,200 12,600 1,140 11,460 4. 45,840 Total 19,800 2 39,600 1,200 40,800 840 39,960 4 159,840 97 98 99 100 101 Direct labor budget 102 103 104 105 106 107 Particulars Units to be produced Labor hours required per unit of finished goods Total labor hours needed Labor rate per Hour Total Direct Labor Cost 1st quarter 4,200 2 8,400 8.00 67,200 2nd Quarter 3rd Quarter 4,700 5,200 2 2 9,400 10,400 8.00 8.00 75,200 83,200 4th Quarter 5,700 2 11,400 8.00 91,200 Total 19,800 2 39,600 8.00 316,800 108 109 110

Manufacturing Overhead budget 112 113 114 115 Particulars 1st quarter 2nd Quarter 3rd Quarter 4th Quarter Total 116 Total direct Labor hours Variable Overhead cost (1.15+1.40+.45+25) Budgeted Variable Overhead Budgeted Fixed Overhead (18000+3600+44100/4+4500) Total Manufacturing Overhead Less Depreciation Cash Paid for Manufacturing Overhead 8,400 3.25 27,300 37,125 64,425 3600 60,825 9,400 3.25 30,550 37,125 67,675 3600 64,075 10,400 3.25 33,800 37,125 70,925 3600 67,325 11,400 3.25 37,050 37,125 74,175 3600 70,575 39,600 3.25 128,700 148,500 277,200 14400 262,800 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 Selling and administrative expense budget Particulars 1st quarter 2nd Quarter 3rd Quarter 4th Quarter Total Units to be Sold Variable Selling expenses (2 +1) Total variable expenses 4,100 3.00 12,300 4,600 3.00 13,800 5,100 3.00 15,300 5,600 3.00 16,800 19,400 3.00 58,200

132 133 134 135 136 137 138 139 Fixed Selling expenses Advertising Sales Salaries Office Manager Depreciation Property Taxes Total fixed Expenses Total Selling & adminstration expenses less Depreciation Cash Paid 3,500 10,000 7,500 1,400 1,250 23,650 35,950 1,400 34,550 3,500 10,000 7,500 1,400 1,250 23,650 37,450 1,400 36,050 3,500 10,000 7,500 1,400 1,250 23,650 38,950 1,400 37,550 3,500 10,000 7,500 1,400 1,250 23,650 40,450 1,400 39,050 14,000 40,000 30,000 5,600 5,000 94,600 152,800 5,600 147,200 140 141 142 143 144 145 146 147 Note; As per rule, we are allowed to answer first four parts however I have answered 6 parts.