Assignment - Using Excel to Help Formulate Sales Forecasts

Overview

In this assignment, you will use an Excel spreadsheet to make production decisions and to forecast next round's sales. The purpose of this assignment is to help you see how decisions in Capsim can be approached in a structured and logical manner and to help you recognize the potential power of using Excel to help make these decisions. For this particular assignment, you will be given the spreadsheet, "Exercise 1 Forecasting Fall 2016.xlsx", and a link to a companion video which provides a step-by-step demonstration of how to use this spreadsheet. As the semester progresses, the assignments will become more challenging. Eventually, you should be able to design and construct your own Excel spreadsheets to aid decision-making. However, it is important to recognize that any spreadsheet's effectiveness is limited by the data that is entered into it. Furthermore, the "results" provided by the spreadsheet are only meant to help guide your decisions, not to provide absolute answers. The companion video discusses some of these nuances in greater detail.

Details

Accurate sales forecasting is key to a company's success. Manufacturing too many units results in higher inventory carrying costs. Manufacturing too few units results in stock outs and lost sales opportunities, which can cost even more. Thus, in Capsim, two critical decisions you need to make each round are to choose, for each product, a sales forecast and the number of units to produce. During recitation, it has been emphasized that you should produce to your "best case" and forecast to your "worst case." An Excel spreadsheet will help you construct worst case and best case predicted market share and convert them into unit demand. Please carefully view the short video which provides a step-by-step demonstration of how to use this spreadsheet most effectively. This video also discusses several nuances regarding the rationale behind this spreadsheet and the inherent uncertainty that goes with deciding what information to enter into some of the cells.

In particular, you will enter information into the blue cells of the spreadsheet. The spreadsheet will convert this information into sales forecasts (in units) and production orders. The formulas used to construct these outputs (shaded red or green) can be viewed in the formula bar. The spreadsheet has been locked in order to prevent you from inadvertently changing anything other than the blue cells. However, there is no password, so you can unlock the spreadsheet (by clicking on the "Unprotect Sheet" under the "Review" tab) if you wish to alter the spreadsheet for any reason.

Ultimately, there is much uncertainty as to next round's market share of your product. The forecasts in this spreadsheet are estimated on the basis of your most recent position in the market. Specifically, the customer survey scores determine demand for each month. Your demand in any given month is your score divided by the sum of the scores (see page 8 of your team member guide). The "Predicted Market Share" in the red cell (B4) provides the estimated market share based on the most recent customer survey scores. However, you and your competitor's positions will change throughout the next round. So, this prediction is not perfect. Thus, your "Desired Forecasting Spread" allows you to decide how much cushion to build into your forecasts. The larger your spread, the larger will be your cushion. A larger cushion means there's less likelihood that you will stock out and also less likelihood that you will experience an emergency loan. However, a larger cushion also comes at a cost of larger inventory carrying costs and/or needing to take out more loans.

In addition, the spreadsheet predicts "Long-Term Expected Sales" for 2-5 years in the future. This estimate is entirely based on your most recent position in the market. Although there is inherently large uncertainty whenever you attempt to predict demand more than a year out, such estimates can be very useful in determining whether you may need to buy or sell capacity. The video tutorial discusses how to utilize such long-term forecasts in more detail.

Specific Assignment

Part I

Use the following information to answer parts (a) - (h). You are the product manager for Briny. For the Dec. Cust. Survey, Briny earned a score of 39. You have 5 competitors in the segment, who earned survey scores of 18, 22, 32, 37, and 53, respectively. After last year's sales, 211 units of inventory of Briny remain. Last year, the Total Industry Unit Demand for the segment was 6885. This segment has an annual growth rate of 12.1%. Use a forecasting spread based on 90 days of inventory. Answer the following questions using the predictions that are "Based on Attractiveness" (of Briny and its competitors):

(a) What is Briny's Predicted Market Share?

(b) What is the Worst Case Market Share for Briny?

(c) What is the Best Case Market Share for Briny?

(d) What is next year's Sales Forecast (which would be entered on the Marketing decision page)?

(e) How many units of Briny does the spreadsheet indicate you should to produce next round?

(f) Suppose you were to increase your desired forecasting spread to 120 days. How many units of Briny would you need to produce next round?

(g) What is the expected number of sales of Briny four years from now, i.e., the Expected Sales "in four years"?

(h) If current first-shift capacity is 1500 units, what would your Plant Utilization rate be if your production order equals the expected sales of Briny in four years (as given in part (g))?

Part II

(a) Using your results from the first practice round, construct a forecast for round 2 for your Agape product, making sure to complete all the blue cells in the forecasting spreadsheet. Submit your completed worksheet as an Excel file.

(b) Briefly explain how and why your best case and worst case estimates of market share differ from those based solely on the Dec. Cust. Survey. To receive full credit, you need to explain (1) whether you shifted the forecasts upward or downward AND (2) what specific factors led you to revise the forecast in this particular direction.

Attachment:- Assignment.rar