Formulas are very important when working as a financialanalyst, as you need to make sure you are using the correct formulaconstruction. There are many methods to construct a formula for aparticular outcome, so understanding is very important when calculatingto ensure you arrive at the correct answer. In addition, the simplestmethod is always the best, rather than typing a long, complicatedformula to arrive at the same result. Choose to use the most efficientmethod.
Let’s have a look at how we would calculate the percentage change. Wehave ACTUAL SALES figures for ourproducts in cells C16:C20 of the worksheet, and
- Actual sales minus budget divided by budget
- Actual sales divided by budget minus budget divided by budget
So, to elaborate, you would use this formula in this particularexample: =C16/D16-D16/D16, which can be simplified toactual sales divided by budget – 1,
We will continue using the ConvertingValues.xlsx workbookfor this example. Proceed as follows:
- Click on cell E16.
- Type the following formula, clicking on the relevant cell referencesas you formulate it: =C16/D16-1.
- Place the mouse pointer at the bottom right-hand corner of thecell.
- The AutoFill handle (the +crosshair pointer) will appear; then, double-click to fill the formuladown to the rest of the cells. The result can be seen in the followingscreenshot:
- You are now able to view the percentage change in cells E16:E20 andcan adjust actual and budget projections where necessary, and the changewill be reflected in the % CHANGEcolumn. If we adjust cell C17 to read 65,000.00 as the
ACTUAL SALES figure, then % CHANGE will update to a positive4.839%.
In the next topic, we will concentrate on working out the percentageincrease or decrease.