1 1 7 9 920 1 Functions: MAX, MIN, SLOPE, INTERCEPT; Formula: Add, Multiply; Cell Referencing 2 3 E2.10 - Using Excel to Use the High-Low Method Student Work Area 4 PROBLEM Required: Provide input into cells shaded in yellow in this template. Use the MAX, MIN, 5 After graduating from dental school two years ago, Dr. Lauren Farish 1 SLOPE, and INTERCEPT functions with cell references to the Problem area or work area, 6 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. 7 purchased the practice in January of this year and immediately had to 8 replace the outdated autoclave equipment she inherited from the previous 9 dentist. Now, as she is preparing her budget for next year, she is concerned . jabout 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 .1 changed. She has gathered the following information from her records. used with the high-low method. Then use the SLOPE function to determine the L2 variable cost per unit of sterilizing an instrument using the new equipment. 13 1 4 5 Number of Total Autoclave Activity Level Cost -6 Month Instruments Used Cost High activity level .7 January 850 $ 7,600 Low activity level .8 February 750 6,800 March 8,200 Variable cost per unit 20 April 950 8,600 21 May 1,150 9,900 b. Use Excel's INTERCEPT function to determine the fixed cost of the autoclave 22 June 900 8,100 equipment? 3 July 1,050 9,600 August 975 8,700 Total fixed costs 25 26 C. What is the cost formula that Dr. Farish should use for estimating autoclave 27 sterilization costs for next year's budget? 18 29 Total cost = ) + 30 1 d. Dr. Farish is estimating instruments for next month, what cost should she include in 12 her budget for instrument sterilization for the following estimate? 3 34 Number of instruments 900 35 6 Budgeted cost 37 8 24 X 1 1 7 9 920 1 Functions: MAX, MIN, SLOPE, INTERCEPT; Formula: Add, Multiply; Cell Referencing 2 3 E2.10 - Using Excel to Use the High-Low Method Student Work Area 4 PROBLEM Required: Provide input into cells shaded in yellow in this template. Use the MAX, MIN, 5 After graduating from dental school two years ago, Dr. Lauren Farish 1 SLOPE, and INTERCEPT functions with cell references to the Problem area or work area, 6 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. 7 purchased the practice in January of this year and immediately had to 8 replace the outdated autoclave equipment she inherited from the previous 9 dentist. Now, as she is preparing her budget for next year, she is concerned . jabout 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 .1 changed. She has gathered the following information from her records. used with the high-low method. Then use the SLOPE function to determine the L2 variable cost per unit of sterilizing an instrument using the new equipment. 13 1 4 5 Number of Total Autoclave Activity Level Cost -6 Month Instruments Used Cost High activity level .7 January 850 $ 7,600 Low activity level .8 February 750 6,800 March 8,200 Variable cost per unit 20 April 950 8,600 21 May 1,150 9,900 b. Use Excel's INTERCEPT function to determine the fixed cost of the autoclave 22 June 900 8,100 equipment? 3 July 1,050 9,600 August 975 8,700 Total fixed costs 25 26 C. What is the cost formula that Dr. Farish should use for estimating autoclave 27 sterilization costs for next year's budget? 18 29 Total cost = ) + 30 1 d. Dr. Farish is estimating instruments for next month, what cost should she include in 12 her budget for instrument sterilization for the following estimate? 3 34 Number of instruments 900 35 6 Budgeted cost 37 8 24 X