Requirements: You are required to create a 12-month budget (except as noted) wit

Question

Requirements:
You are required to create a 12-month budget (except as noted) with monthly columns and
one 2016 total column for each of the following items (on the following tabs listed in
parenthesis):
1. Sales Budget (Sales)
2. Production Budget (Production)
3. DM Purchases Budget (DM Purchases)
4. DL Budget (DL)
5. OH Budget (OH)
6. Selling and Administrative Expense Budget (SellingAdmin)
7. Ending FGI Budget (FGI; more of a calculation than a 12-month “Budget”; Base
Costs on December costs)
8. COGS Budget (COGS; more of a calculation than a 12-month “Budget”; you may
assume the Beginning Inventory cost per unit is equal to the cost per unit calculated
in the Ending FGI Budget)
9. Budgeted Income Statement (IS)
10. Cash Budget (Cash)
11. Direct Material Variances* (MatVar; Variances for Part #K298 only for the whole
year, not monthly)
12. Direct Labor Variances* (DLVar; Variances for whole year, not monthly; use
annual weighted average budgeted rate)
13. Variable OH Variances* (VOHVar; Variances for whole year, not monthly)
14. Fixed OH Variances* (FOHVar; Variances for whole year, not monthly)
* for variance calculations in #’s 11-14 make sure to insert formulas to determine rules whether
variances are “U” or “F” (using IF statements). Also, use formulas to check whether the sum of
DM
MPVMUV

DM

