Can I please get some assistance in understanding how to get the answers using only formulas?
A B G Chapter 9: Using Excel to Make Decisions at Current Designs Parts 1 and 2 Data Budgeted unit sales Quarter 1, 2020 1,000 Quarter 2, 2020 1.500 Quarter 3, 2020 750 Quarter 3, 2020 750 Quarter 1, 2021 1, 100 Quarter 2, 2021 1,500 Desired finished goods ending inventory 20% of next month's sales Desired ending inventory of polyethylene powder next month's production Estimated inventories Finished goods inventory at Dec. 31, 2019 200 kayaks Polyethylene powder at Dec. 31, 2019 19,400 pounds Polyethylene powder at Dec. 31, 2020 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 finishing kits 170.00 per kit Labor rate of type I employees 15.00 per hour 29 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 32 Required Follow the step-by-step directions to prepare selected budgets for Current Designs. CUSTOM DESIGNS Production Budget For the Year Ending December 31, 2017 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total 42 Expected unit sales 1,000 1,500 750 750 4,000 43 Add: Desired ending finished goods units 45 Total required units Less: Beginning finished goods units Required production units 48 49 CUSTOM DESIGNS 50 Direct Materials Budget For the Year Ending December 31, 2017 52 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total 53 Units to be produced 54 Pounds of polyethylene powder per unit 55 Total pounds need for production 56 Add: Desired ending powder inventory 57 Total pounds of powder 58 Less: Beginning inventory of powder 59 Pounds of polyethylene powder to be purchased Cost per pound Cost of powder to be purchased 63 Finishing kits required per unit 64 Cost of finishing kits per unit 65 Cost of required finishing kits 66 Total costs for direct materials 67 68 CUSTOM DESIGNS 69 Direct Labor Budget 70 For the Year Ending December 31, 2017 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total 2 Units to be produced 73 Hours of type | labor per kayak 74 Total number of type | labor hours Hourly rate of type I labor 76 Total cost of type | labor Units to be produced Hours of type II labor per kayak 80 Total number of type II labor hours Hourly rate of type II labor Total cost of type II labor 83 Total cost for direct labor CUSTOM DESIGNS Manufacturing Overhead Budget For the Year Ending December 31, 2017 88 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total 89 Total direct labor costs 90 Manufacturing overhe Manufacturing overhead cost 92 93 CUSTOM DESIGNS 94 Selling and Administrative Expense Budget 95 For the Year Ending December 31, 2017 96 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total 97 Expected unit sales 98 Variable selling and administrative cost 99 per unit 0 Total variable selling and administrative 101 cost WHAT-IF? Please help answer with words Use sheet tab Part 2. 2. Perform what-if analysis to answer the following: Suppose the sales in units for the second quarter are now estimated to be 1,200 units. For the third quarter... ) By how much will the total cost of materials differ? (b) By how much will the total cost of labor differ? (C) What will be the total cost of manufacturing overhead
