Capsim 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


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. 


1This workbook pulls formulae suggested in the Team Member Guide; follow the steps in the order they are numbered in each tab.
2It’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
3There 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.
4All 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)
5This 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.
6This 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.
7There 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.
8There 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 ExpectationsMinimumMaximum
Last Period’s price2030From Capstone Courier from Desired Segment
Price drift rate-0.5-0.5
Next period’s price19.529.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 productProduct Daze-Traditional
Sales Forecast for Next Round Calculation: You need a forecast for each product based on their segment
ALast Year’s Segment Demand7387From Courier report
BSegment’s estimated Growth Rate (%)9.20%From Courier report: Enter as a percent
CExpected Segment Demand8066.604C=A*(1+B)
DEstimated Market Share Next Round (%) for the product in question13.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
FSales forecast next round1048.66F=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
ALast Year’s Segment Demand0From Courier report — for the segment in which the product competes
BSegment’s estimated Growth Rate (%)18.30%From Courier report – from the Courier segment in which the product competes.
CExpected Segment Demand0C=A*(1+B)
DEstimated Market Share Next Round (%) for the product in question2.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.
FSales forecast next round0F=C*D
Total Sales Forecast1048.65852This 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.AbleTraditional
Awareness Dollars to Spend Calculation
ALast Round Awareness55%From Simulation Report for that product
BAwareness loss-33%Standard as each round advances — from User Guide, page 6
CStarting Awareness next round22%C=A+B
DTarget Awareness60%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
EDesired Change in Awareness38%E=D-C
FDollars to spend$ 1,600Look 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
AStarting Accessibility54%From Courier Report for as specific product
BDesired Ending Accessibility60%Get this by consulting the qualitative description in the Six Strategies document for your strategy, and comparing to your competitors
CDesired change in accessibility6%C= B-A
DDollars to spend900From 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

Production Plan
Sales Forecast1049
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:

This can be the value from the Create Sales Forecast worksheet, or a more optimistic value, depending how your first attempt at pro formas look

From Sales Forecasting Spreadsheet
Ending Inventory Last Round189From Capstone Courier Production Section
Ending Inventory desired this round0Make zero if you have too much ending inventory; be conservative to avoid inventory carrying costs
Production Requirements859.7If 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 round1049From Sales Forecast you made on the Marketing Sales Forecast Slide- do not change this after you have calculated it earlier.
Current Capacity550From Page 4 of the Courier
Surplus/Shortfall upcoming round-499Calculated
Segment Growth Rate9%From Segment of the courier in which your product competes
Forecast Round after this1145Sales Forecast from D6*(1+segment growth rate above)
Total Capacity for round after this550From Cell D7
Surplus/Shortfall Round After this595Cell D10-D11
Capacity to Purchase Now0Type in a value to make cell D14 zero if D13 is negative
Surplus Shortfall Round After This595D12+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 round1049Filled automatically from the Marketing Sales Forecast Worksheet
Current Capacity first Shift1800Entered from Page 4 of the Capsone courier for your product
Current Capacity running 2 shifts3600Double the Current first shift capacity since you are running 2 shifts
Surplus/Shortfall upcoming round2551d20-d18
Segment Growth Rate9.2%From the segment in the Capstone Courier where the product competes
Forecast Round after this1145=d18*(1+d22)
Total Capacity for round after this3600taken from cell D20
Surplus/Shortfall Round After this2455=d20-d24
Capacity to Purchase Now0If 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 round2455


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

Looking for this or a Similar Assignment? Click below to Place your Order