Last Week’s Homework

How did you go with last week’s task? Check the spreadsheet to see my solution.

Excel’s What-If Analysis Tool

Have you ever been in that situation where someone asks you numerous times, “what if we did this? What if that happens? What if we went left instead of right?”? Have you ever had to choose between a couple of products based on price and features but had a lot of trouble making the right choice? Excel’s ‘What-If’ Analysis tool provides some features that will ease the stress and pressure around decisions that you make on a daily basis.

To showcase the various features that make up the what-if analysis function of Excel, I’m going to break this post down into three different (main) sections:

• Goal Seek
• Solver
• Sensitivity Analysis

For this analysis series, I am going to imagine that I am a part of the business unit that is responsible for Powerade. I will tailor the financial analysis around this case study.

Goal Seek

Easy Example

When generating a report on potential future sales based on the possibility of economic events, the business unit manager is interested in current sales of the product. So far, Powerade is generating a revenue of $2,990, where the quantity is 1,000 bottles, priced at $2.99 each bottle.

Thinking ahead, I’m interested in knowing how many bottles would need to be sold in order to generate $10,000 in revenue. There’s a few ways we can go about this:

1. Handwriting the algebra and solving the solution,

This doesn’t do us much good for future analysis though, so let’s try another way,

2. Guessing the revenue by changing the Quantity Sold field manually in Excel,

Since I can’t find the value by trial and error, I’m going to try another method,

3. Use Excel’s Goal Seek function

Since I’ve chosen this method to solve this problem, I’m going to set the information to the original state:

Now, we can engage the Goal Seek function to find this point for us – saving us some handwritten algebra time; Tools > Goal Seek:

We want to find out how we can earn $10,000 by selling how many bottles. So, we want to set that revenue cell (B23) to the value 10,000 by changing the quantity sold cell (B21):

Excel’s Goal Seek function has now solved this problem and it shows that, for a price of $2.99 per bottle, we would need to sell 3,344 bottles in order to earn a revenue of $10,000.

The thing is…is this number correct? Let’s have a look at the Quantity Sold value by increase the number of decimal points that can be seen after the whole number of bottles:

This value shows that we need to sell 3,344.48 bottles. Since you can’t see a partial asset to a consumer (…let’s just ignore the whole BitCoin thing for this, shall we?…) so we can enter the ROUNDUP formula into cell B21:

So, in the report to management, you would use this figure with a brief talking point regarding this rounding marginal unit of benefit:

Advanced Example

Since we now know that we have the revenue, we’ve been asked to calculate the profit earned on this $10,000 target. To answer this question, we need to enter the costs that are contributing to the manufacturing of this product.

Since we now have the Variable Cost per Unit and Fixed Costs, we can calculate this information into the income statement. Variable Costs will be the Quantity Sold * Variable Cost per Unit (E28 * E30). Fixed Cost will be the $10,000 listed in the information above. Using this information, we can now calculate the profit using the following formula: Total Revenue – Variable Costs – Fixed Costs:

Since we’re interested in generating a profit in our business, we should determine how many bottles we need to sell in order to generate a profit. Let’s go for $10,000 in profit and we’ll find this by using the Goal Seek function. Before we do this, make sure that the value in cell E28 is a fixed number and not a function, i.e., do not attempt Goal Seek with =ROUNDUP(xxx,0) otherwise the function will not work:

Goal Seek has reported back with an answer of $12,195. But remember to ROUNDUP before the final report:

So, this means that to generate a profit of >$10,000, we would need to sell 12,196 bottles.

Solver

Powerade will capture the market if we increase the price above $2.50. This means we need to institute a constraint on the objective function (profit) such that the price does not increase about $2.50. If you have missed last week’s post about the Solver add-in then I would review that one first before continuing through this portion of the post.

Objective

The objective for this problem was for Profit to be equal to $10,000. So, we’ll enter G51 (the profit cell) into the ‘Set Objective’ parameter.

