1. What is the formula for calculating your sales forecast?

2. Where do you get the information needed to fill in the formula?

Below is a Capsim worksheet I created to help you make decisions based on data.

Below is also a video on how to make decisions based on data. Scroll ahead to the Sales forecasting portion of the video and answer these questions.

Please answer the following questions following the review of the video and worksheet.

## Instructions

Instructions | ||||

1 | This workbook pulls formulae suggested in the Team Member Guide; follow the steps in the order they are numbered in each tab. | |||

2 | It’s assumed that you already have a strategy aimed at a particular market segment for each product– either Low End or High End. If you don’t have a strategy and target segment(s) | |||

chosen, then review the Six Strategies PowerPoint to determine the meaning of each strategy, suggested, conceptual direction for decisions, and which segment | ||||

Seems suitable for that strategy | ||||

3 | There are severalTabs representing several different calculations you should make in each decision round in early stages of the simulation | |||

When satisfied with the decisions, then save them and submit. | ||||

See the document I gave on how to make decisions in Capsim, which describes how to use the simulation software. | ||||

4 | All tabs are at the product level of decision making. | |||

Therefore, if you have more than 1 product, you need to make a copy of this workdbook and do the calculations again for your other product(s) | ||||

5 | This workbook is for students doing Practice Rounds. It is to prevent you from being overwhelmed by the complexity of the | |||

information and decisions you must make. You should refine this approach as you gain experience analyzing the data. There is a lot more | ||||

you can do to be successful than these fundamental calculations and decisions. You can even refine the formulae here as you learn more | ||||

about the simulation. | ||||

6 | This spreadsheet is not a substitute for reading the Team Member Guide or Six Strategies document. You should consult it whenever you need to make decisions in each functional category. | |||

7 | There may be more worksheets in this workbook that can display at once. Don’t forget to use the right-arrow and left-arrows to display workbooks. Do this so you | |||

see all worksheets in this file. | The arrows are at the bottom left or bottom right of the workbook. | |||

8 | There may be values in each tab — ignore them and use your own. Watch out for columns that populate from other areas of the worksheet or calculated; don’t type anything into those cells. |

## 1-R&D Decisions

Research and Development Decisions | |

Make sure you are following one of the six strategies your team agrees to. | |

Make these decisions in Capsim’s online decision software | |

The software shows you the new customer expectations for the next year in the form of a perceptual map | |

Position your products so they appear in the ideal spot on the map according to your strategy | |

Make sure you introduce new products early in the simulation, as in Round 1 (this is to ensure you see the benefits of the new product as soon as possible) | |

Also be sure to make sure the revision date for all products is within the next year — not in the year after. If you get a revision date after this next year, be less aggressive in your R&D so your product updates within the next year (round). |

## 2-Marketing Pricing Decisions

Pricing Expectations | Minimum | Maximum | ||

Last Period’s price | 20 | 30 | From Capstone Courier from Desired Segment | |

Price drift rate | -0.5 | -0.5 | ||

Next period’s price | 19.5 | 29.5 | ||

Make sure your price is within the new range above, consistent with your strategy’s direction on price. | ||||

## 3- Marketing Sales Forecast

For marketing decisions, you need to forecast unit sales for each product | Product Daze-Traditional | ||

Sales Forecast for Next Round Calculation: You need a forecast for each product based on their segment | |||

A | Last Year’s Segment Demand | 7387 | From Courier report |

B | Segment’s estimated Growth Rate (%) | 9.20% | From Courier report: Enter as a percent |

C | Expected Segment Demand | 8066.604 | C=A*(1+B) |

D | Estimated Market Share Next Round (%) for the product in question | 13.0% | From Courier report Page 8 Potential Market Share for the product : This is a judgment call — you can use your market share for the product last round, and adjust it. Refine this formula as you get more experience with the simulation. Enter it as a percent as in 15% for example |

F | Sales forecast next round | 1048.66 | F=C*D |

The above number (sales forecast next round) is a starting point estimate. | |||

Its purpose is to give you a rational basis for your sales forecast | |||

