Please answer the last picture in excel, using the data from the third and fourth pictures provided ~Thanks

Nikki started by talking with her parents about some of the estimated sales and costs that they thought would incur for the month of September. Here are some of those estimates: Caramel Total Apple Pies Apples Sales price (per unit) $22.00 $8.00 Expected sales units (for September) 15,000 6,000 9,000 Total $20,150.00 Expected Manufacturing costs: Fixed OH (per month) Direct labor (per unit) Direct materials (per unit) Variable OH (per unit) Apple Pies $16,500.00 $3.50 $2.50 $2.00 Caramel Apples $3,650.00 $2.10 $1.75 $0.75 Expected Marketing and admin costs: Fixed costs (per month) Variable costs (per unit) $5,750.00 $4,500.00 $1.25 $1,250.00 $0.25 Additionally, Sally informed her that the farm received a special order to sell their apple pies and caramel apples at the local county fair in September. Sally wanted to sell 3,250 pies and 4,250 caramel apples for a discounted price of $18.00 and $6.50, respectively. With the amount of staff and the size of the bakery, Nikki determined that the maximum capacity for the bakery is 3,700 labor hours a month. At the current projected sale amounts she expects that they'll need 2,850 hours to meet their regular sales volume. After review of the salaries of all the staff, Nikki determined that the average hourly wage was $14.00. Ned was also concerned about how much it cost to keep apple pie inventory on hand at the end of the month, caramel apples should be sold within a couple days so no inventory is left at the end of the month. Since there is a limited shelf life for the apple pies, Ned and Sally try and keep the ending inventories in line with next month's expectations. They estimated that they'd have to start 7,000 apple pies in order to have 1,000 apple pies in-process at the end of the month and 500 finished pies. At the end of August there were 500 pies that were in-process and no finished pies. The variable costs associated with these inventories were as follows: Beginning WIP inventory September costs DM $1,400 $17,500 Conversion $3,350 $38,500 And the conversion cost for the in-process pies is 50% completed and 100% for direct materials. (Use the expected for September Apple Pie Sales for determining units started and units completed and transferred out) September was a success for the bakery! They were able to complete the special order and with some overtime baked a few more pies. Here were the results for the actual sales and costs at the end of the month. The actuals as presented below are only to be use for Problem 6.) Caramel Total Apple Pies Apples Actual Sales price (per unit) $22.50 $7.00 Actual sales units 17,750 7,250 10,500 Actual variable manufacturing OH $81,625 Actual variable marketing and admin cost $15,125 Actual fixed manufacturing costs $18,000 Actual marketing and admin costs $5,000 2 3 a. Total Profit for the month of September 4 5 Apple Pies Caramel Apple Total 6 Revenue $ 132.000.00 $ 72,000.00 $ 204,000.00 7 Variable costs $ 55,500.00 $ 43.650.00 $ 99,150.00 8 Contribution margin $ 76,500.00 $ 28,350.00 $ 104.850.00 9 Fixed costs $ 25.900.00 10 Operating profit $ 78,950.00 11 12. 13 14 b. Compute the weighted average contribution margin per unit 15 Percentage 16 Weights: Apple Pies Total Sales Weight 17 132.000 204,000 65% 18 Percentage 19 Caramel Apples Total Sales Weight 20 72.000 204 000 35% 21 Contribution Weighted 22 Margin per unit Weight Average CM 23 Apple Pie $ 12.75 65% $ 8.25 24 Caramel Apples $ 3.15 35% $ 1.11 25 26 Weighted average contribution margin per units 9.36 27 28 29 c. Compute breakeven: 30 Fixed Costs Contrib. Margin Breakeven 32 25,900 9.36 2.767 33 Breakeven per Total Breakeven Weight Unit 34 Apple Pie 2,767 65% 1.790 36 Caramel Apples 2,767 976 2.767 36 Prob 1 Prob 2 Prob 3 Prob 4 Prob 5 Prob 6 Analysis 31 35% Apple Hilly Farms a. Labor hours required for special order Product Apple Pies Caramel Apples Laborcos urrunt Wage rate per hour 3.50 $ 14.00 2100 14.00 Labor hours needed for astimated production 1.500.00 1.350.00 Labochours per unit 0.25 015 Totalhos ander quantity Apple Pies 3,250 Caramel Apples 4,250 Capacity used for special order 813 63B 1.450 b. Incremental protuloss) on special order Total Apple Pies Caramel Apple 4.250 5 18.003 8.50 $ 58.500 5 27,625 86 125 Differential venues Special order uns Selling price Incremental revenue Differential costs Special order is Total variable costs Diferential cost Dereal profiss) S 5 3,250 9.255 30,00 4,250 4.85 20, 6135 $ 50.675 35.450 If labor hours for the additional 7,500 units combined, then regular production will need to be reduced for the product with the lowest contribution margin Bakery capacity in direct-abor hours 2 Capacity used for special order Capacity avalable for regular orders Capacity needed for apple pies Capacity needed for caramel apples Hours over capecky for regular orders 3.700 1.450 2 250 1,500 1150 800 c. Compute contribution margin per hour for regular orders Revenus par un Vanable cost per Contribution margin perint Direct-labor hours per un Contribution margin per hour Apple Pies Caramel Aegies 22.00 $ 8.00 5 9.255 485 $ 12.755 3.15 0.15 5 51.00 5 21 00 5 d. Compute contribution margin lost from regular sales $ Lowest contribution margin per hour Hours over capacity for regular orders Toulos comibution margin Total lost production in uns 21.00 600.00 12.000.00 4 000.00 S .. Total contribution margin with special order Angles Caramel Ayias Total S 8.16 165 3.210 4.250 520,43703 7,012,50 36000 Sa Spesin Londer CM perut Number olunte Special Dider CM Regular Production CM per un Number of units Repule producon CM Total controupon margin S 12.75 S 8.000.00 $ 76,600.00 5 5000.00 15.750.0002260.00 127/0000 Reguer production on buton margin 5.10416000 Office in pol with spec order $ 22.850.00 F F G D B AA C Apple Hilly Farms Production Cost Report 3 4 a. Compute total Apple Pie units to be accounted for 5 Equivalent Units Materials Conversion Physical Units 8 9 Flow of production units: Units to be accounted for: Beginning WIP inventory Units started in September Total units to be accounted for Units to be accounted for Total transferred out Units in Ending WIP inventory Total units to be accounted for b. Compute total Apple Pie costs to be accounted for 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Total Costs Materials Conversion Costs to be accounted for: Costs in beginning WIP inventory Current period costs Total costs to be accounted for Costs per equivalent unit: Materials Conversion 27 28 29 30 31 32 Costs accounted for Costs assigned to units transferred out: Materials Conversion Total costs of units transferred out 34 35 26 37 38 39 40 Costs assigned to ending WIP inventory Materials Conversion Total costs of ending WIP inventory Total costs to be accounted for AZ