Functions: MAX, MIN, SLOPE, INTERCEPT; Formula: Add, Multiply; Cell Referencing E2.10 - Using Excel to Use the High-Low Method Student Work Area PROBLEM Required: Provide input into cells shaded in yellow in this template. Use the MAX, MIN, After graduating from dental school two years ago, Dr. Lauren Farish SLOPE, and INTERCEPT functions with cell references to the Problem area or work area, purchased the dental practice of a long-time dentist who was retiring. She and mathematical formulas with cell references to the Problem or work area as indicated purchased the practice in January of this year and immediately had to replace the outdated autoclave equipment she inherited from the previous dentist. Now, as she is preparing her budget for next year, she is concerned about understanding how her cost for sterilizing her dental instruments has a. Use Excel's MAX and MIN functions to identify the high and low data points to be changed. She has gathered the following information from her records. used with the high-low method. Then use the SLOPE function to determine the variable cost per unit of sterilizing an instrument using the new equipment. Number of Total Autoclave Activity Level Cost Month Instruments Used Cost High activity level January 850 $ 7,600 Low activity level February 750 6,800 March 920 3,200 Variable cost per unit April 950 3,600 May 1, 150 9,900 b. Use Excel's INTERCEPT function to determine the fixed cost of the autoclave June 900 8 , 100 equipment? July 1,050 9,600 August 975 8, 700 Total fixed costs C. What is the cost formula that Dr. Farish should use for estimating autoclave sterilization costs for next year's budget? Total cost = x )+ d. Dr. Farish is estimating instruments for next month, what cost should she include in her budget for instrument sterilization for the following estimate? Number of instruments 900 Budgeted costFormula: Multiply, Divide, Subtract; Cell Referencing E3.2 - Using Excel for Breakeven Analysis Student Work Area PROBLEM Required: Provide input into cells shaded in yellow in this template, Scott Confectionery sells Stack-o-Choc candy bars, Information using mathematical formulas with cell references to the Problem area and concerning the candy bars is presented here. the work area as indicated. Selling price of each candy bar 0.80 Variable cost per candy bar 0.45 a. What is the contribution margin per unit for the Stack-o-Choc candy 10 Total fixed costs 175,000 bar? 11 12 Contribution margin per unit 13 14 b. What is the contribution margin ratio for the Stack-o-Choc candy 15 bar? 16 Contribution margin ratio c. What is the breakeven point in units? In sales dollars? Breakeven point in units Breakeven point in sales dollars d. If an increase in chocolate prices causes the variable cost per unit to increase, what will happen to the breakeven point? New variable cost per unit 0.55 New breakeven point in units New breakeven point in sales dollars The breakeven point will by units and by sales dollars. 43A1 V X v fx Formula: Multiply, Divide, Subtract; Cell Referencing A B C D E F G H K L Formula: Multiply, Divide, Subtract; Cell Referencing - W N E3.2 - Using Excel for Breakeven Analysis Student Work Area PROBLEM Required: Provide input into cells shaded in yellow in this template, Scott Confectionery sells Stack-o-Choc candy bars, Information using mathematical formulas with cell references to the Problem area and 6 concerning the candy bars is presented here. the work area as indicated. Selling price of each candy bar S 0.80 4 00 Variable cost per candy bar 0.45 a. What is the contribution margin per unit for the Stack-o-Choc candy 10 Total fixed costs 175,000 bar? 11 12 Contribution margin per unit 13 14 b. What is the contribution margin ratio for the Stack-o-Choc candy bar? Contribution margin ratio c. What is the breakeven point in units? In sales dollars? Breakeven point in units Breakeven point in sales dollars d. If an increase in chocolate prices causes the variable cost per unit to increase, what will happen to the breakeven point? New variable cost per unit 0.55 New breakeven point in units New breakeven point in sales dollars The breakeven point will by units and by sales dollars