Chapter 9: Using Excel to Make Decisions at Current Designs Part 1
Data
Budgeted unit sales
Quarter 1, 2017 1,000
Quarter 2, 2017 1,500
Quarter 3, 2017 750
Quarter 4, 2017 750
Quarter 1, 2018 1,100
Quarter 2, 2018 1,500
Desired finished goods ending inventory 20% of next month’s sales
Desired ending inventory of polyethylene powder 25% of next month’s production
Estimated inventories
Finished goods inventory at Dec. 31, 2016 200 kayaks
Polyethylene powder at Dec. 31, 2016 19,400 pounds
Polyethylene powder at Dec. 31, 2017 15,930 pounds
Production requirements
Polyethylene powder 54 pounds
Finishing kit 1 kit
Type I employees labor time 2 hours
Type II employees labor time 3 hours
Manufacturing overhead 150% of direct labor costs
Cost of polyethylene powder 1.50$ per pound
Cost of finishing kits 170.00$ per kit
Labor rate of type I employees 15.00$ per hour
Labor rate of type II employees 12.00$ per hour
Selling and administrative expenses 45.00$ per unit sold
Selling and administrative expenses 7,500$ per quarter
Required
Follow the step-by-step directions to prepare selected budgets for Current Designs.
Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total
Expected unit sales 1,000 1,500 750 750 4,000
Add: Desired ending finished
Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total
Units to be produced 1,100 1,350 750 820 4,020
For the Year Ending December 31, 2017
CUSTOM DESIGNS
Production Budget
For the Year Ending December 31, 2017
CUSTOM DESIGNS
Direct Materials Budget
Pounds of polyethylene powder to be
purchased 58,225 64,800 41,445 49,140 213,610
Cost per pound 1.50$ 1.50$ 1.50$ 1.50$ 1.50$
Cost of powder to be purchased 87,338 97,200 62,168 73,710 320,415
Finishing kits required per unit 1 1 1 1 1
Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total
Units to be produced 1,100 1,350 750 820 4,020
Hours of type I labor per kayak 2 2 2 2 2
Units to be produced 1,100 1,350 750 820 4,020
Hours of type II labor per kayak 3 3 3 3 3
Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total
Total direct labor costs 72,600$ 89,100$ 49,500$ 54,120$ 265,320$
Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total
Expected unit sales 1,000 1,500 750 750 4,000
Variable selling and administrative cost
CUSTOM DESIGNS
Selling and Administrative Expense Budget
For the Year Ending December 31, 2017
CUSTOM DESIGNS
Direct Labor Budget
For the Year Ending December 31, 2017
CUSTOM DESIGNS
Manufacturing Overhead Budget
For the Year Ending December 31, 2017
Chapter 9: Using Excel to Make Decisions at Current Designs Part 2
Data
Budgeted unit sales
Quarter 1, 2017 1,000
Quarter 2, 2017 1,500
Desired finished goods ending inventory 20% of next month’s sales
Desired ending inventory of polyethylene powder 25% of next month’s production
Estimated inventories
Finished goods inventory at Dec. 31, 2016 200 kayaks
Polyethylene powder at Dec. 31, 2016 19,400 pounds
Polyethylene powder at Dec. 31, 2017 15,930 pounds
Production requirements
Polyethylene powder 54 pounds
Finishing kit 1 kit
Type I employees labor time 2 hours
Type II employees labor time 3 hours
Required
Follow the step-by-step directions to prepare selected budgets for Current Designs.
Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total
Expected unit sales 1,000 1,500 1,200 750 4,450
Add: Desired ending finished
Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total
Units to be produced 1,100 1,440 1,110 820 4,470
For the Year Ending December 31, 2017
CUSTOM DESIGNS
Production Budget
For the Year Ending December 31, 2017
CUSTOM DESIGNS
Direct Materials Budget
Pounds of polyethylene powder to be
purchased 59,440 73,305 56,025 49,140 237,910
Cost per pound 1.50$ 1.50$ 1.50$ 1.50$ 1.50$
Cost of powder to be purchased 89,160 109,958 84,038 73,710 356,865
Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total
Units to be produced 1,100 1,440 1,110 820 4,470
Hours of type I labor per kayak 2 2 2 2 2
Total number of type I labor hours 2,200 2,880 2,220 1,640 8,940
Units to be produced 1,100 1,440 1,110 820 4,470
Hours of type II labor per kayak 3 3 3 3 3
Total number of type II labor hours 3,300 4,320 3,330 2,460 13,410
Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total
Total direct labor costs 72,600$ 95,040$ 73,260$ 54,120$ 295,020$
Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total
Expected unit sales 1,000 1,500 1,200 750 4,450
Variable selling and administrative cost
per unit 45$ 45$ 45$ 45$ 45$
CUSTOM DESIGNS
Selling and Administrative Expense Budget
For the Year Ending December 31, 2017
CUSTOM DESIGNS
Direct Labor Budget
For the Year Ending December 31, 2017
CUSTOM DESIGNS
Manufacturing Overhead Budget
For the Year Ending December 31, 2017