Profitability Analysis of a Company - Excel

Document Sample
Profitability Analysis of a Company - Excel Powered By Docstoc
					    Palisade Corporation                                     HippoSensitivity: Advanced Sensitivity Analysis Example Model

New Product Profitability: Advanced Sensitivity Analysis
When a company develops a new product, the profitability of the product is highly uncertain. Simulation is an
excellent tool to estimate the average profitability and riskiness of new products. The following example
illustrates how simulation can be used to evaluate a new product. Imagine Pigco is thinking of marketing a new
drug used to make hippos healthier. The model below sets up the variables involved in marketing the new
product, such as market size, use of the drug, whether competitors enter the market, etc. @RISK distributions
(shown in green) are used to illustrate the uncertainty. We will make C32, the NPV of our 5 year profits, our
output cell. Analyzing the results of this output will help Pigco decide whether introducing the hippo drug would
be profitable or not.
Advanced Sensitivity Analysis:
If any of the inputs in C16-C19 or E16-E20 can be determined with greater precision, Advanced Sensitivity
Analysis can help decide which ones to focus on. For example, one can set up an analysis stepping through 7
values for each of the inputs in the range of +/-10% from the current cell value. If mean is selected as the
"tracking statistic", the Price and Year 1 Market Size stand out as the most significant in the tornado diagram in
the report workbook. On the other hand, if the tracking statistic is standard deviation, the tornado diagram
shows Year 1 best share as the most significant input, followed closely by Price and Year 1 Market Size. On
this basis a decision can be made to research these 3 inputs more.
This example was taken from Chapter 28 of Financial Models using Simulation and Optimization by Wayne Winston,
published by Palisade Corporation, where a detailed, step-by-step explanation can be found. It is also explained further in
the @RISK User's Guide.

Price              $           2.20    Compet %age                            0.2
Unit Var Cost      $           0.40    Year 1 Market Size                1000000
Interest Rate                    0.1   Year 1 worst share                     0.2
Entrant Prob                     0.4   Year 1 most likely                     0.4
                                       Year 1 best share                      0.7

Year                      1                    2                      3                     4                       5
Market Size               1000000           #NAME?                 #NAME?                #NAME?                  #NAME?
Use per hippo          #NAME?               #NAME?                 #NAME?                #NAME?                  #NAME?
Competitors                     0           #NAME?                 #NAME?                #NAME?                  #NAME?
Entrants               #NAME?               #NAME?                 #NAME?                #NAME?                  #NAME?
Unit Sales             #NAME?               #NAME?                 #NAME?                #NAME?                  #NAME?
Revenues               #NAME?               #NAME?                 #NAME?                #NAME?                  #NAME?
Costs                  #NAME?               #NAME?                 #NAME?                #NAME?                  #NAME?
Profits                #NAME?               #NAME?                 #NAME?                #NAME?                  #NAME?

NPV                    #NAME?

                                                               Page 1

Description: Profitability Analysis of a Company document sample