Forecasting and Best/ Worst Case Scenario Building

Class Exercise

In the simulation you need to balance two risks -- the risk of missed sales because of stock outs, and the risk of building too much inventory. In your worst case, competition is fierce and you end up carrying lots of inventory. In your best case, you sell everything but one unit on each product.The spread between these two positions represents a policy decision. In the worst case, you have lots of Inventory and little Cash. In the best case, you have the opposite, lots of Cash and little Inventory.

If you add the Balance Sheet's Cash and Inventory together, you get a sense for your reserves. A small reserve exposes you to both risks. But a big reserve in itself earns you nothing, not even bank interest. Therefore, you want to keep the reserve as small as possible. With a perfect forecast, your reserve could be tiny. The better your forecasting, the smaller the reserve you require.

Be aware that the computer's unit sales forecast is unreliable, because the computer does not know what competitors have done. Instead it assumes a mediocre product from every competitor in every segment. The computer's forecast is useful for sensitivity analysis. For example, if you drop price 10% and observed a 15% increase in unit sales, that relationship will hold - unless your competitors also drop the price 10%.

Therefore, you need to become proficient at forecasting, and should develop a method to develop a best/worst case for a product.

1. Using any of the methods discussed in class to forecast, develop a set of decisions for each product in R&D, Marketing, and Production. For example,

2. Next, predict a worst-case unit sales forecast for each product. That is, you want to be able to say, "This product's sales could not reasonably be any worse than this number of units." Enter the values in the "Your Sales Forecast" column.

3. Then, predict a best-case unit sales forecast for each product. That is, "This product's sales cannot be better than this number of units." On the production spreadsheet, produce enough so that your starting inventory plus the production will equal your best case forecast.

4. Examine your Balance Sheet. You should observe lots of Inventory and little or no Cash. If your Cash is negative, use Current Debt to bring Cash to a small positive. In your worst case scenario you have a little Cash and lots of Inventory.

5. Examine your Income Statement. You will observe worst-case sales and profits. This is as bad as it can get.

6. Save your decisions.

7. Return to the Marketing Spreadsheet. Enter your best case forecast. Observe that your Balance Sheet will now reflect lots of Cash and no Inventory. The Income Statement will show your best possible Sales (you are selling everything) and your best possible profits.

You are now prepared to answer some important questions. In your responsestake numbers from your spreadsheet's proformas:

1. Record Cash and Inventory from your worst case. How much do they total?

2. Record your best and worst case numbers for Sales. What is the spread between them? For example, in your worst case, total sales might be $120M. In the best, $150M. The spread is $150M - $120M = $30M. Another way to look at this would be in months of sales. $30M/$120M = .25 years or 3 months. Your policy, then, is to carry sufficient reserves to survive a three month build-up of inventory.

3. How much does this spread cost you? Idle cash could be used to pay down debt or improve plant. Inventory carry costs eat into profits. Estimate the cost of the spread as follows. Add together Cash and Inventory. Multiply this by your current short-term debt rate. Add the Inventory Carry Cost from your worst case. Record the result.

4. Suppose you narrowed the spread from say 3 months to 1 month. This would save you the cost of carrying large working capital reserves, but if you were wrong on the downside, you would run out of cash and take an emergency loan. What policy, expressed as months, would you set for your company's reserves?