Chat with us, powered by LiveChat ? 1. What is the formula for calculating your sales for - Study Help


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.?


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

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:

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 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


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
error: Content is protected !!