ALB CD EFG HIJ KLM ON o p a 2016 Budget information 16,000 15,000 194,000 $110.00 $110.00 $110.00 $110.00 $115.00 $115.00 $120.00 $12000 $12000 $125.00 $125.00 $125.00 $125.00 3 Projected sales Units (Projected selling price 5 Beg Inventory Units 01/01/16 5 Desired Ending inventory $125.00 20.00% of next month's sales units 3 Direct Materials a Pant AK298; O Part AK298 Per-Unit Usage 1 Part OK298 cost 2 Beg Inventory Qty 01/01/16 3 Desired Ending inventory k 4 Part HC30: 5 Part AC30 Per-Unit Usage 6 Part #C30 cost 7 Beg inventory aty 01/01/16 3 Desired Ending inventory % 200 per unit 54.00 per unit of HK298 6,720 units of #K298 30.00% of next month's production needs 57.00 per unit of HC30 10,080 units of HC30 30.00% of next month's production needs O Direct Labor 1 |DL Hrs per Unit 2 DL Avg Cost per DLH 150 per unit $2000 so . Fixed Va (per DLH) 5 Supplies .6 Power .7 (Maintenance 8 Supervision 9 (Depreciation Taxes 1 Other $12,500 $14,000 S45,000 $4,300 $86,000 $0.20 $110 $0.00 $0.00 So.00 3 Selling & Admin: 4 salaries 5 (Commissions 5 Depreciation Shipping 8 (Other so $140 $25000.00 $3.60 $137000 $160 -o [Planned Capital Expenditures So S63,000 So So So $500000 So So So So So So I $25,000 -2 Cash: 3 Beg Balance 01/01/16 4 Minimum Monthly Balance 5 Minimum Cash Borrow 5 Annual Interest Rate -7 Monty Interest Rate 9 Actuals (use in Variances; 0 Annual Sales in S) 1 Annual Sales Units 2 Annual Production Units 1200% (use below rate for monthly interest calculations) 1.00% (use this for monthly rate) 198,000 200,000 I $1,950,000 Annual $1,224500 Annual 4 (Actual FMOH 5 Actual VMOH 6 Actual DL Hours 7 |Total DL Cost $6,665,000 Annual I 9 Pant AK298 purchase units 0 (Part WK298 total cost 405,000 Annual $1579,500 Total I T DATA sales Production DM Purchases | DL | Oh SelingAdmin | PGI | coGs is Cash Matvar Dlvar voHvar | FOHVar

Explanation / Answer

1. Sales Budget 2016 2016 January February March April May June July August September October November December Total Sales units 10000 10500 13000 16000 18500 20000 20000 20000 18000 17000 16000 15000 194000 Selling Price($) 110 110 110 110 115 115 120 120 120 125 125 125 Total sales ($) 1100000 1155000 1430000 1760000 2127500 2300000 2400000 2400000 2160000 2125000 2000000 1875000 22832500 2. Production Budget 2016 2016 January February March April May June July August September October November December Total Sales units 10000 10500 13000 16000 18500 20000 20000 20000 18000 17000 16000 15000 194000 Desired ending Inventory 2100 2600 3200 3700 4000 4000 4000 3600 3400 3200 3000 2800 2800 (20% of next month's sale) Total units neded 12100 13100 16200 19700 22500 24000 24000 23600 21400 20200 19000 17800 196800 Less: Begining invntory 900 2100 2600 3200 3700 4000 4000 4000 3600 3400 3200 3000 900 Units to produce 11200 11000 13600 16500 18800 20000 20000 19600 17800 16800 15800 14800 195900 3. DM purchase budget 2016 2016 Part K298 January February March April May June July August September October November December Total Units produced (From 2 above) 11200 11000 13600 16500 18800 20000 20000 19600 17800 16800 15800 14800 195900 DM per unit 2 2 2 2 2 2 2 2 2 2 2 2 2 DM required for production 22400 22000 27200 33000 37600 40000 40000 39200 35600 33600 31600 29600 391800 Add:Desired ending inventory 6600 8160 9900 11280 12000 12000 11760 10680 10080 9480 8880 8280 8280 (30% of next month's production needs) Total units needed 29000 30160 37100 44280 49600 52000 51760 49880 45680 43080 40480 37880 400080 Less: BeginningInventory 6720 6600 8160 9900 11280 12000 12000 11760 10680 10080 9480 8880 6720 DM to be purchased 22280 23560 28940 34380 38320 40000 39760 38120 35000 33000 31000 29000 393360 Cost per unit     ($) 4 4 4 4 4 4 4 4 4 4 4 4 4 Total K298 purchase cost 89120 94240 115760 137520 153280 160000 159040 152480 140000 132000 124000 116000 1573440 2016 2016 Part C30 January February March April May June July August September October November December Total Units produced (From 2 above) 11200 11000 13600 16500 18800 20000 20000 19600 17800 16800 15800 14800 195900 DM per unit 3 3 3 3 3 3 3 3 3 3 3 3 3 DM required for production 33600 33000 40800 49500 56400 60000 60000 58800 53400 50400 47400 44400 587700 Add:Desired ending inventory 16500 20400 24750 28200 30000 30000 29400 26700 25200 23700 22200 20700 20700 (50% of next month's production needs) Total units needed 50100 53400 65550 77700 86400 90000 89400 85500 78600 74100 69600 65100 608400 Less: BeginningInventory 10080 16500 20400 24750 28200 30000 30000 29400 26700 25200 23700 22200 10080 DM to be purchased 40020 36900 45150 52950 58200 60000 59400 56100 51900 48900 45900 42900 598320 Cost per unit     ($) 7 7 7 7 7 7 7 7 7 7 7 7 7 Total C30 purchase cost 280140 258300 316050 370650 407400 420000 415800 392700 363300 342300 321300 300300 4188240 4. Direct Labor Budget 2016 January February March April May June July August September October November December Total Units produced 11200 11000 13600 16500 18800 20000 20000 19600 17800 16800 15800 14800 195900 Direct labor hours per unit 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 Total direct labor hours 16800 16500 20400 24750 28200 30000 30000 29400 26700 25200 23700 22200 293850 Direct labor average cost per unit 20.00 20.00 20.00 20.00 20.00 20.00 22.00 22.00 22.00 22.00 22.00 22.00 Total Direct labor cost 336000 330000 408000 495000 564000 600000 660000 646800 587400 554400 521400 488400 6191400 5. Overhead Budget 2016 January February March April May June July August September October November December Total Budgeted direct labor hours 16800 16500 20400 24750 28200 30000 30000 29400 26700 25200 23700 22200 293850 Variable overhead per direct labor hour ** 3.90 3.90 3.90 3.90 3.90 3.90 3.90 3.90 3.90 3.90 3.90 3.90 3.90 Total variable overhead 65520 64350 79560 96525 109980 117000 117000 114660 104130 98280 92430 86580 1146015 Fixed overhead * 161800 161800 161800 161800 161800 161800 161800 161800 161800 161800 161800 161800 1941600 Total budgeted overhead cost 227320 226150 241360 258325 271780 278800 278800 276460 265930 260080 254230 248380 3087615 Overhead Fixed * Variable per DLH ** Supplies 0 1.00 Power 0 0.20 Maintenance 12500 1.10 Supervision 14000 0.00 Depreciation 45000 0.00 Taxes 4300 0.00 Others 86000 1.60 Total 161800 3.90 6. Selling and Administration Budget 2016 January February March April May June July August September October November December Total Budgeted sales units 10000 10500 13000 16000 18500 20000 20000 20000 18000 17000 16000 15000 194000 Variable overhead per direct labor hour ** 6.60 6.60 6.60 6.60 6.60 6.60 6.60 6.60 6.60 6.60 6.60 6.60 6.60 Total variable overhead 66000 69300 85800 105600 122100 132000 132000 132000 118800 112200 105600 99000 1280400 Fixed overhead * 250500 250500 250500 250500 250500 250500 250500 250500 250500 250500 250500 250500 3006000 Total budgeted selling and administration 316500 319800 336300 356100 372600 382500 382500 382500 369300 362700 356100 349500 4286400 Selling and administration Fixed * Variable per DLH ** Salaries 88500 0.00 Commissiion 0 1.40 Depreciation 25000 0.00 Shipping 0 3.60 Others 137000 1.60 Total 250500 6.60 7. Ending FGI budget Calculation of unit cost Qty Cost ($) Per unit ($) Direct material    Part K298 2 4.00 8.00    Part C30 3 7.00 21.00 Direct labor 1.5 20.00 30.00 Overhead Variable 1.5 3.90 5.85 Fixed ( 1941600 / 195900) 9.91 Total unit cost 74.76 Ending inventory 2800 Ending Finished goods value 209331 8. COGS budget DM used (Schedule 3) Qty Cost ($) Total K298 391800 4 1567200 C 30 587700 7 4113900 Direct labor used (Schedule 4) 293850 22 6464700 Overhead (schedue 5) 3087615 Budgeted manufacturng costs 15233415 Add: Beginning FGI 900 74.76 67284 15300699 Less: Ending FGI 209331 COGS 15091368 9. Budgeted Income statement for 2016 Sales (Schedule !) 22832500 Less: COGS 15091368 Gross margin 7741132 Less: S & A Expenses 4286400 Total budgeted NIBT 3454732

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.