Sensitivity Analysis of Excel data before starting a new business
I’m planning to start a cybercafe in a newly built mall with great potential. But before I rent the place I wish to estimate my variable costs, revenues and profit and how these will vary with the price I charge per hour for using the cybercafe. First I compute the annual revenue using the formula ‘=b1*b2’. The profit is calculated using the formula ‘=(b1*b2)-b3-b4’ i. e. ‘=revenue-fixed costs-variable costs’. Now I can find out how my annual profit will vary depending on the change in the surfing charges. Since I’m going to change only the price input, a one-way table should be able to provide a solution. I first vary the surfing charges in increments of 0.15 units and input them in Excel cells b11 to b25. Next we apply the headings revenue, variable costs and profit in cells c9, d9 and e9. Below the headers we apply the formulas ‘=b5’, ‘b4’ and ‘b6’ respectively. Now we select the range b10:e25, click on the ‘Data’ tab in the ribbon, click ‘what-if’ and in the data table that pops up select the cell b1 because this is the value that we wish to vary and get our related values for revenue, variable costs and profit.