# Excel Annual Cost Calculator

Enter your current expenses in Scenario A, and revised expenses in Scenario B. Formulas calculate annual totals and differences

## Using the Cost Calculator

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.

## Enter the Time Units

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.

## Enter the Scenario A Amounts

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.

## Enter the Scenario B Amounts

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.

## How It Works

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]]**

## Get the Sample File

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.

## More Tutorials

Named
Excel tables

INDEX
and MATCH functions

Create a drop
down list

Names - Naming Ranges