AutoSave ON A P8 ? " C ... CVP Project Spreadsheet Fa22 - Ryan Durst & - Saved Home Insert Draw Page Layout Formulas Data Review View Analytic Solver Data Mining ? Tell me Share Comments Calibri (Body) 10 AA ab v Currency Insert v 2 x Delete Paste MVA E $ ~ % 9 Conditional Format Cell Sort & Find & Analyze Sensitivity Solver Formatting as Table Styles Format v DV Filter Select Data C21 X V fx =SUM(C12:C18) B C D E F G H K Variable Costs 3 Conversion of Units Purchased to Units Used* Number of ounces in a gallon 128 Number of teaspoons in a cup 48 * EXCEPTION: Since these are actual constants, you may enter numbers in C5 and C6. 8 9 Variable Product Costs 10 11 Product Costs Cost per Small Shake Cost per Large Shake 12 Whole Mill $0.07 SO. 10 13 Cream $0.31 $0.47 14 Sugar $0.20 $0.30 15 Premium Vanilla Ice Cream $0.25 50.38 16 Flavorings 0.50 50.80 17 Flavored specialty straws $0.80 0.80 18 8 oz cups 50.40 19 12 oz cups $0.51 20 21 Product C $2.53 $3.35 22 23 Variable Selling Costs 24 Selling Costs Cost per Small Shake Cost per Large Shake 25 PLUS Rent based on Sales $0.70 $1.00 26 27 Total Selling Costs $0.70 $1.00 28 29 Total Variable Costs 30 Cost per Small Shake Cost per Large Shake 31 Tota $3.2 $4.35 32 33 Information for Chart 34 Label Variable Cost for Large Milkshake PROJECT INFORMATION Capital Expenditures Funding Depreciation Variable Costs Fixed Costs Breakeven Target Profit Cont Margin Inc Statements Checkfigures + Ready Accessibility: Investigate 136%AutoSave ON A P8 ? " C ... CVP Project Spreadsheet Fa22 - Ryan Durst & - Saved Home Insert Draw Page Layout Formulas Data Review View Analytic Solver Data Mining ? Tell me L Share Comments Calibri (Body) 10 AA g v Currency Insert v 2 Delete Paste BI UV HV MVAv $ ~ % 9 Conditional Format Cell Sort & Find & Analyze Sensitivity Solver Formatting as Table Styles Format v DV Filter Select Data B10 X V fx B C E G H K M N O P Q Fixed Costs W N Monthly Costs 4 Shack Rental $591.00 5 Cleaning and other Supplies $90.0 6 Insurance $50.00 Employee Pay $1,600.00 8 Interest $14.27 9 Depreciation $55.33 10 11 13 4 Total $2,400.60 15 16 E PROJECT INFORMATION Capital Expenditures Funding Depreciation Variable Costs Fixed Costs Breakeven Target Profit Cont Margin Inc Statements Checkfigures + Ready Accessibility: Investigate 100%AutoSave ON A Fe ? " C ... CVP Project Spreadsheet Fa22 - Ryan Durst & - Saved Home Insert Draw Page Layout Formulas Data Review View Analytic Solver Data Mining ? Tell me L Share Comments Calibri (Body) 10 AA 297 ab v Number Insert v x Delete V V Paste BIUv A % 9 508 Conditional Format Cell Sort & Find & Analyze Sensitivity Solver Formatting as Table Styles Format v Filter Select Data B21 X V. fx? B C D E F G H K M See discussion beginning on page 219 in your text for an example of a Multi-product Breakeven. 2 Breakeven 3 Put your well-labelled breakeven analysis below. Remember that all of the calculations must be completed in EXCEL formulas and no 4 new data is entered on this worksheet; You may add more rows to this section if needed Small Large 7 Sales Mix 40% 60% 8 Selling Price $7.00 $10.0 9 Variable Costs per Unit $3.23 $4.35 10 Contribution Margin per Unit $3.77 $5.65 11 2 Weighted Average CM per Unit 13 Fixed Costs $2,400.60 14 BE Units 15 16 17 18 19 Results 20 Number of Small Shakes 21 Number of Large Shakes 23 Information for Chart 24 25 26 28 29 30 35 36 40 PROJECT INFORMATION Capital Expenditures Funding Depreciation Variable Costs Fixed Costs Breakeven Target Profit Cont Margin Inc Statements Checkfigures + Enter Accessibility: Investigate 115%