Build Your Formulas

Continuing on with “everybody loves a good spreadsheet”, today I am writing about the formulas that make up the cost calculation. If you haven’t been following this series (shame on you) over the past two-weeks I have been writing about finding your cost and the time that is freed up by automation, which is the true definition of automation.


Above is a part of the spreadsheet that I use to calculate cost. The entry is pretty simple and the formulas are not that difficult either. The logic seems to be the most difficult things for people. If you follow the top line. It asks how many people open payable. In this example there are “2”. The next line is the section  people get wrong most times… wrong in the respect that it will considerably change the formula and the outcome if you don’t put in the right number. The number I am looking for is the average salary of the people doing this task. In this case it is $40k. This means that one person could be making $35k and another making $45k so the average is $40k. Where people go wrong; they will put $80k which will mess the entire number up. Lastly, on the third line I am asking for a percentage of how much time (per week) they spend on the task. I ask per week because monthly seems to be too long and daily won’t accommodate for peaks and valleys in their duties.

Once the information have been entered, the formulas take the number of people multiplied by the average salary multiplied by the percentage of time on the task which gives the overall cost of that task. Then I divide that by the number of invoices which gives me my invoice cost for that one task. It looks like this:

Opening Invoice Cost = (Number of employees X Salary X percentage of Time) / Number of Monthly Invoices

Lastly, I take the percentage of time and multiple that by 2080 which is a full-time employee. The outcome to that calculation is the number of hours per year that is spent on that task.

If you want my spreadsheet… I will sent it to you (fill out the contact form below) on a small condition… if you find another calculation or improve the spreadsheet… please share it with me… that’s all I ask!

Buy the book – The Argument to Automate – How Innovation Can INSPIRE Not Fire – click here to buy

(Also) To get your copy of The 8 Pitfalls of Accounts Payable Automation – click here to buy

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s