Individual Case Study Report
Winter 2017[1]
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.
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 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 January[2]. The production for the entire year is found below. Review it before you attempt this case study.
| Prescott Bakery Pie Production 2016[3] | |||
| 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 | |
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 efforts[4]. 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
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 Use[6]
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) |
| 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 |
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.
- The Last Value Forecasting Technique
- The Averaging Forecasting Technique
- The Moving Average Forecasting Technique (3MA)
- The Exponential Smoothing Forecasting Technique with alpha = .1
- The Exponential Smoothing Forecasting Technique with alpha = .5
- 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 column[7] is used for the Exponential Smoothing, and both the left and middle columns[8] are used for the linear regression. The month column is not needed for either of these calculations.
The Raw Data
| 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 |
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 | Alpha = .5 | Alpha = .9 |
| MAD | |||
| MSE | |||
| January Forecast |
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:
- What is the difference in the smoothing of the data as you change the alpha values? Explain completely.
- How do the MAD and MSE change as you change the alpha values? Explain completely why you think this is happening.
- 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.
- Which of these pie costs yield the most pies produced and why?
- 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.
#
[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.
[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.
[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.
[7] The number of pies produced by month.
[8] The cost of each pie sold.
