Excel has a variety of useful functions which individually have limited use, but when combined they are enormously powerful.
In my video showing the reporting model for my imaginary company BAR Fruits Ltd, I showed how a monthly reporting model could be made more powerful through a What If table, to look at possible year end outcomes.
The 2 main variables effecting the full year forecast are sales and gross margin percentages for the rest of the year. These are converted to ASSUMPTION VARIABLES cell reference, and then used to flex the Budget for the remainder of the year, to give a revised full year forecast (Actual YTD plus Forecast and not Budget).
These VARIABLES are then used in a TWO-VARIABLE DATA TABLE. The FORMULA cell (top left cell of the table) is a CHOOSE function which give a range of alternative full year What If’s. These are returning to budget or other targets, from a predefined DROP DOWN FORMS LIST. Thus gives a useful cross table showing the full year impact of changes to the variables.
However, this table is very static, and not interactive. Excel’s GOAL SEEK can calculate a known output figure, where the input value is not known. For example, what % uplift in sales is required to get back to full year budget? This function is limited by being single action only.
However, by recording a MACRO, the standard table can be refined to give multiple GOAL SEEK ‘s across the diagonal either for required Sales at specified Margin Change Assumptions. e.g. if margins change by 1% what would the % sales change required to eliminate the year to date EBIT variance. You can then record a second MACRO for the required Margins and specified Sales change assumptions, and a Reset MACRO to return the figures to the base assumptions.
To finalise this, I have then added SPINNERS so that you can manually change the two VARIABLES, so that the effect of the changes can be shown on screen or projected for further discussion.
Experiment yourself, but the combination of a two-variable Data Table, with Choose, Macros and Goal Seek, to produce a dynamic What If table, is very powerful.