Sign in or start a free trial to avail of this feature.
10. Update Warnings for Charts
Some charts, such as the Tornado, won't update when we change a control panel input. As a safety mechanism, we'll build in warnings that tell us when our chart data has not updated.
Update warnings for charts
- Some charts (e.g. Tornado charts) don't update automatically after changes in control panel
- This can lead users to plotting the wrong chart unless a warning is given
- To create this warning, run a test to see if the chart inputs correspond to the control panel values
- If the test fails, the title on the chart will flag a warning
AND(logical test 1, logical test 2, etc): Returns TRUE only if all logical tests are TRUE
- Can used effectively with IF to create multiple logical tests
The one drawback from building tornado charts from scratch is that the chart doesn't update automatically when we make changes in the control panel.
This is because in a previous lesson we needed to break the link between the inputs to our chart and the control panel so that the data table would work correctly.
Now, however, when we change a variable, for example, market price to let's say $3 dollars, we don't get the desired results in our tornado chart.
As you can see, the minus 10% percent value stays at 252, when we would expect it to be $2.70 which is $3 dollars minus 10% percent.
To make sure that I don't display the wrong chart, I'd like to create warnings that tell me when my chart data needs to be updated.
To do this, we'll write a simple formula that checks that the latest version of the input in the control panel, plus or minus 10% percent, is equal to the input for our tornado chart.
Let's start with the market price.
So I'll write an IF function that checks if the input to my tornado chart is equal to the market price multiplied by the change in percentage which is one plus the change.
I'll then close the bracket and if this is true, I'll return TRUE and if it is not true, I'll just return FALSE. Then I'll close the bracket and press Enter.
If this returns false, because my 252 value comes from the old price which is $2.80, and the new value should be $2.70.
I'll now repeat this formula for variable cost.
So again I'll write an IF statement that will check if our input is equal to the variable cost multiplied by the percentage change.
And if it is, I'll return True, and if it isn't, I'll return False.
You can see that this value is still true because I haven't made a change to the unit variable cost in the control panel.
Off camera, I'll repeat this formula for the remaining three variables.
We now have five checks on each of our variables to make sure that our control panel and the input data for the tornado chart match correctly.
When we see a false flag we can either update these two values with the new control panel input, or we can move the control panel back to its original value.
So for example, if I move my market price back to $2.80 from $3 dollars, my flag will move from False to True.
While these five checks are clear in the model, it would be nice if we had some sign on our chart to tell us whether our values are updated or not.
And I do this by changing the title of the tornado chart.
For the title of the tornado chart to be dependent on certain values, we need to link it to a cell. And we'll link it to the cell that's directly above the chart title, cell B30.
And in cell B30, I'll write an IF statement.
And this IF statement will check if our five values are true.
And to do this, I'll use an AND function.
So I'll write AND, and I can simply enter the location of each of these cells.
If any of the cells return false then AND will return False and our chart should be updated.
And so the cell references are, K33, K35, K37, K39, and K41.
And if the AND function returns True, I'll then return a string which corresponds to the current chart title.
And if the AND function returns False, I'll simply return a title that says, needs to be updated.
I'll then close the bracket and press Enter.
Now all I need to do is to link the chart title to this cell, so I'll select the chart title and then type equals and find the location of that cell and then press Enter.
Now let�s test this out by changing one of our input values.
So I go back to the control panel and I'll change my price to $3 dollars.
And when I do this, you can see that the flag returns False and the chart title says, needs to be updated exactly what we want.
When I change it back to the original value, the chart returns to the original title.
While this isn't the ideal solution for updating tornado charts, it does ensure that a flag is created every time the input values in the control panel don't tally with the input values for our tornado chart.
In the next lesson, we'll move on from tornado charts and begin the second iteration of our model adding taxes to the operating cash flows.