Next will be setting the ‘Value of’ to that value (10,000).

Changing Variables

The variables that we are interested in controlling and manipulating in this situation are the quantity sold and price per unit. So, let’s select cells G42 and G43.

…But, one of these variables will have a constraint. We don’t want that price per unit to exceed $2.5. So, let’s click ‘Add’ on the right-hand side, next to ‘Subject to the Constraints’.

Constraint

Since we’ve written this information below the question, we’ll use the cells that contain the available data. This is also good practice for later as it allows for easier (and dynamic) manipulation.

Since we have it all set up, let’s click ‘Solve’ on the bottom right-hand side of the Solver Parameters window.

Now, we have a solution. However, remembering what happened last time, we can see that we’ll need to adjust this Quantity Sold cell.

That’s looking better.

Within this report, we have mentioned the new quantity sold, but it would be good to consider a range of values that could have an impact on the profit. We could develop a series of Solver output values, but this would take a while. There’s another approach that we can use.

Sensitivity Analysis

A Sensitivity Analysis (or Data Table) allows us to consider multiple scenarios at any one time and see the various outputs.

Table

First, let’s make a table that allows for some variance in both Quantity Sold and Price per Unit. You can see in the image below that I have used the current optimal solution values of $2.5 per unit and 17,392 bottles.

Price per Unit

For this scenario, I am going to increase and decrease the price per unit by $0.5:

This means that our sensitivity analysis will range in price between $1.5 and $3.5.

Quantity Sold

Next, I’m going to reduce and increase the number of bottles sold by 500 bottles.

Profit Goal

We need to include the Profit Goal in the top left-hand side of the matrix, so I will use the cell G51 as the reference cell:

Setup

Let’s now set up this information in Excel’s Data Table (Sensitivity Analysis). Under the Data tab, click What-If Analysis > Data Table:

Next, we need to input our row information:

Since the top row of the matrix is dealing with the quantity of bottles sold, we will select cell G42 in the question information:

Since the side column of the matrix is dealing with the price per bottle, we will select cell G43 in the question information:

To confirm this, let’s look at our current pricing figure:

Price per Unit is $2.5 and Quantity Sold is 17,392. This gives us $10,001. If we keep our quantity sold the same, but increase our price by $0.5 to $3, we can see a potential increase in profit to $18,697. If we keep our price the same but reduce the quantity sold by 500 to 16,392, we can see that our profit will reduce to $9,426. This is a lot of information to take in, so let’s use some conditional formatting to make it a little bit easier on us:

Homework

How do you think you went with this? I have three questions for you to answer – I’ll upload those answers next week.

1. Goal Seek:
   1. So far you have sat 3 exams and you have received the following scores (out of 100) for Exams 1, 2 and 3: 50, 80, 60. Your final grade is the average of all exams. What will you need to receive on your final (fourth) exam to receive a 70 overall?
2. Solver:
   1. You have 5 exams in your subject. Exam 1 is worth 30%, you can get 30 points, and you received all 30 points (earning 100%). Exam 2 is worth 15%, you can get 25 points, and you received 20 points (earning 80%). Exam 3 is worth 15%, you can get 30 points, and you received 22 points (earning 73%). There are two more exams remaining: Exam 4 is worth 15% and you can get 30 points; Exam 5 is worth 25% and you can get 50 points. You can use SUMPRODUCT to calculate your final Grade (Grade * Weight for each exam). Given that you can only receive 30 and 50 points on your last two exams, what kind of mix of grades can you get to receive 85% overall?
3. Sensitivity Analysis:
   1. Assume the same information in question 2, but you received 26.8125 marks out of 30 (89%) for Exam 4, and 37.1875 marks out of 50 (74%) for Exam 5. How could your outcome for exam 5 be different if you hadn’t known if the exam was going to be 50 marks. What if you heard it could have been from somewhere between 40 and 60 marks?