What If Analysis

  • Thread starter Thread starter Zork
  • Start date Start date
Z

Zork

Wandering what would be the best way to plug in percentages on financials?

For example

Sales xxx 100%
COGS= xxx 70%

Gross Margin xxx 30%

The actual numbers(xxx) and percentages are 2 separate columns. All numbers
are calculated through formulas.

I want to be able to punch in for example, 40% for COGS and automatically
calculate the number? Not sure how to approach this.

Any suggestions?
 
Here's a simple example set-up of a 1-variable data table
to illustrate what-if analysis:

Assuming you have in A2:B4

Sales: 100 (value)
COGS: 40 (the what-if-variable)
Gross M: 60 (a what-if-dependency)

with formula in B4: =B2-B3

Setting up the Data-Table
-------------------------------
Enter in A7:A10

COGS
20
50
70

Enter the col labels in:

B6: Sales
C6: Gross M

Enter in:

B7: =B2
C7: =B4

Select A7:C10

Click Data>Table

In the Table dialog box:
a. Leave row input cell empty
b. For column input cell, enter: $B$3 (or just click on B3)
c. Click OK

(Mask B7:C7 by formatting the font in white, same colour as backgrd)

The data table will calculate the sensitivity of the single dependency
(Gross M) to the single variable COGS.

As Sales is a precedency to COGS, the values under Sales col
will not be affected by the changes in COGS,
i.e. all values in B7:B10 = value in B2, i.e. 100.

But if you change the value in B2,
the data table will recalculate accordingly.

You can also plug in other values under the COGS col
(instead of 20, 50, 70)
which will then be recalculated accordingly in the data table.
 
Back
Top