x

How to Do a Cost Reduction & Avoidance Spreadsheet

by Karen Myers ; Updated September 26, 2017
Use computers to track hard and soft savings.

Creating a tracking spreadsheet to record cost savings and cost avoidance allows your organization to report accurately both “hard” and “soft” savings. Hard savings are shown as a lower cost when compared to a previous year. Soft savings are less tangible and may include negotiating a lower increase in cost from a supplier or "free" training that comes with a rate increase. Developing a spreadsheet to track savings is critical for providing incentives to those responsible for purchases.

Group operating expenses into logical categories. Categories can be by type, such as grouping all utilities together. If multiple employees are responsible for purchasing, group expenses by the responsible person.

Type in the heading "Expenses" in the first cell of the spreadsheet. Beneath, enter the name of a category, then list the expense names in the cells below. Repeat for each category.

In the next column, type in the column heading “Actuals” and the year. Beneath it enter the dollars spent in the cell to the right of each expense name. Label the next column “Adjustments” and enter any contractual increases or decreases in the same expense row to which it applies. Label the next column “Benchmark” and enter a formula to multiply Actual amounts by one plus the adjustment: (Actual * (1 + Adjustment)).

Identify the quantity of items produced last year and the planned production for next year. At the top of the next row, type in “Per Unit.” For all applicable expenses such as material and labor, enter a formula to adjust the benchmark to the new quantities: (Benchmark / Quantity Last Year * Quantity This Year).

Type in “Actuals” and the current year in the top row of the next column and leave it blank until actual expense amounts are known. Title the next column “Difference.” For each expense, enter a formula subtracting this year’s actual expense from the number in the benchmark column.

After each category, insert two blank lines. On the first line, type in “Subtotal.” In the cell next to it, type in a formula to add all the expenses in that category together and copy it across all columns in the spreadsheet. After the last category, type in “Total” in the first column and in the cell next to it, enter a formula to add all category subtotals together. Copy this across all columns in the spreadsheet.

Enter expenses as new contracts are signed or actual invoices are received. Insert additional rows for any new expenses or to log cost avoidance savings.

Tips

  • Enhance total cost ownership by deciding in advance how savings will be rewarded. Key decisions include whether employees will be rewarded on net savings for all their expenses and handling intangible savings that impact another category. The adjustment column can also be used to set goals. Putting in the desired savings percentage for each category will create benchmark goals for each expense. Make sure to include process improvements that result in lower operating expenses or a reduction in labor hours.

References

  • “Journal of Business Logistics”; Purchasing: The Cornerstone of the Total Cost of Ownership Concept; Lisa M. Ellram and Sue Perrott Siferd; 1993

About the Author

Based outside of Philadelphia, Karen Myers has been writing health-, personal finance- and human interest-related articles since 2002. Her articles have appeared in "The Kennett Paper," "Chadds-Ford Post" and "Daily Times." She is a former marketing executive and holds a Masters of Business Administration from the University of Delaware.

Photo Credits

  • Stockbyte/Stockbyte/Getty Images
Cite this Article A tool to create a citation to reference this article Cite this Article