This workbook shows how to calculate annual costs and savings in Excel. It’s amazing how all those little expenses can add up over a year.
For example:
- Upgrade your cable package for an extra $30 per month, and that’s $360 more per year.
- Buy your lunch for $15 each workday, instead of bringing a $5 lunch from home, and you’ve added $2500 to your annual expenses.
Compare Expenses in Excel
Instead of ignoring those extra expenses, you can use Excel to calculate annual totals, and see what happens if you can cut costs.
I’ve created a workbook where you can compare two scenarios, and see the difference in annual costs.
First, enter your current spending in Scenario A, for the items that you can adjust.
Then, in Scenario B, enter the revised items – maybe you can reduce the cost, or the frequency of some items. How about golfing once a week, instead of twice? And maybe you can negotiate a lower monthly plan for your cell phone.
The worksheet calculates the annual cost for each item, and shows the difference between the scenarios.
Set the Time Units
On a separate worksheet, there is a list of time units, which is used for the data validation drop down on the Scenarios sheet. You can change the number of work weeks, and the days per work week.
Set Limits for Total Units
Some items, like golf or lawn care, are seasonal, so you can set a maximum number of occurrences for those items. For example, you play golf weekly, but only during the summer months. Instead of 52 weeks per year, the expense occurs for 25 weeks.
Calculate the Annual Quantity
To calculate the Annual Quantity, a formula checks the Max Units column, and uses that amount, if entered. Otherwise, it looks up a number from the time units table. Then, that number is multiplied by the quantity.
=IF([@[Max Units]]<>””,[@[Max Units]],
IFERROR(INDEX(TimeAnnual,MATCH([@[Time Unit]],TimeUnits,0)),0))
*[@Qty]
Download the Sample File
To download the sample file (xlsx file format), please visit the Annual Cost Calculator page on my Contextures website.
Watch the Video
To see how the annual cost calculator works, you can watch this short video.
___________________