what adjustments would i make in excel to complete this problem in excel(using formulas)?

quarter 1. 4. After completing requirements 1-3 above revise the budget spreadsheet to include following changes: Bronco Corp. is contemplating to increase the selling price of the frames by 10% during the high sales months (graduation months) of December- January and April-May. The management of Bronco believes that it would affect unit sales marginally only, thereby, reducing unit sales by 5% in those months. How would these two changes affect the income of Quarter 12 Based on the analysis, should Bronco Corp. increase the price of frames temporarily during those high demand months? Include a short explanation in Excel file itself. (You need to revise your Excel spreadsheet to determine the effect on income statement. If you used your formulas correctly for requirements 1-3, you will not need to make whole lot of changes to your spreadsheet). Save a cony of th 5. B C D E F G H 1 J K PB 8-1 to PB8-3 Given Data: Sales price per unit Linear feet of oak wood per frame Oak wood cost per foot other DM package per frame Other DM package cost per unit Labor hours per unit Labor rate per hour $ 150.00 5 feet $4.00 per foot 1 unit $25.00 per package 0.5 hour $15.00 25% of next month's sales 30% of next month's production need 20% of next month's production need $12.50 per unit $25,000.00 (=$300,000/ 12 months) $31,000.00 $15.00 $300,000.00 annual estimate 20,000.00 frames Ending Finished goods inventory policy Ending inventory policy for Oak wood 3 Ending inventory policy for other DM package 1 5 Variable mfg OH rate per unit produced 6 Fixed mfg OH per month Fixed selling and adm expenses per month 8 Variable selling and adm expenses per unit sold 9 Annual fixed manufacturing overhead 20 Annual production volume in units 21 22 23 Budgeted 24 Sales (in units) 25 x sales price per unit 26 Total Sales Revenue ($) 27 28 29 30 Budgeted Sales (in units) 31 Add: Required Ending Inventory of Fin. Goods 32 Less: Bud. Beginning Inventory of Fin. Goods 33 Budgeted Production (in units) 34 35 36 37 Bud. Production (in units) 38 Oak Wood per frame (linear feet) 39 Oak wood needed for Production (linear feet) Sales Budget Quarter 2 December January February March Quarter 1 April May 2000 1500 800 760 3060 2100 1800 $150.00 $150.00 $150.00 $ 150.00 $150.00 $150.00 $150.00 $300,000.00 $225,000.00 120,000.00 $114,000.00 $459,000.00 315,000.00 270,000.00 Production Budget December January February 2000 1500 800 375 200 190 (375) (200) 1325 790 May 1800 March 760 525 (190) 1095 Quarter 1 3060 525 (375) 3210 3210 April 2100 450 (525) 2025 December March Oak Wood Purchase Budget January February 1,325 790 5.00 5.00 6,625.00 3,950.00 1,095 5.00 5,475.00 Quarter 1 3,210 5.00 16,050.00 April 2,025 5.00 10,125.00 April May 1800 2100 450 (525) 2025 April 2,025 5.00 10,125.00 8 Production Budget -9 December January February March Quarter 1 50 Budgeted Sales (in units) 2000 1500 800 760 3060 81 Add: Required Ending Inventory of Fin. Goods 375 200 190 525 525 32 Less: Bud. Beginning Inventory of Fin. Goods (375) (200) (190) (375) 33 Budgeted Production (in units) 1325 790 1095 3210 34 3210 35 Oak Wood Purchase Budget 36 December January February March Quarter 1 37 Bud. Production (in units) 1,325 790 1.095 3,210 38 Oak Wood per frame (linear feet) 5.00 5.00 5.00 5.00 39 Oak wood needed for Production (linear feet) 6,625.00 3,950.00 5,475.00 16,050.00 40 Add: Ending inventory of Oak wood (linear feet) 1,987.50 1.185.00 1,642.50 3,037.50 3,037.50 41 Less: Beg. Inventory of Oak wood (linear feet) (1.987.50) (1,185.00) (1,642.50) 42 Budgeted Oak wood Purchase (in linear feet) (1.987.50) 5,822.50 4,407.50 6,870.00 17,100.00 43 Oak wood Cost per foot $4.00 $4.00 $4.00 $4.00 44 Total Budgeted Oak wood Purchase ($) $23,290.00 $17,630.00 $27,480.00 $68,400.00 45 $68,400.00 46 Other Direct Material Package Purchase Budget 47 December January February March Quarter 1 48 Bud. Production (in units) 1325 790 1095 3210 49 Other DM package per frame (in units) 1 1 1 1 50 Other DM packages needed for Production (units) 1325 790 1095 3210 51 Add: Ending inventory of other DM packages (un 265 158 219 642 642 52 Less: Beg. Inventory of other DM packages (units) (265) (158) (219) (265) 53 Bud. Purchase of Other DM packages (in units) 1218 851 1518 3587 54 Cost per package $25.00 $25.00 $25.00 $25.00 55 Total Bud. Purchase Cost of other DM packages ($) $30,450.00 $21,275.00 $37,950.00 $89,675.00 56 $89,675.00 57 58 Direct Labor Budget 59 January February March Quarter 1 60 Bud. Production (in units) 1325 790 1095 3210 61 DL hours per frame 0.5 0.5 0.5 0.5 62 Bud. DL Hours needed for Production 662.5 395 547.5 1605 63 DL rate per hour (s) $15.00 $15.00 $15.00 $15.00 Bud. DL Cost (5) 64 $9,937.50 $5,925.00 $8,212.50 $24,075.00 $24.075.00 April 2025 1 2025 3587 CE G H J L M N Bud. Production (in units) DL hours per frame Bud. DL Hours needed for Production DL rate per hour (s) Bud. DL Cost ($) Direct Labor Budget January February March Quarter 1 1325 790 1095 3210 0.5 0.5 0.5 0,5 662.5 395 547.5 1605 $15.00 $15.00 $15.00 $15.00 $9,937.50 $5,925.00 $8,212.50 $24,075.00 $24,075.00 MOH Budget January February March Quarter 1 1325 790 1095 3210 $12.50 $12.50 $12.50 $12.50 $ 16,562.50 $9,875.00 $13,687.50 $40,125.00 $25,000.00 $25,000.00 $25,000.00 $75,000.00 $41,562.50 $34,875.00 $38,687.50 $115,125.00 $115,125.00 DM1 DM2 DL VMOH FMOH Total Mfg. Cost per unit 5 linear feet Oak wood @$4.00 per foot $20.00 One other DM package @ $25 per unit $25.00 0.5 hours @ $15.00 per hour $7.50 Var. MOH per unit $12.50 Fixed MOH per unit ($300,000/20,000 ur $15.00 Mfg. Cost per unit $80.00 COGS Budget January February March Quarter 1 1,500 800 760 3,060 $80.00 $80.00 $80.00 $80.00 $120,000.00 $64,000.00 $60,800.00 $244,800.00 3 Bud. Production (in units) Var. MOH per frame 0 Bud. Total Var. MOH ($) 1 Bud. Total Fixed MOH ($) 2 Bud. TOTAL MOH ($) 23 74 75 76 77 Budgeted Sales 78 * Mfg. cost per unit 79 Bud. COGS 80 81 82 83 84 B5 Budgeted Sales (in units) 86 Variable S&A Expense per unit 87 Budgeted Variable S&A Expense Total B8 Budgeted Fixed S&A Expense Total 89 Budgeted Total S&A Expense 90 91 92 93 Budgeted Sales Revenue 94 Less: Budgeted COGS 95 Gross Margin Selling and Adm. Expense Budget January February March Quarter 1 1500 800 760 3060 $15.00 $15.00 $15.00 $15.00 $22,500.00 $12,000.00 $ 11,400.00 $45,900.00 $45,900.00 $31,000.00 $31,000.00 $31,000.00 $93,000.00 $53,500.00 $43,000.00 $42,400.00 $138,900.00 $ 138,900.00 Budgeted Income Statement January February March Quarter 1 $225,000.00 $120,000.00 114,000.00 $459,000.00 (s120,000,00) ($64,000.00) ($60,800.00) $244.800.00) $105,000.00 $56,000.00 $53.200.00 $214,200.00 Onginal Data Selling and Adm. Expense Budget January February March Quarter 1 Budgeted Sales (in units) 1500 800 760 3060 Variable S&A Expense per unit $15.00 $15.00 $15.00 $15.00 Budgeted Variable S&A Expense Total $22,500.00 $12,000.00 $ 11,400.00 $45,900.00 $45,900.00 3 Budgeted Fixed S&A Expense Total $31,000.00 $31,000.00 $31,000.00 $93,000.00 Budgeted Total S&A Expense $53,500.00 $43,000.00 $42,400.00 $138,900.00 0 $138,900.00 01 Budgeted Income Statement 32 January February March Quarter 1 23 Budgeted Sales Revenue $225,000.00 $120,000.00 114,000.00 $459,000.00 94 Less: Budgeted COGS ($120,000.00) ($64,000.00) ($60,800.00) $244,800.00) 95 Gross Margin $ 105,000.00 $56,000.00 $53,200.00 $214,200.00 96 Less: Bud. S&A Expenses ($53,500.00) ($43,000.00) ($42,400.00) $138,900.00) 97 Net Operating Income (Loss) $51,500.00 $ 13,000.00 $10,800.00 $75,300.00 98 $75,300.00 99 Budgeted Cash Receipts/Collection 100 December January February March Quarter 1 101 Budgeted Sales (s) $300,000.00 $225,000.00 $120,000.00 $114,000.00 $459,000.00 102 Current month's sales collected, 80% $180,000.00 $ 96,000.00 $91,200.00 $367,200.00 103 Prior month's sales collected, 20% $60,000.00 $45,000.00 $ 24,000.00 $129,000.00 104 Budgeted Total Cash collection/receipt $240,000.00 $141,000.00 $115,200.00 $496,200.00 105 $496,200.00 106 Budgeted Cash Payments 107 December January February March Quarter 1 108 Budgeted Oakwood Purchase ($) $34,200.00 $23,290.00 $17,630.00 $27,480.00 $68,400.00 109 Bud. Cash payment toward current month purchase(60%) $13,974.00 $10,578.00 $ 16,488.00 $41,040.00 110 Bud Cash payment toward prior month purchase (40%). $13,680 $2,316 $7,052 $30,048.00 111 Bud. Total Cash payment toward Oakwood purchase $27,654.00 $19.894.00 $23,540.00 $71,088.00 112 Budgeted Purchase of other DM packages ($) (all cash) $30,450.00 $21,275.00 $37,950.00 $89,675.00 113 Bud. Cash payment towards DL cost $9,937.50 $5,925.00 $8,212.50 $24.075.00 114 Budgeted MOH costs $41,562.50 $34,875.00 $38,687.50 $115,125,00 115 Less: Depreciation cost (non cash expense) ($5,000) ($5,000) 65,000) ($15.000) 116 Budgeted S&A Expenses $53,500.00 $43,000.00 $42,400.00 $138.900.00 117 Budgeted Capital Expenditure $ 110,000.00 $0.00 $0.00 $110,000 118 Budgeted Total Cash Payments $268,104.00 $119,969.00 145,790.00 $533,863.00 533,863.00 119 January February March Quarter 1 Budgeted Sales Revenue $225,000.00 $120,000.00 114,000.00 $459,000.00 Less: Budgeted COGS ($120,000.00) ($64,000.00) ($60,800.00) $244.800.00) Gross Margin $ 105,000.00 $56,000.00 $53,200.00 $ 214,200.00 Less: Bud. S&A Expenses ($53,500.00) ($43,000,00) ($42,400.00 $138,900.00) Net Operating Income (Loss) $51,500.00 $13,000.00 $10,800.00 $75,300.00 $75,300.00 Budgeted Cash Receipts/Collection December January February March Quarter 1 Budgeted Sales ($) $300,000.00 $225,000.00 $120,000.00 $114,000.00 $459,000.00 Current month's sales collected, 80% $180,000.00 $96,000.00 $91,200.00 $367,200.00 3 Prior month's sales collected, 20% $60,000.00 $45,000.00 $24,000.00 $129,000.00 4 Budgeted Total Cash collection/receipt $240,000.00 $141,000.00 $115,200.00 $496,200.00 5 $496,200.00 6 Budgeted Cash Payments 07 December January February March Quarter 1 38 Budgeted Oak wood Purchase ($) $34,200.00 $23,290.00 $17,630.00 $27,480.00 $68,400.00 09 Bud. Cash payment toward current month purchase(60%) $13,974.00 $10,578.00 $16,488.00 $41,040.00 10 Bud. Cash payment toward prior month purchase (40%) $13,680 $2,316 $7,052 $30,048.00 11 Bud. Total Cash payment toward Oakwood purchase $27,654.00 $19,894.00 $23,540.00 $71,088.00 112 Budgeted Purchase of other DM packages (s) (all cash) $30,450.00 $21,275.00 $37,950.00 $89,675.00 113 Bud. Cash payment towards DL cost $9,937.50 $5,925.00 $8,212.50 $24,075.00 114 Budgeted MOH costs $41,562.50 $34,875.00 $38,687.50 $ 115, 125.00 115 Less: Depreciation cost (non cash expense) ($5,000) ($5.000) $5,000) ($15.000) 116 Budgeted S&A Expenses $53,500.00 $43,000.00 $42,400.00 $138,900.00 117 Budgeted Capital Expenditure $110,000.00 $0.00 $0.00 $ 110,000 118 Budgeted Total Cash Payments $268,104.00 $119,969.00 $145,790.00 $533,863.00 533,863.00 119 120 Cash Budget 121 December January February March Quarter 1 122 Beginning Cash Balance $61,000 $50,000.00 $53,927.00 $61,000 123 Budgeted Cash Collection/Receipts $240,000.00 $141,000.00 $115,200.00 $496,200.00 124 Less. Budgeted Cash Payments ($268,104.00) 5119,969.00) 145,790.00) $533,863.00) 125 Preliminary Cash Balance $32,896.00 $71,031.00 $23,337.00 $23,337.00 126 Borrowings and Repayments: 127 Borrowing $17,104 0 $26,663.00 $43,767 128 Repayment of Principal 0 ($17,104) ($17,104) 120 Ending Cash Balance $50.000.00 $53.927.00 $50.000.00 $50.000.00 Onginal Dala