A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 B C D E F G Estimate Estimation Error Square of Error Template for Linear Regression Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Independent Variable Dependent Variable Average: #DIV/0! H I J K L M N O 1 2 3 Linear Regression Line 4 y = a + bx a= #VALUE! 5 6 b= #VALUE! 7 8 9 Estimator If x = 10 11 12 then y= #VALUE! 13 14 f(x) = 15 12 16 17 18 10 19 20 21 8 22 Dependent Variable 23 24 6 25 26 27 4 28 29 30 2 31 32 33 0 34 0.00 35 36 37 38 Range Name a b DependentVariable Estimate EstimationError IndependentVariable SquareOfError x y 2.00 4.00 Cells J5 J6 D5:D34 E5:E34 F5:F34 C5:C34 G5:G34 J10 J12 6.00 Independent Variable 8.00 10.00 12.00 0.00 P 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 12.00 35 36 37 38 A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 B C D E F G H I J K L M Template for Exponential Smoothing Forecasting Method Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 True Value Exponential Smoothing Forecast #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Forecasting Error Smoothing Constant a = Initial Estimate Average = Mean Absolute Deviation MAD = #DIV/0! Mean Square Error MSE = Range Name Alpha Forecast ForecastingError InitialEstimate MAD MSE TrueValue Cells H6 D6:D35 E6:E35 H9 H12 H15 C6:C35 #DIV/0! 12 10 8 Value 6 True Value Forecas t 4 2 0 1 2 3 4 5 6 7 8 9 101112131415161718192021222324252627282930 Time Period N Individual Case Study Report Winter 20171 The Prescott Bakery Company Case Study Qnt. 5160 Maximum Points: 25 There are three parts to this individual case study be sure that you answer each part completely. You may answer these questions directly into this Word document. When you save it, save it as the following: (Your last name) Individual Case Study. Submit it into the Blackboard drop box by the deadline listed in your class calendar. You must have, as the front page, the required NSU/CBE front page with your typed signature attesting that you have not copied answers. Hints are provided throughout this document. It is strongly suggested that you follow those hints to obtain the maximum number of points in this important exercise. Read the backstory carefully, it will explain much of the information that you need to understand about this case study. Remember to write this report directly to William Flours whom you will meet in just a few minutes. Bill is NOT a statistician and does not understand much about these forecasting techniques, so explain them to him in a way that he will understand. Completely explain terms and concepts when they first appear in the case study questions. You need not explain the same term more than once, but explain each one completely so that Bill can understand it. There are two Excel spreadsheets (blank) that are needed for part 3. They are the Exponential Smoothing Spreadsheet, and the Blank Linear Regression Spreadsheet. Your professor has explained how to use them in your weekly chats, so refer to them or ask questions if you are lost. Now on to our story. The Prescott Bakery Company Story The Prescott Bakery Company is an old line company started in Prescott, Florida by the Flours family right after the Civil War. The company has moved as it expanded, with the latest bakery located just east of town off of State Route 13, about 2 miles out of Prescott. The current company president, William (Bill) Flours is an energetic man who loves baking and has said that \"If I cut myself, I guess I'd bleed flour\". His daughter, Robin, has been groomed to take over the company when her dad retires in about 10 years. Until then she is taking graduate business classes at Florida Northwestern State which is about 90 miles northwest of Prescott. Fall Pie Spectacular Robin is concerned that her dad does not do much to forecast production, and said recently \"He seems to fly by the seat of his pants\". She has been working with him to get a forecasting method for the company's pies. While the bakery is a full service company, producing breads, rolls, and other bakery products, it is well known for its pies. In fact, the company sponsors one of the biggest 1 While your professor reuses the basic information for these case studies, the data changes each term. Do NOT make the mistake of copying results from a prior term, or you will earn ZERO points for copying. 1|Page Copyright R&A 2017 festivals in Prescott, the Fall Pie Spectacular. Thousands of people flock into town one weekend in July when \"pies rule\". So Robin has been working on a number of forecasting techniques to try and solve the problem of how many pies should the bakery produce each month. The bakery produces about 5,000 pies a month selling them for between $5.00 to a little over $7.00 a pie. The type of pie determines the price, but we will not be concerned with the type of pie in this case study, just in the amount of pies that the company should produce during January 2017. We have information about their production for the entire year of 2016, but Robin is working toward showing her father that using the correct forecasting technique he can estimate his production for any month. Later in this case study you will be combining the cost of the pies and the number produced to come up with a production schedule, but for right now you will be concentrating on the number of pies for the month of January2. The production for the entire year is found below. Review it before you attempt this case study. Prescott Bakery Pie Production 2016 3 4,870 $6.25 January 4,965 $5.95 February 4,915 $5.50 March 5,107 $6.15 April 5,048 $7.29 May 5,102 $6.99 June 5,146 $6.39 July 4,965 $6.69 August 5,040 $6.45 September 4,968 $7.10 October 5,065 $6.85 November 5,019 $6.99 December Robin is off at school right now and has asked you, her close friend, to help with a preliminary report on various forecasting techniques and concepts. So this is your chance to help her, and make a few dollars in return for your efforts4. Remember, her dad does not understand statistics, so whatever you explain, be sure that he can understand it, and that your description is complete. Part 1. The Forecasting Techniques 2 By the way, this month has already passed and Robin knows the actual production for the month of January, but she wants to prove to her dad that forecasting works, so after this is all over she will review the actual production with the forecasted production with him. 3 While this case study shell has been used before, the data has been changed, so don't cheat and use prior data, it will be WRONG. 4 She has offered you $1,500 to help her, so make it a good report. 2|Page Copyright R&A 2017 Robin has tried out a number of techniques that she thinks will work well at the bakery. While she knows that there are other forecasting techniques (she has a very competent statistics professor at FLNWS), but she wants to keep the techniques simple and easy to understand. Robin needs to help her dad go through the various forecasting planning steps, but she has not done this yet, so you will have to help her.5 Based on the five (5) steps in creating a forecast, help Bill Flours by presenting him with suggestions that walk him through each of these steps. Remember, Bill is not a statistician, so help him with common English terms, (and define any uncommon ones) and go through each of these steps with him so that he can understand what they mean. Step 1: Define the Problem to be solved Step 2: Gather Statistical and Other Data Step 3: Look for Patterns in the Data or Outliers Step 4: Select a Forecasting Model to Use6 Step 5: Evaluate the Results of the Forecasting Model and Apply the Results Part 2. On her last vacation Robin used five different techniques to show her dad how this forecasting works. Your first job will be to explain these techniques to her dad. Prescott Bakery Pie Forecasting Summary Forecasting technique Statistic Last Value Averaging Moving Average 3MA Exponential Smoothing with Seasonality (a=.1) Exponential Smoothing with Seasonality (a=.5) 5 Remember you learned these planning techniques way back in Week 4, slides 9 through 14. Go there to refresh your memory as to what is required to accomplish in this planning. 6 Here you may discuss in general what are common forecasting techniques. 3|Page Copyright R&A 2017 MAD MSE January Forecast 129 26,046 5,019 126 24,989 5,129 136 26,314 5,181 88 10,504 5,134 70 7,512 5,026 For each of these techniques explain (1) what the technique is, (2) how does it work and then (3) what the results mean. You only need to explain the MAD and the MSE once, but make sure that Bill can understand what you are telling him, then at the end of this section compare and contrast these different forecasting techniques and recommend that he consider adopting one (and only one) to use as a possible forecasting technique. Be sure to explain your reasons for this decision. Type your responses in the following section, but remember to explain these results completely within each section. 6. The Last Value Forecasting Technique 7. The Averaging Forecasting Technique 8. The Moving Average Forecasting Technique (3MA) 9. The Exponential Smoothing Forecasting Technique with alpha = .1 10. The Exponential Smoothing Forecasting Technique with alpha = .5 11. Recommendation on which Forecasting Technique to Select and Why Part 3 - Casual Forecasting versus Exponential Smoothing In this third part you will be using two Excel templates to calculate both a linear regression and an exponential smoothing and then compare and contrast the two. You must explain your decision either way. Using the two Excel files provided to you, the Blank Linear Regression Spreadsheet and the Exponential Smoothing Spreadsheet to prepare several forecasts using the raw data as seen below. There are 2 columns of data, the middle column7 is used for the Exponential Smoothing, and both the 7 The number of pies produced by month. 4|Page Copyright R&A 2017 left and middle columns8 are used for the linear regression. The month column is not needed for either of these calculations. 8 The cost of each pie sold. 5|Page Copyright R&A 2017 The Raw Data Prescott Bakery Pie Production 2016 Month Pies Produced January February March April May June July August September October November December 4,870 Average Selling Price $6.25 4,965 $5.95 4,915 $5.50 5,107 $6.15 5,048 $7.29 5,102 $6.99 5,146 $6.39 4,965 $6.69 5,040 $6.45 4,968 $7.10 5,065 $6.85 5,019 $6.99 Exponential Smoothing Using the Exponential Smoothing Spreadsheet, import the raw data on the number of pies produced during 2016, set the Initial Estimate Average to 5,000. First set alpha at .1, then at .5, and then again lastly at .9 and complete the following table with the output information from the spreadsheet. Exponential Smoothing Results Forecast Output Alpha = .1 MAD MSE January Forecast Alpha = .5 Alpha = .9 NOTE: You may have to copy the Exponential Smoothing Forecast to the next blank space below to create the forecast for the month of January. After you have completed this, then compare and analyze the results including the three graphs that are produced as you change the alpha. In short, you will see three different results as you change the alpha. Answer these questions: 12. What is the difference in the smoothing of the data as you change the alpha values? Explain completely. 13. How do the MAD and MSE change as you change the alpha values? Explain completely why you think this is happening. 6|Page Copyright R&A 2017 14. Of these three different forecasting options, which one appears to produce the best forecast and why? The Causal Forecasting Technique Using the linear regression spreadsheet and using the monthly pie production as the dependent variable and the cost per pie as the independent variable, change the Estimator to 5,000 and complete the following table. Linear Regression Results Estimated Error Cost per Pies Estimated number of Pies Produced $6.50 $7.00 $7.50 NOTE: If you have forgotten how to use the results of the linear regression go to the PowerPoint presentation for week 5, slide 54 for how to do these calculations. Answer the following questions about the linear regression. 15. Which of these pie costs yield the most pies produced and why? 16. Which of these pie costs yield the least pies produced and why? Summary Section Comparing only the causal forecasting technique and the Exponential techniques (Part 3 only) which of these techniques would you recommend to Bill to use as a production forecasting technique, and why? This concludes this individual case study. Be sure to post it as well as a copy of both the linear and exponential smoothing spreadsheets into the Blackboard Dropbox by the deadline for your class. ### 7|Page Copyright R&A 2017 THE INDIVIDUAL CASE STUDY REPORT EXPLAINED Dr. Phillip S. Rokicki Qnt. 5160 The Prescott Bakery Case Study Copyright R&A 2017 The Course Evaluation The University has a new course evaluation process and you need to complete this evaluation by no latter than the last day of the term. You have been notified that the evaluation is now available, so open up the link and copy the front page of the link showing this course and attach the picture in a Blackboard message. You will earn 1 point for this activity. Of course complete the evaluation. Tell me what worked in this class, what did not work, and what could have been done better. 2 Copyright R&A 2017 Why this PowerPoint? The individual case study is relatively simple to both understand and to calculate. Much of the work has been done for you, but you still need to change the templates to find the correct answers. This short PowerPoint will show you how you need to do those changes. NOTE: You must read the textbook and the forecasting PowerPoints for this to make any sense. So also do those things! 3 4 Copyright R&A 2017 The Back Story The Prescott Bakery Company Case Study The Prescott Bakery Company is an old line company started in Prescott, Florida by the Flours family right after the Civil War. The company has moved as it expanded, with the latest bakery located just east of town off of State Route 13, about 2 miles out of Prescott. The current company president, William (Bill) Flours is an energetic man who loves baking and has said that \"If I cut myself, I guess I'd bleed flour\". His daughter, Robin, has been groomed to take over the company when her dad retires in about 10 years. Until then she is taking graduate business classes at Florida Northwestern State which is about 90 miles northwest of Prescott ? Copyright R&A 2017 5 The bakery produces about 5,000 pies a month selling them for between $3.00 to a little over $4.00 a pie. The type of pie determines the price, but we will not be concerned with the type of pie in this case study, just in the amount of pies that the company should produce during January 2016. We have information about their production for the entire year of 2015, but Robin is working toward showing her father that using the correct forecasting technique he can estimate his production for any month. Later in this case study you will be combining the cost of the pies and the number produced to come up with a production schedule, but for right now you will be concentrating on the number of pies for the month of January . The production for the entire year is found below. Review it before you attempt this case study. Prescott Bakery Pie Production 2016 4,870 $6.25 January 4,965 $5.95 February 4,915 $5.50 March 5,107 $6.15 April 5,048 $7.29 May 5,102 $6.99 June 5,146 $6.39 July 4,965 $6.69 August 5,040 $6.45 September 4,968 $7.10 October 5,065 $6.85 November 5,019 $6.99 December Copyright R&A 2017 6 Read the case study over carefully, there are several parts that must be answered. Part 1 is a discussion of the general forecasting techniques. Part 2 is a discussion of the last value, averaging, moving average and exponential smoothing with 2 different alphas. Part 3 is a comparison of the output for an exponential smoothing forecast versus causal forecasting (simple linear regression). Copyright R&A 2017 7 There are three data files here, be sure to carefully look at both. Copyright R&A 2017 8 You will type your answers to these questions directly into the Word document provided to you. (See below) Copyright R&A 2017 9 Review these questions to be answered in Part 1 of the report . . . Step 1: Define the Problem to be solved Step 2: Gather Statistical and Other Data Step 3: Look for Patterns in the Data or Outliers Step 4: Select a Forecasting Model to Use Step 5: Evaluate the Results of the Forecasting Model and Apply the Results Remember you learned these planning techniques way back in Week 4, slides 9 through 14. Go there to refresh your memory as to what is required to accomplish in this planning. 9 Copyright R&A 2017 10 All of the answers for Part 2 of this case study are found in this table. . . You do not have to recalculate these \"answers\" because they are already provided to you. Copyright R&A 2017 11 In Part 2 of the case study you will be looking at each of these forecasting techniques and discussing each by comparing and contrasting the statistical results as presented to you. Remember, you will be explaining this information to an individual who is not schooled in statistics, so your explanations must be tailored to them, but the answers must be complete. What is the definition for each of these forecasting techniques? What is the MAD, what does it mean? What is the MSE, and what does it mean? What is the difference between the MAD and MSE for each of these techniques? Which of these techniques are the best/worst to use for these respective companies? Why have you selected this forecasting technique? 11 Copyright R&A 2017 12 Part 3 for the Prescott Bakery Company Case Study Using the two Excel files provided to you, the Blank Linear Regression Spreadsheet and the Exponential Smoothing Spreadsheet to prepare several forecasts using the raw data as seen below. There are 2 columns of data, the left column is used for the Exponential Smoothing, and both the left and right columns are used for the linear regression. Prescott Bakery Pie Production 2016 Month Pies Produced Average Selling Price January 4,870 $6.25 February 4,965 $5.95 March 4,915 $5.50 April 5,107 $6.15 May 5,048 $7.29 June 5,102 $6.99 July 5,146 $6.39 August 4,965 $6.69 September 5,040 $6.45 October 4,968 $7.10 November 5,065 $6.85 December 5,019 $6.99 13 Copyright R&A 2017 Now let's look at the templates The statistical template files are here. Copyright R&A 2017 Exponential Smoothing Template 14 Copyright R&A 2017 Simple Regression Template 15 Copyright R&A 2017 What is the MAD or the MSE? If you have forgotten what the MAD or the MSE is then go to the PowerPoint for week 4, and look in your textbook. Each is explained in detail there. 16 Copyright R&A 2017 Question 3 Prescott Bakery Exponential Smoothing 17 Copyright R&A 2017 Question 3 Prescott Bakery Regression 18 Copyright R&A 2017 19 These hints should help you to complete the statistics needed for your individual case study. Remember to: Answer these questions in the Word document provided. Be sure that your answers make sense. In other words if the prior year/month the company had X cases or Y pies and your answer shows substantially fewer or more, does this make sense???? Obviously it does not so you have made a mistake somewhere, go and find it. Run spell check on this file before you submit it by the deadline. You have 25 points riding on this submission. Copyright R&A 2017 The end of this PowerPoint on the Bakery Case Studies 20 The Individual Case Study - Revisited QNT 5160 DR. PHILLIP S. ROKICKI 2 The Individual Case Study Questions In this PowerPoint we will look at the different questions to be covered in your individual case study report. Many of the questions are simple if you first read and understand the case study itself. Only in part 3 are you asked to do calculations, but they must be done correctly so that you get the maximum number of points. Start with reading the materials for this week, then the case study, and the work to be done. Copyright R&A 2017 Read the case study over carefully, there are several parts that must be answered. Copyright R&A 2017 3 Your answers are to be typed (not hand written) into 4 this Word document under each appropriate question. Part 1. Help the owner of the business to understand the basics of creating a forecast: Step 1: Define the Problem to be solved; Step 2: Gather Statistical and Other Data; Step 3: Look for Patterns in the Data or Outliers; Step 4: Select a Forecasting Model to Use; and, Step 5: Evaluate the Results of the Forecasting Model and Apply the Results. Remember you learned these planning techniques way back in Week 4, slides 9 through 14. Go there to refresh your memory as to what is required to accomplish in this planning. Copyright R&A 2017 5 All of the answers for part 2 of this case study are found in these tables Prescott Bakery Pie Forecasting Summary Forecasting technique Exponential Smoothing with Seasonality (a=.5) Statistic Last Value Averaging Moving Average 3MA Exponential Smoothing with Seasonality (a=.1) MAD 129 126 136 88 70 MSE 26,046 24,989 26,314 10,504 7,512 January Forecast 5,019 5,129 5,181 5,134 5,026 Copyright R&A 2017 Can you explain these forecasting techniques, the MAD and the MSE, and which one provides the best forecast for the company? Part 2 -- Questions 6 For each of these techniques explain (1) what the technique is; (2) how does it work; and then, (3) what the results mean. You only need to explain the MAD and the MSE once, but make sure that your person can understand what you are telling them, then at the end of this section compare and contrast these different forecasting techniques and recommend that she consider adopting one (and only one) to use as a possible forecasting technique. Be sure to explain your reasons for this decision. Type your responses in the following section, but remember to explain these results completely within each section. 6. The Last Value Forecasting Technique; 7. The Averaging Forecasting Technique; 8. The Moving Average Forecasting Technique (3MA); 9. The Exponential Smoothing Forecasting Technique with alpha = .1; 10.The Exponential Smoothing Forecasting Technique with alpha = .5; and, 11.Recommendation on which Forecasting Technique to Select and Why? Copyright R&A 2017 6 7 Prescott Bakery This is important Prescott Bakery Copyright R&A 2017 7 Raw data goes here Smoothing constant goes here 8 Exponential Smoothing Template Initial average goes here Copyright R&A 2017 9 Causal or Linear Regression Setup The Constant Slope of the line Copyright R&A 2017 Dependent data goes here Estimator goes here 10 Causal or simple regression Template Independent data goes here Copyright R&A 2017 Y estimators - constant and slope of the line are found here. You use them to create your estimate Example of a completed Regression Template for question 3 11 This is the constant Average error is here Copyright R&A 2017 This is the regression template. You will copy the independent and dependent variables using the data for question 3. You must also change the \"Estimator\" as was demonstrated in the Week 5 PowerPoint based on information from the case study. This is the slope of the line Here are your answers you need to forecast the next period Part 3 -- Questions 12 Questions for the Exponential Smoothing Data: 12.What is the difference in the smoothing of the data as you change the alpha values? Explain completely. 13.How do the MAD and MSE change as you change the alpha values? Explain completely why you think this is happening. 14.Of these three different forecasting options, which one appears to produce the best forecast and why? Questions for the Causal or Simple Linear Regression: 15.Which of these estimator values yield the most bottles sold and why? 16.Which of these estimator values the smallest error and why? Copyright R&A 2017 12 13 Summary Question Summary Section Comparing only the causal forecasting technique and the Exponential techniques (Part 3 only) which of these techniques would you recommend to Susan to use as a production forecasting technique, and why? Copyright R&A 2017 14 The final report . . . This report is worth a maximum of 25 points. It will go a long way to determine your final course grade. Be sure to run spell check and read over your submission before you send it in. Did you completely answer each question? Does your answer make sense? Does your forecast for the month or year compare with the prior period's number of pies or wine bottles used? If it is not close then you have made a mistake, look at your calculations again. Copyright R&A 2017 15 This is the final project for the course. Good luck on it and in your future NSU classes. Copyright R&A 2017 A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 B C D E F G Estimate 5,002 4,986 4,962 4,996 5,057 5,041 5,009 5,025 5,012 5,047 5,033 5,041 Estimation Error 131.52 20.54 46.57 110.81 8.91 61.06 137.02 59.96 27.83 78.79 31.52 21.94 Square of Error 17,298 422 2,169 12,278 79 3,729 18,775 3,595 774 6,209 994 481 Template for Linear Regression Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Independent Variable 6.25 5.95 5.50 6.15 7.29 6.99 6.39 6.69 6.45 7.10 6.85 6.99 Dependent Variable 4,870 4,965 4,915 5,107 5,048 5,102 5,146 4,965 5,040 4,968 5,065 5,019 Average: 61.37 H I J K L M N O 1 2 3 Linear Regression Line Range Name Cells 4 y = a + bx a J5 a= 4,668.63 b J6 5 6 b= 53.26 DependentVariable D5:D34 Estimate E5:E34 7 EstimationError F5:F34 8 IndependentVariable C5:C34 9 Estimator If x = 5,000 SquareOfError G5:G34 10 11 x J10 12 then y= 270,985.26 y J12 13 14 15 5,200 16 17 5,150 18 5,100 19 20 5,050 21 f(x) = 53.2633279483x + 4668.6252019386 22 Dependent Variable 5,000 23 24 4,950 25 26 4,900 27 28 4,850 29 4,800 30 31 4,750 32 33 4,700 34 5.40 5.60 5.80 6.00 6.20 6.40 6.60 6.80 35 Independent Variable 36 37 38 7.00 7.20 7.40 P 1 7.20 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 7.40 35 36 37 38 A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 B C D E F G H I J K L M Template for Exponential Smoothing Forecasting Method Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 True Value 4,870 4,965 4,915 5,107 5,048 5,102 5,146 4,965 5,040 4,968 5,065 5,019 Exponential Smoothing Forecast 5,000 4,883 4,957 4,919 5,088 5,052 5,097 5,141 4,983 5,034 4,975 5,056 5,023 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Forecasting Error 130 82 42 188 40 50 49 176 57 66 90 37 Smoothing Constant a = Initial Estimate Average = 0.9 5,000 Mean Absolute Deviation MAD = 84 Mean Square Error MSE = Range Name Alpha Forecast ForecastingError InitialEstimate MAD MSE TrueValue Cells H6 D6:D35 E6:E35 H9 H12 H15 C6:C35 9,616 5,200 5,150 5,100 5,050 5,000 Value 4,950 True Value 4,900 Forecas t 4,850 4,800 4,750 4,700 1 2 3 4 5 6 7 8 9 101112131415161718192021222324252627282930 Time Period N A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 B C D E F G Estimate 5,002 4,986 4,962 4,996 5,057 5,041 5,009 5,025 5,012 5,047 5,033 5,041 Estimation Error 131.52 20.54 46.57 110.81 8.91 61.06 137.02 59.96 27.83 78.79 31.52 21.94 Square of Error 17,298 422 2,169 12,278 79 3,729 18,775 3,595 774 6,209 994 481 Template for Linear Regression Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Independent Variable 6.25 5.95 5.50 6.15 7.29 6.99 6.39 6.69 6.45 7.10 6.85 6.99 Dependent Variable 4,870 4,965 4,915 5,107 5,048 5,102 5,146 4,965 5,040 4,968 5,065 5,019 Average: 61.37 H I J K L M N O 1 2 3 Linear Regression Line Range Name Cells 4 y = a + bx a J5 a= 4,668.63 b J6 5 6 b= 53.26 DependentVariable D5:D34 Estimate E5:E34 7 EstimationError F5:F34 8 IndependentVariable C5:C34 9 Estimator If x = 5,000 SquareOfError G5:G34 10 11 x J10 12 then y= 270,985.26 y J12 13 14 15 5,200 16 17 5,150 18 5,100 19 20 5,050 21 f(x) = 53.2633279483x + 4668.6252019386 22 Dependent Variable 5,000 23 24 4,950 25 26 4,900 27 28 4,850 29 4,800 30 31 4,750 32 33 4,700 34 5.40 5.60 5.80 6.00 6.20 6.40 6.60 6.80 35 Independent Variable 36 37 38 7.00 7.20 7.40 P 1 7.20 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 7.40 35 36 37 38 A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 B C D E F G H I J K L M Template for Exponential Smoothing Forecasting Method Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 True Value 4,870 4,965 4,915 5,107 5,048 5,102 5,146 4,965 5,040 4,968 5,065 5,019 Exponential Smoothing Forecast 5,000 4,883 4,957 4,919 5,088 5,052 5,097 5,141 4,983 5,034 4,975 5,056 5,023 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Forecasting Error 130 82 42 188 40 50 49 176 57 66 90 37 Smoothing Constant a = Initial Estimate Average = 0.9 5,000 Mean Absolute Deviation MAD = 84 Mean Square Error MSE = Range Name Alpha Forecast ForecastingError InitialEstimate MAD MSE TrueValue Cells H6 D6:D35 E6:E35 H9 H12 H15 C6:C35 9,616 5,200 5,150 5,100 5,050 5,000 Value 4,950 True Value 4,900 Forecas t 4,850 4,800 4,750 4,700 1 2 3 4 5 6 7 8 9 101112131415161718192021222324252627282930 Time Period N