You may find you have to be more optimistic than this calculation suggests if your pro formas don’t show acceptable sales, profit, contribution margin, stock price, and market share | |||

Product Able — Low Tech Segment | |||

If you expect to make sales in more than one segment for this product, complete the calculations again for that second segment | |||

A | Last Year’s Segment Demand | 0 | From Courier report — for the segment in which the product competes |

B | Segment’s estimated Growth Rate (%) | 18.30% | From Courier report – from the Courier segment in which the product competes. |

C | Expected Segment Demand | 0 | C=A*(1+B) |

D | Estimated Market Share Next Round (%) for the product in question | 2.9% | From Courier report : This is a judgment call — you can use your POTENTIAL market share for the product last round, and adjust it. Refine this formula as you get more experience with the simulation. |

F | Sales forecast next round | 0 | F=C*D |

Total Sales Forecast | 1048.65852 | This is the sum of the forecasts of the two segments, your final sales forecast |

## 5-Awareness Spending

This is money spent to make people aware of your product. It is called the Promotion budget. | Able | Traditional | |||||||||||

Awareness Dollars to Spend Calculation | |||||||||||||

A | Last Round Awareness | 55% | From Simulation Report for that product | ||||||||||

B | Awareness loss | -33% | Standard as each round advances — from User Guide, page 6 | ||||||||||

C | Starting Awareness next round | 22% | C=A+B | ||||||||||

D | Target Awareness | 60% | Consult the awareness of top competitors and the strategy you are following for that product in the Six Strategies PowerPoint. They will give you an idea. This Is in the Courier | ||||||||||

E | Desired Change in Awareness | 38% | E=D-C | ||||||||||

F | Dollars to spend | $ 1,600 | Look up the desired change in awareness on the y-axis of the chart below. Read across until you hit the curve. Read downward to the X- axis to get your required spending | ||||||||||

Note, new products get a 25% awareness | |||||||||||||

due to the buzz from new products. | |||||||||||||

You can start with row C as 25% for new products | |||||||||||||

Ignore A and B rows for new products | |||||||||||||

See page 6 of the User Guide for more info | |||||||||||||

Spend 1.2 million on Promotion Budget |

## 6-Accessibility Spending

Accessiblity is how easy it is to buy your product. Money spent on accessibility goes to making websites available for purchasing, or more sales people to help customers buy. It is called the Sales Budget | ||||

Here is the formula | ||||

A | Starting Accessibility | 54% | From Courier Report for as specific product | |

B | Desired Ending Accessibility | 60% | Get this by consulting the qualitative description in the Six Strategies document for your strategy, and comparing to your competitors | |

C | Desired change in accessibility | 6% | C= B-A | |

D | Dollars to spend | 900 | From the Sales Budget Accessibilty Curve — read % from row C on the Y-Axis until you reach the curve, read downward to amount you need to spend | |

Spend 2.5 million on Sales Budget (Accessibility_) |

## 4-Production Planning

ABLE | ||||||

Production Plan | ||||||

Sales Forecast | 1049 tc={786FFF30-FC79-4D83-AE94-D9EDF6ED13E9}: [Threaded comment] Your version of Excel allows you to read this threaded comment; however, any edits to it will get removed if the file is opened in a newer version of Excel. Learn more: https://go.microsoft.com/fwlink/?linkid=870924 Comment: | From Sales Forecasting Spreadsheet | ||||

Ending Inventory Last Round | 189 | From Capstone Courier Production Section | ||||

Ending Inventory desired this round | 0 | Make zero if you have too much ending inventory; be conservative to avoid inventory carrying costs | ||||

Production Requirements | 859.7 | If this figure is negative, then produce 0 of this product. If positive, plan to produce the positive figure. |

## 7-Capacity Planning

This spreadsheet is done for each product in your company’s product line. | ||||||

If running a single shift Brent: Brent: If your strategy doesn’t mention whether your should work a second shift, then you should work the first shift only. If you strategy says to prefer second shift overtime to adding first shift capacity, work two shifts, using the calculation area for 2 shifts below this one. | ||||||

