Yesterday we set up a spreadsheet framework to serve as our basic financial model. I know it was quite a chore to add those column names… But seriously today we will start to delve into the meat of the model. The first cost we will be adding is one that everyone has, staffing. Staffing is an interesting cost as there are other costs that are buried in it. For instance, staffing usually includes benefits, social security tax, and any other employer covered expenses.
At it’s most basic we have a salary that is paid to employee, usually 24 or 26 times per year. If you don’t know the exact amount of the additional expense 25% over the salary is a safe number to use. So for an employee with a salary of $100,000 per year the total expense would be somewhere around $125,000 per year. HR or accounting should be able to tell you the exact number to use. If these employees do not work directly for you, of course, you may not be able to get the exact number.
so our $125,000 per year cost is divided by the number of pay periods in the year. On our spreadsheet we enter it this way:
Notice the formula in the formula bar. A Simple division formula, but it saves you some time if you have lot’s of staff to account for.
The next step is to add staff entries to cover everyone who works in IT. In a more advanced model we would be able to allocate their time to different projects and get a cost per project for staffing expenses, but for now we are going to stick to the basics. The example below shows the staff entered for two system managers and two operators. You may also have supervisors, project managers, managers, architects, analysts, etc, etc, etc… There is no limit to what these cost names can be, just make sure they represent the job that is being performed and that you understand what you meant if you look at this 6 months down the road.
The granular nature of the level we are going to track things at is what will make this a truly powerful financial model when it is done. Remember this can also serve as the input into a more advanced financial management system.
So now we have our staffing entered. It would be nice to get a total of the current staff expenses. Lets add a summary line on the next sheet to roll these up. To get to the next sheet, select it’s tab on the bottom.
At this point it might also make sense to rename the sheets to something better than Sheet 1 and Sheet 2, I am going to rename mine to be Details (for Sheet 1) and Summary (for sheet 2). Do this by right clicking on the name of tab at the bottom.
Now that we have our sheet names, let’s set up our Summary sheet. This new sheet will pull it’s source data from our detail sheet. I’ll get to that in a minute. We need to set up the framework on our Summary sheet.
Notice the indentation of the Staffing expense… We do this so we can at a glance see costs that are categorized as recurring. We will list all of them in this section and ultimately create new sections for Expenses and Capital (Depreciated) Assets.
We want to make sure the formulas on the summary will reference data on the Detail sheet. This will ensure the detail sheet is our master source for information. For the summary staffing cell, we will have it add all the annual salaries on the detail sheet. The easy way is to start a formula in the appropriate cell by typing =SUM( then select the tab for the detail sheet and drag to highlight the cells you want to reference:
Once you have them all selected, hit enter and you will be returned to the Summary sheet with your total calculated in the cell. The next step is to add the calculation for the monthly charges. This is not absolutely necessary to have, but I like it as most budgets and variances are managed on a monthly basis. This formula will use the data, summarized from detail sheet, that we just created in the last step.
Of course the total number is divided by 12 as that is the number of months in a year.
Well, that’s it for today. We now have our first entry in our financial model. Tomorrow we will focus on adding our next recurring cost.
