Enter your current expenses in Scenario A, and revised expenses in Scenario B. Formulas calculate annual totals and differences
The sample workbook calculates the difference in cost for each item,
and the overall difference.
To see how the annual cost calculator works, you can watch this short video. The written instructions are below the video.
In this template, there is a Lists sheet, where the time units are entered. You can adjust the number of work days per week, and the number of work weeks per year, to match your schedule.
B2:B8 is a named range -- TimeUnits
C2:C8 is a named range -- TimeAnnual
These named ranges are used as a lookup for the cost per year calculations.
On the CostPerYear worksheet, enter your current spending, by filling in the green cells for Scenario A.
There is a drop down list of time units in column E, and you can select an item from that list. The list is based on the TimeUnits range on the Lists sheet.
In the Max Units column, you can adjust the maximum occurrences for an item. In the screen shot above, Golf is entered as a Weekly item, and the Max Units is set at 25, because the expense occurs only during the summer weeks.
Next, enter your revised spending plans in the green cells for Scenario B. The item name is linked to Scenario A, and you can enter a different cost, quantity, time unit, or maximum units.
The difference between the two scenarios is shown at the far right.
To see how the formulas are set up, you can watch this short video. The written explanation is below the video
To calculate the annual quantity, a formula checks the Max Units column, and uses that number, if one is entered. Otherwise, it looks up a number from the time units table.
Then, that number is multiplied by the quantity.
Because the scenarios are in named Excel tables, there are field names instead of cell references. The INDEX and MATCH functions are used for the lookup.
Here is the formula for the Annual Qty column:
=IF([@[Max Units]]<>"",[@[Max Units]],
IFERROR(INDEX(TimeAnnual,
MATCH([@[Time Unit]],TimeUnits,0)),0))
*[@Qty]
The Annual Cost is the Annual Qty multiplied by the
Cost.
=[@Cost]*[@[Annual Qty]]
To get the zipped sample file, in xlsx file format, click here: Excel Annual Cost Calculator. The CostPerYear sheet is protected, with no password. There are no macros in the file.
Last updated: July 12, 2021 7:04 PM