When someone asks you to calculate a percent of goal, she is simply saying to compare actual performance to a stated goal. The math involved in this calculation is simple: Divide the goal by the actual. This gives you a percentage value that represents how much of the goal has been achieved.
For instance, if your goal is to sell 100 widgets, and you sell 80, your percent of goal is 80 percent (80/100).
Percent of goal can also be referred to as percent of budget or percent of forecast; it all means the same thing.
In the figure, you see a list of regions with a column for goals and a column for actuals. Note that the formula in cell E5 simply divides the value in the Actual column by the value in the Goal column.
=D5/C5
How it works
There isn’t much to this formula. You’re simply using cell references to divide one value by another. You just enter the formula one time in the first row (cell E5 in this case) and then copy that formula down to every other row in your table.
Alternative: Using a common goal
If you need to compare actuals to a common goal, you can set up a model like the one shown. In this model, each region does not have its own goal. Instead, you’re comparing the values in the Actual column to a single goal found in cell B3.
=C6/$B$3
Note that the cell reference to common goal is entered as an absolute reference ($B$3). Using the dollar symbols locks the reference to the goal in place, ensuring that the cell reference pointing to your common goal does not adjust as you copy the formula down.