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 the components equal the total (for instance insert a formula that would check whether total DM variance equals the sum of MPV and MUV).
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 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
