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.

costperyear02

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.

costperyear03

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.

costperyear01

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.

costperyear04

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]

costperyear05

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.

_

___________________

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>