Oracle Crystal Ball

Product Forecasting Model

Marketing Team

Binary Semantics Ltd.

Plot No: 38, Electronic City

Sector – 18. Gurgaon – 122015

Haryana, India marketing@binarysemantics.com

www.binarysemantics.com

Case Study

• Product Marketing and Forecasting Analysis of an emerging

media product, Interactive TV (ITV).

• Colorado Cable has created a discounted cash flow (DCF)

analysis that examines the success of the product over a six-

year period.

• Monte Carlo simulation and time-series forecasting are used

to provide a greater understanding and quantification of the

risks inherent in a spreadsheet-based business forecast.

Quick View

• Colorado Cable, a local cable provider, is evaluating a new technology known

as Interactive TV (ITV). ITV will provide content - movies, sports games, and

news - on demand. Colorado Cable believes that the local audience will

embrace this type of service, but the company is concerned that there might

be some unanticipated risks because of the down economy.

• Management has requested from you a forecast model for ITV from its

introduction in 2004 through to 2009. They want to better understand the

sales and marketing potential of this new technology before making a hefty

investment. Does ITV have enough potential to merit bringing it to market?

What is the Net Present Value (NPV) over six years? What are the key

success factors driving the bottom-line performance of the new product?

Problem Summary

• Your model examines four products: cable, satellite dish, broadcast TV, and

ITV. The first three products are already offered by Colorado Cable. The

model estimates the number of households with TV for each future year and

the market share and market size for each of the four products. Based on

twenty years of historical data, you have estimated a constant growth rate

of 50,000 new households with TV per year, although you know that the

actual rate of increase is somewhat less predictable. You will address that

aspect of the model later with time-series forecasting methods.

Problem Summary

• You have made the assumption that while the demand for satellite TV will

grow at a slow, steady rate, ITV will draw its growing share of viewers from

households with either cable or broadcast TV. You expect operating costs

for ITV to grow each year, and that while uncertain, the initial investment in

2004 for ITV will average $100 million. You also expect to see an annual

revenue increase of $5 per household. Finally, the model calculates the ITV

revenues, expenses, net profit, and Net Present Value (NPV). Your original,

or base case, NPV estimate for ITV is slightly more than $56 million (with a

10% discount rate).

Problem Summary

• Crystal Ball enhances your Excel model by letting you create probability distributions that describe the uncertainty surrounding specific input variables. This model includes 13 probability distributions, referred to in Crystal Ball as "assumptions." Six assumptions describe the uncertainty around the market share of ITV, and six others describe the uncertainty around the annual operating expenses for ITV. The final assumption describes the uncertainty for the Initial Investment in 2004.

• Each assumption cell is colored green and is marked by an Excel note (mouse over the cell to view the note). To view the details of an assumption, highlight the cell and either select Define Assumption from the Define menu or click on the Define Assumption button on the Crystal Ball toolbar. The ranges and types of assumptions are based on your market research.

Using Crystal Ball

• This model also includes one Crystal Ball forecast, shown in light blue.

Forecasts are equations, or outputs, that you want to analyze after a

simulation. During a simulation, Crystal Ball saves the values in the forecast

cells and displays them in a forecast chart, which is a histogram of the

simulated forecast values. In this example, you want to analyze the Net

Present Value. To view a forecast with Crystal Ball, highlight the cell and

either select Define Forecast from the Define menu or click on the Define

Forecast button on the Crystal Ball toolbar.

Using Crystal Ball

Screenshot

• When you run a simulation, Crystal Ball generates a random number for each

assumption (based on how the assumption has been defined) and places

that new value in the cell. Excel then recalculates the model. You can test

this by selecting Single Step from the Run menu or clicking on the Single

Step button on the Crystal Ball toolbar.

• After you run a simulation, you will see the NPV forecast chart, which you

can use to analyze the potential success of ITV. What is the mean NPV?

What is your certainty of making $51 million (your original prediction)?

What is the certainty that you may lose money on this product? You can

view the statistics and percentiles of the forecast, too. Are the mean and

median values close? If not, can you tell why, and is it important to your

forecast?

Using Crystal Ball

Screenshot

• To view which of the assumptions had the greatest impact on

the NPV, use a sensitivity chart. Which assumptions most

affect this forecast? What effect does the Initial Investment

have on the NPV? Do the market share assumptions have a

greater or lesser effect than the operating cost assumptions?

Are your market share assumptions reasonable? What will

happen if you reduce the ranges of these distributions and re-

run your simulation?

Using Crystal Ball

Screenshot

• To run Predictor, select any cell within the data series you plan to forecast (to the right of the model) and open the program from the Run menu. The first four steps help you to define, organize, and view the data. An autocorrelation feature determines if any seasonality (shown as lags) is present in the data. In this annual data, there is no seasonality, just an increase in number of households.

Using Crystal Ball Predictor

Screenshot

Screenshot

Thank You!!

Contact us for Oracle Crystal Ball:-

Marketing Team

Binary Semantics Ltd.

Plot No: 38, Electronic City

Sector – 18. Gurgaon – 122015

Haryana, India marketing@binarysemantics.com | www.binarysemantics.com