Four years ago I was reading a business plan and I saw something called a sensitivity analysis table that baffled me. It was a beautiful table that showed the profitability of a music festival at different numbers of attendees and price points. The data table showed dozens of profit possibilities in one table, with the different price and attendee numbers laid out across the horizontal and vertical axes. I thought to myself, “that looks complicated, maybe one day after working in Excel for years I’ll be able to do that.” However, the reality is that these tables are quite easy to set up. By the end of this blog post you’ll be able to easily perform what-if-analysis. What is what-if-analysis in Microsoft Excel? What-if-analyses are used to evaluate complex “what if?” scenarios in Microsoft Excel. When we perform a what-if-analysis, we can better understand the variability (risk) of results for a business or data model. Use casesThese types of analyses help us examine questions like: - What happens to profit if we lower the price and spend more on marketing at the same time?
- What does profit look like at 10 different combinations of price & marketing spend fluctuation?
- At what price will we acquire exactly 1000 customers?
- What price do we have to charge at different numbers of customers to achieve profit targets?
These questions are very complex, but with what-if-analysis we can create reports and answer these types of questions in seconds. Basic structure & setup for what-if-analysisWe’re either changing inputs to solve for outputs, or changing outputs to solve for necessary inputs. And to perform this type of analysis, we need to have a data model of some kind, say, a budget, with at least one “hard coded variable” (meaning non-formula, just a typed in number). Two major types: goal seek & sensitivity analysis data tablesThere are two main types of what-if-analysis methods: - Goal seek
- Sensitivity analysis data tables
Let’s start with goal seek. 1. Goal seek Goal seek allows us to solve inputs to achieve whatever outputs we decide we want. To start, we’ll need a simple data model with an inputs and an outputs section. Let’s review the following financial model for our table company in order to perform our analysis. In the Inputs Section, we have our hard coded assumptions. Unit costs: sale price, units sold, manufacturing costs, customer acquisition costs (CAC). Fixed costs: rent.In the Outputs Section we have a financial model that is driven by the inputs. In the model:- Revenue = Sale Price * Units Sold
- Cost Of Goods Sold = Manufacturing Costs * Units Sold
- Marketing = CAC * Units Sold
- Rent = Rent
Our gross profit is $90,000 and our left over profit (net) is $15,000 at a 10% profit margin. Now let’s use goal seek to answer some questionsQuestion 1: How do we get to a 20% profit margin? We can approach this question in a variety of ways. In order to achieve 20% profit, we could change price, units, manufacturing costs, customer acquisition costs, or rent. Let’s solve for different variables using goal seek. Solving for price: What price gets us to 20% profit? First navigate to the data tab, and on the far right you’ll see what if analysis. Click what-if-analysis and then goal seek. We have three boxes: **Set cell:**the thing we’re trying to get. So in this case, it’s the 20% profit margin. So click the corner of “set cell” on the diagonal arrow and click the profit percent cell in the**Outputs Section**. Now click the arrow again to expand the Goal Seek box.**To value:**20%. We want to set the profit margin cell at 20%.**By changing cell:**we want to get to 20% profit by changing the price. So now click the corner of “by changing cell” on the diagonal arrow and click the price cell in the**Inputs Section**. Now click the arrow again to expand the Goal Seek box.
Click OK. So you’ll see that we get 20% profit if we can just raise the price to $562. At this point, we can easily solve for this same 20% profit output by solving for other inputs, like: units sold, manufacturing costs, customer acquisition costs (CAC), or rent. Just follow the same steps. Let’s try one more example with goal seekQuestion 2: How can we get to $250,000 in gross profit? In this scenario, the only variables that will change gross profit are price, units, or manufacturing costs (because gross profit = revenue - cost of goods sold). Let’s solve for price using goal seek. Solving for price: What price gets us to 250,000 in gross profit? Navigate to goal seek again and fill in your three boxes. We have three boxes: **Set cell:**in this case, it’s the 250,000 gross profit. Select the gross profit percent cell in the**Outputs Section**. Now click the arrow again to expand the Goal Seek box.**To value:**$250,000. Just type in 250,000 manually here. We want gross profit to equal this number.**By changing cell:**we want to get to $250K in gross profit by changing the price. So now select the price cell in the**Inputs Section**.
Click OK. So we would need to change the price to $1283 in order to earn $250,000 in gross profit. Now we can easily solve for this same $250K in gross profit by solving for units sold. Just follow the same steps. Limitations & Summary of Goal SeekIf we solve these inputs, and we actually knew this company, we’d immediately recognize what is realistic and what is not realistic. Maybe we just can’t drive marketing costs down, or it’s ridiculous for us to charge $1283 for our product. But this analysis gives us an idea of what if would take and what the available options are for running the business. However, the limitation of this type of analysis is that everything is held constant except your one input you are solving for. It’s probably not reasonable to just raise the price to achieve the profit margin of 20%. But if you can raise prices a little, and lower manufacturing and marketing costs, maybe you can hit your target. But Goal Seek won’t help you with this type of analysis. For more complex questions with multiple moving pieces, we need to use the data table feature known as sensitivity analysis. 2. What-If-Analysis Data Table (Sensitivity Analysis)Sensitivity analysis data tables show us what outputs we’ll achieve at combinations of multiple changing inputs. So as we change the environment dynamically, how does the profitability of our company change? Once we jump into our example, you’ll see that this concept is pretty easy. Let’s use a data table to evaluate whether or not we should raise the price of our productLet’s use the same table company financial model from our goal seek example. Question 1: We’re thinking about charging more per table, but we know this may result in selling fewer units. We want a higher profit margin %. Our current profit percent is 10%. Should we do this? So we need to build a table showing the profit margin % at different unit and price combinations. And the key here is to make sure your assumptions are reasonable. In our current financial model, we’re selling 300 units, at $500 per table. So we want to look at higher prices which will result in lower units sold. So the maximum units should be 300, and we should look at lower numbers from there. How to set up the sensitivity analysis data tableIn order to populate the data table, you’ll need to set the table up like this, although you can format (colors, borders, fonts…etc.) however you want. I use borders and font colors to make it more clear. The important elements are that you have: - One assumption category along the top of the data table (units)
- A different assumption category along the left side of the data table (price)
- The top left corner of the table blank (labeled profit %)
You’ll need to fill in the assumptions with numbers that are reasonable, meaning they reflect a realistic possibility. You fill them in by just “hard coding” (typed in, no formulas) the assumptions you want to evaluate. Now in the top left corner, link in the metrics that you want to sensitize, so in this case, it is profit %. =Profit % Cell Now inside the table, you have different assumption combinations. The data that will populate the table will be the different profit percent for each combination. Now let’s perform the calculation to analyze (sensitize) profit % in our data tableFirst highlight the whole table including the assumptions. Now navigate to the Data tab, click What-If-Analysis and select Data Table. We have two input boxes: **Row input cell:**this is the units assumption along the top row of the table. So click the corner of “row input cell” on the diagonal arrow and click the unit cell in the Inputs Section of the original model. Now click the arrow again to expand the goal seek box.
**Column input cell:**this is the price assumption left column of the table. Click the corner of “column input cell” on the diagonal arrow and click the price cell in the Inputs Section of the original model. Now expand the box again.
Now click OK. Now quickly, I’m going to apply a percent format to the data table for newly populated profit percent data. Here’s what it looks like. Interpreting the results: can we raise the price of our tables to achieve a higher profit %?Our original goal was that we wanted to raise our profit margin % by raising the price. Does this seem reasonable? The results are interesting. If we could raise the price from $500 to $600 per table, we could run a 17% profit margin at 200 units (a reduction of 100 units). So with just a 20% price increase, we could withstand decreasing our unit sales 33% while still increasing our profit margins from 10% to 17%. The conclusion? It looks like we could potentially find a solution in our data table that is reasonable in the real world. I would recommend raising the price slightly as it seems the profit margin is not extremely sensitive to changes in unit sales. Limitations & summary of sensitivity analysis data tablesThe power of data tables comes in the ability to see dozens of possible outcomes at the same time. It helps to drive you strategically in a direction. Very quickly, you will notice whether you should be pushing to raise the price knowing that unit sales will drop, or lower the price to increase unit sales. It can help you optimize your decisions. And if we actually knew this company, we’d immediately recognize whether or not the results are realistic. Maybe we are in a very competitive industry and cannot raise the price because our largest competitor sells the same table for $500. But these data tables give us an idea of how the outcome we are analyzing will be impacted in a dynamic environment. The limitation of sensitivity analysis data tables is the human component – the assumptions. If your assumptions are not reasonable, then your results will not be informative. ConclusionWhat-if-analysis in Microsoft Excel is one of the fastest and most powerful ways to evaluate dozens of hypothetical situations at the same time. These techniques are also easy to learn - you should be a pro now. Goal seek is used to analyze one assumption when targeting an specific outcome.When you need profit margin to be a specific number, and you are only planning to change price, use goal seek. Sensitivity analysis data tables are used to analyze assumption combinations when targeting a specific outcome.When you need profit margin to be a specific number, have the ability to change price and manufacturing costs simultaneously, then you should set up a data table to see what happens to profit in many “what if” situations at the same time. To go deeper with what-if-analysis and the rest of Microsoft Excel, join the army of students on Udemy.com taking my video-based course: Become An Excel Power User in 2.5 Hours. Take a look below!READY TO MASTER EXCEL? ENROLL USING THE LINK BELOW TO SAVE 50%.
0 Comments
## Leave a Reply. |
## ABOUT ERIC ANDREWS
Eric is a senior financial analyst and business intelligence consultant with over 5 years of experience in the finance, technology and education industries. ## Archives
June 2017
## Categories |