Sales Forecast the upcoming round | 1049 | From Sales Forecast you made on the Marketing Sales Forecast Slide- do not change this after you have calculated it earlier. | ||||

Current Capacity | 550 | From Page 4 of the Courier | ||||

Surplus/Shortfall upcoming round | -499 | Calculated | ||||

Segment Growth Rate | 9% | From Segment of the courier in which your product competes | ||||

Forecast Round after this | 1145 | Sales Forecast from D6*(1+segment growth rate above) | ||||

Total Capacity for round after this | 550 | From Cell D7 | ||||

Surplus/Shortfall Round After this | 595 | Cell D10-D11 | ||||

Capacity to Purchase Now | 0 | Type in a value to make cell D14 zero if D13 is negative | ||||

Surplus Shortfall Round After This | 595 | D12+D13 | ||||

If running 2 Shifts Brent: Brent: Use this calculation area if your strategy says to work a second shift rather than adding first shift capacity | ||||||

Brent: Brent: If your strategy doesn’t mention whether your should work a second shift, then you should work the first shift only. If you strategy says to prefer second shift overtime to adding first shift capacity, work two shifts, using the calculation area for 2 shifts below this one. | Sales Forecast the upcoming round | 1049 | Filled automatically from the Marketing Sales Forecast Worksheet | |||

Current Capacity first Shift | 1800 | Entered from Page 4 of the Capsone courier for your product | ||||

Current Capacity running 2 shifts | 3600 | Double the Current first shift capacity since you are running 2 shifts | ||||

Surplus/Shortfall upcoming round | 2551 | d20-d18 | ||||

Segment Growth Rate | 9.2% | From the segment in the Capstone Courier where the product competes | ||||

Forecast Round after this | 1145 | =d18*(1+d22) | ||||

Total Capacity for round after this | 3600 | taken from cell D20 | ||||

Surplus/Shortfall Round After this | 2455 | =d20-d24 | ||||

Capacity to Purchase Now | 0 | If d25 is negative, then plug in a number here that makes cell d27 zero. If D25 if positive, then do nothing. | ||||

Surplus shoftfall, surplus next round | 2455 |

## 8-Automation

For desired automation levels, consult your Strategy in the Six Strategies powerpoint. It gives you a general idea of how much automation you should have for the strategy you are pursuing. | |

Normally, you want a high level of automation if your customer expectations weights low price heavily, as in the Low End market. If you are following a cost leadership strategy, then you will | |

have to invest more in automation than if you follow a differentiation strategy. | |

For differentiation strategies, it’s not good to invest too highly in automation. This is because high levels of automation delay the introduction of new products due to the changes they require | |

in how the company produces them. Further, price is less important to customers whose segment (High End) demands a young product, and strong size and performance ratings. | |

For cost leadership strategies, you should invest heavily in automation. The inflexibility this creates in your R&D cycle time isn’t a big concern due to slow drift rates and the fact that size and performance isn’t | |

critical to customers in segments where a cost leadership strategy makes sense. | |

Do this in the Capsim software — the higher the number, the greater the automation. |

## 9-Finance etc

For financial and other decisions, you are expected to read the Team member guide and Six Strategies document. In the early learning stages of Capsim (as in practice rounds) I would simply play with the Finance numbers. But make | |

sure you finance your operations so you have positive cash flow and net income in the pro formas. These summarize the impact of your decisions described in this spreadsheet. | |

Strive to have a cash cushion that is at least 10% of sales. | |

As you learn more about the simulation, you will learn how to make financial decisions. | |

For now, read the Finance Section starting on Page 8 of the user guide. | |

Let your Pro Forma Net Income/Cash flow be your guide at this early stage, striving for a poistive cash position about 10% of sales after you make the decisions in this workbook. | |

If you end up with negative cash flow, then you have to raise more funds to fund your production, sales, promotion or capacity investments, raise prices (if possible) or make other adjustments. Keep recycling through | |

the various decisions until you get a nice cash cushion in your prof formas |