Need help filling out the pink blanks, i filled out the relevant information just can't seem to get the new prices correctly, it doesn't balance out when i plug it in a later tab on excel

12 13 14: 15 16 17: 18 19 20 21 22 23 24 25 26 27 23 29 30: 31 32 33: 34 35: 36 37: 33 39 40 41 42 43 44 Sales Patterned Glasses Paperweights _ Wrapped tumblers Vases Total WeightfPiece 0.50 0.90 0.50 0.60 Purchased - 200 lbs per week x 40 weeks Waste 96 of waste Materials: Total lbs in FG Cost per lb Product Patterned Glasses Paperweights _ Wrapped tumblers Vases Product Patterned Glasses Papenueights _ Wrapped tumblers Vases 5 857.20 1,572 5 0.55 Old Price 5 9.00 5 15.00 5 8.00 5 25.00 Minutes per unit 13 30 13 30 Total minutes to make products Available minutes (30 hours )4 60 minutes x 40 weeks) Productivity Ratio ll of piecesfyr 760 400 1230 320 2,750 VC per unit 5 5 5 5 1.19 1.41 1.19 1.25 Units 760 400 1,280 320 330 360 640 192 1,572 3,000 5,423 30.4% Operating Exp: Total Pieces 0E per piece CM per unit 5 7.81 5 13.59 5 6.81 5 23.75 Total Minutes 13,530 12,000 23,040 9,500 53,320 72,000 31% 5 5 5 5 5 ' Total lbs of DM ' 1c of Materials ' 207.21 196.31 343.99 104.70 mum-m 857.20 S CK: 30.4% 2,533.45 2,750 0.92 135 305 18$ 305 Product Mix 23% 21% 40% 16% 100% Ck: 31% Variable OE 699.00 367.39 1,177.25 294.31 2,533.45 minutes per unit CM per minute 0.434 0.453 0.373 0.792 Total Variable vc per Unit 5 5 5 5 905.21 554.20 1,525.24 399.01 3,395.55 Bank l-'J-'hNU-l 5 5 5 5 1.19 1.41 1.19 1.25 A B C D E G 2 3 3. FILL IN PINK AREAS ONLY What is his breakeven point in CM per minute? Explain to Mr. Giberson 4 what it means in terms of setting prices. Do any of the product lines breakeven on a CM per minute 5 basis? 6 7 8 9 (3) Fixed costs $ 47,266 (From 1.) 10 Total minutes Mr. Giberson worked in production 58,320 (From 2.) 11 (3) Breakeven CM per minute = FC/minutes available $ 0.81 12 13 14 Do any of the product lines breakeven on a CM per minute basis? (Refer back to tab 2, column G rows 29 thru 32) 15 16 17 18 19 204. Prices New Prices (Start with 5.81 Sales Hot Time (min) Cold Time (min) Total Time CM per minute") Old Prices Change in 5 Change in 96 Patterned Glasses 15 3 18 I Paperweights 15 15 30 Wrapped tumblers 15 3 18 Vases 25 5 30 *The 5.81 is the breakeven CM per minute *Reference back to 3. to capture the 5.81 CM per minute so that you avoid the $27 rounding error on the Proof page. *Use cell referencing to build the pricing formulas 4. Explain Results: 5. Other Recommendations: Sales Weekly Quantity (Q) Weeks Annual Q Unit Cold Time Patterned Glasses 19 40 760 3 2,280 Paperweights 10 40 400 15 6,000 Wrapped tumblers 32 40 1,280 W 3,840 Vases 8 40 320 1,600 Total 69 2760 13,720 24% Hot Time per Total Annual Sales Weekly Quantity (Q) Weeks Annual Q Unit Hot Time Patterned Glasses 19 40 760 15 11,400 Paperweights 10 40 400 15 6,000 Wrapped tumblers 32 40 1,280 15 19,200 Vases 8 40 320 25 8,000 Total 59 2760 44,600 76% Total Productive Time 58,320 Scenario One: Minutes* Extra Units % Increase Rate Total $ 100% of cold time to hot time vases 0% $ 23.75 Extra cm profit Less additional wages @5 per hour 5.0 Extra expense Net improvement to income Net increase to profit; Ck Scenario Two: Minutes Extra Units % Increase Rate Total $ 50% of cold time to hot time vases 0% $ 23.75 50% of cold time to hot time paperweights 0% $ 13.59 Less additional wages @5 per hour 5.00 Net improvement to income Ck = 10,903 Scenario Three: Minutes Extra Units % Increase Rate Total $ 35% of cold time to hot time vases 0% $ 23.75 35% of cold time to hot time paperweights 0% $ 13.59 15% of cold time to hot time patterned glasses 0% $ 7.81 15% of cold time to hot time wrapped tumblers 0% $ 6.81 Less additional wages @5 per hour 5.00 Net improvement to income Ck = 9,226