Accounting Case Study Project.

Please see attached - is this possible to complete ?

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