How to create a sensitivity analysis

When people are creating a new forecast for a company that is just starting they sometimes freeze up because they do not know what values to assign to certain assumptions because the business is new.  If the business has started you should use actuals and the trend. If it has not then I would recommend just starting with your best guess. No forecast is ever correct so do not worry if you don’t know or are unsure. You can research different companies metrics or cost per click costs which are typically $1 to $5 depending on website (google, FB or yelp) for example. I also recommend building a sensitivity analysis, which shows a bunch of scenarios at the same time so you can see different outcomes if your cost per click is $1 or $5 for example.

The other variable is how much much people will pay for your product and what your revenue will be.  It is hard to determine the price and you may start by giving your product away for free and then increasing costs as you get traction.  Since this is a key variable and it is hard to know it is a good idea to include it in a sensitivity analysis.

Here is an example – that shows you how to create a sensitivity analysis.  As the cost / click decreases and the revenue / sale increases the profitability increases.  It also shows that the revenue / sale needs to be over $100 to be profitable and a breakeven at $5 cost / click and $100 revenue / sale.

sensitivity analysis CPC

Here is a screenshot of the detailed model.  If you would like me to email you a copy so that you can see the formulas please send an email to paul@financepals.com.  It was built using excel’s data table functionality and I would be happy to walk you through it.

sensitivty cpc detail

 

Leave a Reply

Your email address will not be published. Required fields are marked *