VIEW U !!! Calibri 11 AA 2 Wrap Text Parte BIU A FEE Merge & Center Clipboard Font Alignment SUBSCRIPTION CANCELLED On Monday, May 24, 2021, most features of Excel will be disabled. Re K15 Ix H A B C D E F G 1 1 The Mac Davis Company specializes in the purchase, renovation, and resale of older 2 homes. Jess employs several carpenters and painters to do the work for him. It is 3 4 essential for him to have accurate cost estimates so he can determine total renovation 5 costs before he purchases a piece of property. If estimated renovation costs plus the purchase price of a house are higher than its estimated resale value, the house is not a 6 worthwhile investment. 7 8 Mac has been using the home's interior square feet for his exterior paint cost 9 estimations. Recently he decided to include the number of external openings-the total 10 number of doors and windows in a house as a cost driver. Their cost is significant 11 because they require time consuming preparatory work and careful brushwork. The rest 12 of the house usually is painted either by rollers or spray guns, which are relatively 13 efficient ways to apply paint to a large area. Mac has kept careful records of these 14 external painting costs on his last 12 jobs (on the next tab). 15 16 Follow the instructions on the following tabs to perform cost estimation for The Mac 17 Davis Company 18 19 You MUST answer each yellow cell with an Excel formula. A specific formula may be 20 required in some instances, read each of the requirements thoroughly. 21 22 23 Instructions Cost Estimation Chart 1 Chart2 Ready Type here to search File Home Insert Draw Page Layout Formulas Data Review View Help Calibri -11-AA General 29 Wrap Text E Merge & Center BTU - $ Clipboard Font Alignment SUBSCRIPTION CANCELLED On Monday, May 24, 2021, most features of Excel will be disabled. Reactivate H23 F G A B D E F H External 1 House Square Feet Openings Costs 2 1 2,500 13 $2,810 3 2 3,010 15 $3,742 4 3 2,800 12 $3,100 5 4 2,850 12 $3,150 6 4,600 19 $4,700 7 6 2,700 13 $3,225 8 7 2,600 11 $2,920 9 8 2,550 11 $2,836 10 9 2,600 10 $3,242 11 10 3,700 16 $4,112 12 11 2,650 13 $3,210 13 12 3,550 16 $3,965 14 15 1. Graph the data following the instructions on the Charts tabs. 16 17 2. Considering square feet as the cost driver, use the high low method to determine the cost 18 estimation formula. Use the boxes below for the fixed cost and variable rate portions. 19 20 Total renovation cost = per square foot 21 Fixed cost Variable rate 22 Instructions Cost Estimation Chart1 Chart2 NON 09 Ready Type here to search Canon 29 Wrap Text - A-EEEE E Merge & Center General BT $ % Clipboard Font Alignment SUBSCRIPTION CANCELLED On Monday, May 24, 2021, most features of Excel will be disabled. Nu Reactivate H23 B D E F G H 22 23 3. Using your answer from #2, estimate the total renovation cost for a 3,300 square foot home. 24 25 Estimated total renovation cost 26 3,300 square foot home 27 28 4. Considering external openings as the cost driver, use the high low method to determine the 29 cost estimation formula. Use the boxes below for the fixed cost and variable rate portions. 30 31 Total renovation cost = per external opening 32 Fixed cost Variable rate 33 34 5. Using your answer from #4, estimate the total renovation cost for a home with 14 external opening 35 36 Estimated total renovation cost 37 14 external openings 38 39 6. Compute the mean absolute percentage error (MAPE) for the cost estimation formula with each 40 cost driver. Use the cells below to calculate the estimated cost (using each cost formula), the 41 absolute error, the total error, and the MAPE. You must use the ABS formula in columns Cand F. 42 43 Square feet as cost driver External openings as cost driver Estimated Absolute Estimated Absolute Instructions Cost Estimation Chart Chart2 Ready Type here to search cel File Home Insert Draw Page Layout Formulas Data Review View Help Calibri 11 - AA 23 Wrap Text General BTU- Merge & Center - $ Clipboard Font Alignment SUBSCRIPTION CANCELLED On Monday, May 24, 2021, most features of Excel will be disabled. Reactivate H23 B D E G H 42 43 Square feet as cost driver Estimated Absolute Cost Error External openings as cost driver Estimated Absolute Cost Error 44 House 1 45 46 2 3 4 5 6 7 8 9 48 49 50 51 52 53 54 10 55 11 56 12 57 Total 58 MAPE 59 60 7. Run the regression analysis using each cost driver. Do not add or delete any cells or rows on the 61 - For regression with square feet as the cost driver, name the tab Regressioni (no spaces). 62 - For regression with external openings as the cost driver, name the tab Regression2 (no spaces). 63 Instructions Cost Estimation Chart1 Chart2 Ready HE Type here to search C Calibri - 11-AA = 2 Wrap Text BIEB O A Merge & Center Clipboard Font Alignment SUBSCRIPTION CANCELLED On Monday, May 24, 2021, most features of Excel will be disabled. Read H23 f A D F B C E G H 63 64 8. Considering the results of the MAPE, which cost driver more accurately estimates the 65 restoration costs? Enter 1 for square feet or 2 for external openings. 66 67 Best cost driver 68 69 9. Considering the square from the regression statistics, which cost driver more accurately 70 estimates the restoration costs? Enter 1 for square feet or 2 for external openings. 71 72 Best cost driver 73 74 75 76 77 78 79 80 81 82 83 84 85 Instructions Cost Estimation Charti Chart2 Ready Type here to search VIEW U !!! Calibri 11 AA 2 Wrap Text Parte BIU A FEE Merge & Center Clipboard Font Alignment SUBSCRIPTION CANCELLED On Monday, May 24, 2021, most features of Excel will be disabled. Re K15 Ix H A B C D E F G 1 1 The Mac Davis Company specializes in the purchase, renovation, and resale of older 2 homes. Jess employs several carpenters and painters to do the work for him. It is 3 4 essential for him to have accurate cost estimates so he can determine total renovation 5 costs before he purchases a piece of property. If estimated renovation costs plus the purchase price of a house are higher than its estimated resale value, the house is not a 6 worthwhile investment. 7 8 Mac has been using the home's interior square feet for his exterior paint cost 9 estimations. Recently he decided to include the number of external openings-the total 10 number of doors and windows in a house as a cost driver. Their cost is significant 11 because they require time consuming preparatory work and careful brushwork. The rest 12 of the house usually is painted either by rollers or spray guns, which are relatively 13 efficient ways to apply paint to a large area. Mac has kept careful records of these 14 external painting costs on his last 12 jobs (on the next tab). 15 16 Follow the instructions on the following tabs to perform cost estimation for The Mac 17 Davis Company 18 19 You MUST answer each yellow cell with an Excel formula. A specific formula may be 20 required in some instances, read each of the requirements thoroughly. 21 22 23 Instructions Cost Estimation Chart 1 Chart2 Ready Type here to search File Home Insert Draw Page Layout Formulas Data Review View Help Calibri -11-AA General 29 Wrap Text E Merge & Center BTU - $ Clipboard Font Alignment SUBSCRIPTION CANCELLED On Monday, May 24, 2021, most features of Excel will be disabled. Reactivate H23 F G A B D E F H External 1 House Square Feet Openings Costs 2 1 2,500 13 $2,810 3 2 3,010 15 $3,742 4 3 2,800 12 $3,100 5 4 2,850 12 $3,150 6 4,600 19 $4,700 7 6 2,700 13 $3,225 8 7 2,600 11 $2,920 9 8 2,550 11 $2,836 10 9 2,600 10 $3,242 11 10 3,700 16 $4,112 12 11 2,650 13 $3,210 13 12 3,550 16 $3,965 14 15 1. Graph the data following the instructions on the Charts tabs. 16 17 2. Considering square feet as the cost driver, use the high low method to determine the cost 18 estimation formula. Use the boxes below for the fixed cost and variable rate portions. 19 20 Total renovation cost = per square foot 21 Fixed cost Variable rate 22 Instructions Cost Estimation Chart1 Chart2 NON 09 Ready Type here to search Canon 29 Wrap Text - A-EEEE E Merge & Center General BT $ % Clipboard Font Alignment SUBSCRIPTION CANCELLED On Monday, May 24, 2021, most features of Excel will be disabled. Nu Reactivate H23 B D E F G H 22 23 3. Using your answer from #2, estimate the total renovation cost for a 3,300 square foot home. 24 25 Estimated total renovation cost 26 3,300 square foot home 27 28 4. Considering external openings as the cost driver, use the high low method to determine the 29 cost estimation formula. Use the boxes below for the fixed cost and variable rate portions. 30 31 Total renovation cost = per external opening 32 Fixed cost Variable rate 33 34 5. Using your answer from #4, estimate the total renovation cost for a home with 14 external opening 35 36 Estimated total renovation cost 37 14 external openings 38 39 6. Compute the mean absolute percentage error (MAPE) for the cost estimation formula with each 40 cost driver. Use the cells below to calculate the estimated cost (using each cost formula), the 41 absolute error, the total error, and the MAPE. You must use the ABS formula in columns Cand F. 42 43 Square feet as cost driver External openings as cost driver Estimated Absolute Estimated Absolute Instructions Cost Estimation Chart Chart2 Ready Type here to search cel File Home Insert Draw Page Layout Formulas Data Review View Help Calibri 11 - AA 23 Wrap Text General BTU- Merge & Center - $ Clipboard Font Alignment SUBSCRIPTION CANCELLED On Monday, May 24, 2021, most features of Excel will be disabled. Reactivate H23 B D E G H 42 43 Square feet as cost driver Estimated Absolute Cost Error External openings as cost driver Estimated Absolute Cost Error 44 House 1 45 46 2 3 4 5 6 7 8 9 48 49 50 51 52 53 54 10 55 11 56 12 57 Total 58 MAPE 59 60 7. Run the regression analysis using each cost driver. Do not add or delete any cells or rows on the 61 - For regression with square feet as the cost driver, name the tab Regressioni (no spaces). 62 - For regression with external openings as the cost driver, name the tab Regression2 (no spaces). 63 Instructions Cost Estimation Chart1 Chart2 Ready HE Type here to search C Calibri - 11-AA = 2 Wrap Text BIEB O A Merge & Center Clipboard Font Alignment SUBSCRIPTION CANCELLED On Monday, May 24, 2021, most features of Excel will be disabled. Read H23 f A D F B C E G H 63 64 8. Considering the results of the MAPE, which cost driver more accurately estimates the 65 restoration costs? Enter 1 for square feet or 2 for external openings. 66 67 Best cost driver 68 69 9. Considering the square from the regression statistics, which cost driver more accurately 70 estimates the restoration costs? Enter 1 for square feet or 2 for external openings. 71 72 Best cost driver 73 74 75 76 77 78 79 80 81 82 83 84 85 Instructions Cost Estimation Charti Chart2 Ready Type here to search