Hey everyone,
I have two difficulties to finalize my Sensitivity Analysis and hope that someone can help me out.
This is my simplified basis table:
<tbody>
</tbody>
1) My plan was to create a 2-dimensional table by using the "What-if-Analyis". So I create a table with a variety of "Sales per Store" in the top row. And a variety of "Numbers of Stores" on the left column. Then I linked the Operating Profit (307,64) to the upper left cell, marked the whole table and chose the "What-if-Analysis". Then I was asked to link the "Row Input Cell" (= 13,3 from the basis table) and the "Column input cell" (= 368 from the basis table).
The result looks as follows:
Sales per Store
<tbody>
</tbody>
Now I am wondering what went wrong. Usually, the middle line should show my base case from the table above which means: 368 to 13,30 = 307,64. But instead it shows 294,61.
The basis table is very easy linked: Net Sales = Sales per Store * Number of Stores
EBIT = Net Sales - COGS - Selling expenses - Adm. expenses
2) In addition to the 2-deminsional chart I was planning to make an analysis that shows the change in EBIT in accordance with changes in net sales and cost of goods sold (+/- 10%). Therefore, I prepared another chart with two columns, "Change" and "EBIT". The "Change" has three rows and shows 0% / 10% / -10% ; the "EBIT" columns stays empty for the time being. Then I added in the basis table "+0%" to the cell which shows the net sales. As result the line shows "=4907,7+0%". The next step is to link the EBIT of the base case to the "EBIT" column, in the 0% row. Then i select the area of whole table and choose the "What-if-Analysis". In this case, I only need the column to be filled. Therefore, I dont fill anything in when Excel asks me for the "Row Input Cell" but I do fill in the "Column Input Cell" by choosing the 0% cell (which indicated the column which the percent data).
But instead of calculating the outcome of a change in net sales of +/- 10 % Excel calculated the same EBIT for all cases. The result looks as follows:
<tbody>
</tbody>
The bold numer is correct. But the others are obviously not. Now my question: How can that be? What did I wrong?
Thank you very much in advance! I hope my explanation is understandable, otherwise, please ask for more details!
Ellap
I have two difficulties to finalize my Sensitivity Analysis and hope that someone can help me out.
This is my simplified basis table:
Income Statement | Forecast |
NET SALES | 4907,7 |
Number of Stores | 368 |
Net Sales per Store | 13,3 |
Cost of Good Sold | -1976,4 |
GROSS PROFIT | 2931,1 |
Selling expenses | 2406,14 |
Administrative expenses | -217,30 |
OPERATING PROFIT | 307,64 |
<tbody>
</tbody>
1) My plan was to create a 2-dimensional table by using the "What-if-Analyis". So I create a table with a variety of "Sales per Store" in the top row. And a variety of "Numbers of Stores" on the left column. Then I linked the Operating Profit (307,64) to the upper left cell, marked the whole table and chose the "What-if-Analysis". Then I was asked to link the "Row Input Cell" (= 13,3 from the basis table) and the "Column input cell" (= 368 from the basis table).
The result looks as follows:
Sales per Store
307,64 | 10,64 | 11,97 | 13,30 | 14,63 | 15,96 |
294 | -1467,37 | -1075,82 | -684,27 | -292,72 | 98,83 |
331 | -1075,82 | -635,33 | -194,83 | 245,67 | 686,16 |
368 | -684,27 | -194,83 | 294,61 | 784,05 | 1273,49 |
404 | -292,72 | 245,67 | 784,05 | 1322,43 | 1860,82 |
441 | 98,83 | 686,16 | 1273,49 | 1860,82 | 2448,15 |
<tbody>
</tbody>
Now I am wondering what went wrong. Usually, the middle line should show my base case from the table above which means: 368 to 13,30 = 307,64. But instead it shows 294,61.
The basis table is very easy linked: Net Sales = Sales per Store * Number of Stores
EBIT = Net Sales - COGS - Selling expenses - Adm. expenses
2) In addition to the 2-deminsional chart I was planning to make an analysis that shows the change in EBIT in accordance with changes in net sales and cost of goods sold (+/- 10%). Therefore, I prepared another chart with two columns, "Change" and "EBIT". The "Change" has three rows and shows 0% / 10% / -10% ; the "EBIT" columns stays empty for the time being. Then I added in the basis table "+0%" to the cell which shows the net sales. As result the line shows "=4907,7+0%". The next step is to link the EBIT of the base case to the "EBIT" column, in the 0% row. Then i select the area of whole table and choose the "What-if-Analysis". In this case, I only need the column to be filled. Therefore, I dont fill anything in when Excel asks me for the "Row Input Cell" but I do fill in the "Column Input Cell" by choosing the 0% cell (which indicated the column which the percent data).
But instead of calculating the outcome of a change in net sales of +/- 10 % Excel calculated the same EBIT for all cases. The result looks as follows:
Change | EBIT |
0% | 307,64 |
10% | 307,64 |
-10% | 307,64 |
<tbody>
</tbody>
The bold numer is correct. But the others are obviously not. Now my question: How can that be? What did I wrong?
Thank you very much in advance! I hope my explanation is understandable, otherwise, please ask for more details!
Ellap