e3BFM’s Premium Client Content includes the downloadable Excel spreadsheet for Monthly Productivity Tracking. Click Here for more information.

Setting up the Spreadsheet

I track revenue in an Excel file. I use one Excel file per quarter with one spreadsheet per month on each tab. For example, Excel file “2016 1Q Productivity” has three tabs: Jan, Feb, Mar. Here is how each month is set up:

The above shows a column for each in-office revenue stream, (Product, Sales Tax, ROA, EOB, etc.) and total at the end of each week. The Subtotal of the in-office revenue streams are added to EFT #1 and EFT #2 for the Total. The purple boxes total the revenue streams and EFTs by row. This should match the column total above each purple box for error checking, (shown in Table 1 the second week with the bold boxes for $16,683 and $19,838).

Projecting Revenue

Once the spreadsheet is setup, it can be used to project revenue based on daily averages, as follows:

This spreadsheet shows the third week of revenue for September, (with the second week’s revenue not shown and no revenue for the first week of September). The blue box shows the total revenue days for the month of 15, (five days week two, five days week three and five days week for). The red box shows the total days for which revenue has been entered, (weeks two and three). The daily average is the green row, (total revenue divided by the red box).

Revenue is projected in the row with the blue box by taking the total revenue days times the daily average (green row). The bolded box on Table 2 shows that with current revenue of $36,490.50, monthly revenue is projected at $54,735.75.

Evaluating Productivity

Adding the previous months’ daily averages allows for tracking the performance of the current month’s daily average.

The yellow row shows the current’s month percentage above or below previous months’ daily averages. This is calculated by taking the current month’s daily average, (in green) and dividing it by the monthly average for previous months, (last row). In this month, EOBs and Ancillary revenue are performing below average, while the rest of the revenue streams are above average.