What is a Proactive Revenue Dashboard?

The term "proactive dashboard" was first introduced to me through a YouTube video by Noah Kagan, founder of AppSumo. In this video, Noah explains how and why he uses it to make sure he accomplishes the goals he has set out for himself at the beginning of the year. A full discussion of what a proactive dashboard is and isn't, doesn't fall within the scope of this article as it would take us too far from the subject matter itself. If you would like to dig in a little more about this, there is some damn good content available on the internet you can look into. For the purposes of this blog post however, there really are only two major things you need to know before we continue:

• The purpose of this dashboard is to track our progress towards a specific goal we have set for ourselves. This can be anything that is quantifiable, but in this case it will be our business revenue.
• The dashboard is called "proactive" because we are not using it to analyze our past performance. Instead, it provides us with a starting point for the upcoming weeks and months, so we can clearly map out the trajectory we need to follow to achieve our goals.

What does it do?

The Proactive Revenue Dashboard (PRD) is a simple tool that will allow us to determine the financial progress of a business towards a specific monetary goal, at any time and in a minimal amount of time. It will allow the user to easily determine whether we are ahead or behind schedule and by how much. We do this by tracking the amount of revenue we have secured during the current and all previous months up to this point. Based on our current revenue and the revenue goal we have set for ourselves, the dashboard will then calculate how many hours of work it will still require in the current as well as the upcoming months to achieve our initial goal. It will also give us an idea of the amount of buffer space we have left in the year, i.e. days that we can schedule as time off (vacations, illness,...).

What does it not do?

While the dashboard does give us some insight into our (professional) financial situation, it is in no way intended to function as a simplified accounting snapshot or overview. As much as I would like to fix all your problems, it is simply impossible for me to do this with a simple spreadsheet. In order to prevent any confusion, it is also important that you understand what this dashboard will not give you:

• Since this is a revenue dashboard, we purposefully do not track expenses in this tool. This is mainly due to the fact that we can't reliably predict incoming invoices and expenses based on previous months, since new invoices might come in at any time without any warning or notice. Additionally, it would also make things a lot more cumbersome and complicated, which would in turn defeat the purpose of creating a simple, intuitive dashboard that provides us maximum insight for minimal effort. I do acknowledge that monitoring expenses is incredibly important, since our revenue should increase in accordance with our expenses. However, I personally worked around this issue by picking a yearly revenue goal that far exceeds my average yearly expenses (e.g. € 100.000 in revenue to cover € 70.000 in expenses). By doing this, I make sure that I always have significantly more money coming in than going out, which subsequently removes the need to track my expenses as closely as I do my revenue.
• It is important to realise that revenue doesn't equal the actual amount of money you have in your bank account. Having a lot of revenue is of course a good thing, but it won't do you any good if your customers aren't actually paying the invoices you've sent them. Therefore, it is very important that you find another way to monitor incoming payments so you never run into any cash flow problems.

Who is it for?

This dashboard was initially designed to fit the needs of freelancers and/or consultants who (primarily) invoice their customers based on an agreed upon hourly or daily rate. This of course includes IT professionals like myself but is also applicable to freelancers in other niches such as marketing, HR, recruitment and others. Although the dashboard does allow for project work and/or miscellaneous revenue streams to be incorporated, the overall added value from the dashboard will be significantly less than for people who work by the hour. That said, even if it doesn't match your business entirely, it might serve as a starting point for you to design your own revenue dashboard that suits your own personal needs and those of your business.

How does it work?

The PRD is, in fact, nothing more than a simple spreadsheet that will automatically perform calculations about your current revenue streams based on the input that you've provided.

Our spreadsheet consists of 2 tabs: one called "Dashboard" and one called "Revenue Calculator." The Dashboard tab is used to give us a brief overview of our status and to store important variables (such as our yearly revenue goal). The Revenue Calculator on the other hand, will be used to register the hours we have performed and make the calculations that will feed into the Dashboard tab.

The Dashboard Tab

The Dashboard tab is the simplest one of the two. There are three major constants in this sheet that need to be configured correctly if we want the sheet to function properly:

• the yearly goal that we have set for ourselves (red)
• the hourly rate that we are likely to use for (most of) the hours that we will perform (blue)
• the amount of hours that we intend to work in one day (green)

The yearly goal is obviously a free and personal choice, but the hourly rate and amount of hours are vital for our dashboard calculations. Because of this, they must be filled in as accurately as possible to get a clear and useful overview of our revenue status.

Aside from these constants, the Dashboard tab also pulls data from the Revenue Calculator, such as the current amount of revenue earned thus far in the year (red), the monthly amount due for the coming months based on our current revenue (cyan) and a calculation of hours and days we still need to perform in order to reach our goal, based on the data we have entered earlier. It also holds the total amount of buffer days we have left in the year (purple), which is a neat metric to keep an eye on as the year progresses.

Sidenote: I originally intended the dashboard to be the main thing I would use to check my progress, but it turns out that I hardly ever use it. This is due to the fact that I tend to focus on monthly goals instead of yearly ones, so the Revenue Calculator tab is a lot more valuable to me. I could theoretically get rid of the Dashboard tab and integrate all of these fields into the Revenue Calculator, but I prefer to keep my constants in a different tab so I don't accidentally change them and screw up all of my calculations.

The Revenue Calculator Tab

As mentioned earlier, the Revenue Calculator tab is the most important tab in this spreadsheet, and it is also where I spend most of my time. As you can see in the image below, it contains a yearly overview of all 12 months of the year, appended with various calculations to the right (to calculate yearly totals and projections) and below (to calculate monthly progress and expectations). I will explain all of these sections in detail so you know what they are, what you can learn from them and how they should be used properly.

The Calendar Overview

The calendar overview is located at the top left of the Revenue Calculator. This is a list of all the months of the year, which will allow us to track each and every month individually. At the top of every column, we see the start and end date of every month. This might seem trivial but due to the limitations of spreadsheets, we need to enter this data manually and make sure it is correct. If not, the calculations made in other sections of the spreadsheet will be incorrect and might give us a misleading picture of our financial situation.

Below the start and end dates, we find a large section called the absence overview. This section will allow us to determine the amount we have left to work in any given month. Remember, the goal of our spreadsheet is to make sure that we earn (more than) the monetary goal we have determined for our business and to determine how many hours and/or days of work it will take for us to achieve this goal. In order for us to do that, we need to be able to take into account when we can and cannot work.

This is easily done by entering every single weekday (weekends do not count) that we will not be able to work due to any number of reasons (e.g. illness, bank holidays, family vacations,...). Once completed, our spreadsheet will count the amount of cells within the absence overview that are not empty and subtract that amount from the total number of work days in that month. A couple of things need to be taken into consideration for this section:

• Due to the limitations of spreadsheets, it's not possible to add timespans, so each and every day must be added individually. So if you take one day off, it would be one line. If you were to take 2 weeks off, it would be a total of 10 lines. Theoretically, this could be improved upon by adding a row where you could add a number instead of counting non-empty cells but I prefer to do it this way since it's easier to check if I haven't forgotten a date.
• While the spreadsheet does list several categories of absences (universal, vacations), the type of absence is absolutely irrelevant for the calculations. I just like to do it that way as theoretically, I can still work on bank holidays even though my employer does not.
• Due to the fact that we count "full" cells instead of measuring actual time, it is not possible to enter half days off in this format. Personally, I have not experienced this to be a problem since it rarely happens in my line of work. If this were to become an issue, it can be dealt with by updating the amount of hours per day in the Dashboard tab to 7.2 (if it happens on a weekly basis) or by simply adding the full day, so the total count shows the worst case scenario.

The billable Overview

Below the calendar overview, we have the billable section of the spreadsheet. Here, we list all the hours we have worked in a particular month. Since I have different rates for different clients, I have added multiple rows of different rates to the spreadsheet. The actual rate is listed in the `B` column (second from the left).

Below the hourly registrations, there are two additional rows for "recurring" and "other revenue streams." These are used to denote revenue streams that can't be measured on an hourly basis, such as revenue from a webshop you might have or if you sell a piece of equipment you no longer need.

As the name suggests, the "Recurring" row denotes any type of revenue that comes in every single month. If you fill out a cell here, the value will be copied to all subsequent months of the year, until you change it. The "Other" row is used for random, one-off payments that can't be added to any of the above columns. For me personally, I use this row to add small, yearly recurring invoices like hosting services or domain names that I offer to my clients, as well as for small websites that I charge on a per-project basis.

Now that we've entered all our revenue streams, we can finally move down to the calculations part. We start by calculating the total amount of hours we worked in one month. This is technically not necessary to track our revenue but I like to do it to get an idea of my consistency throughout the year. It must also be said that this might actually give you a better sense of your performance, since your revenue is greatly dependent upon the rate you charge for an hour of work. If your rates vary a lot, it might seem like you've underperformed significantly during one particular month based upon your total revenue but you've actually worked more hours at a lower rate.

After the total hours, we then see the total monthly revenue. As we might expect, this is calculated by summing up the hours worked, multiplied by their respective rates, plus the total recurring revenue and the additional ("other") revenue. This gives an accurate overview of the amount of money we have earned thus far in any given month, based on our personal performance. Below that, this progress is compared to the monthly goal we have calculated in the Dashboard tab. Based on our progress, the cell is colored green if we've reached our monthly target or red if we haven't.

It is worth noting that the monthly progress calculations are updated and recalculated every time a change is made to the spreadsheet. This is due to the fact that the monthly target is calculated for the upcoming months based on our current overall progress. Because of that, it is possible that a particular month can shift from red to green if you add a large number of hours or a large one-time invoice to the spreadsheet. By contrast, a cell can shift from green to red if you add a lot of absences or work a lot of hours at a lower rate. Naturally, making changes in the overall yearly goal in the Dashboard tab will also change both the monthly goal and the monthly progress indicators.

The scheduling Overview

Now that we've done all of the configuration and have documented the work that we've done so far in the year, we have arrived at the single most valuable section of this spreadsheet: the scheduling overview. In this section, all of the information we have entered above and in the Dashboard tab, is used to calculate how many days in the month (and also the year) we have left to accomplish our yearly revenue goal.

The calculation itself is fairly straightforward. To accomplish this, the Revenue Calculator will start by taking the monthly total revenue that was calculated for us in the Billable Overview and compare it to the monthly goal that was automatically calculated in the Dashboard tab. Assuming we are below the monthly goal (which will be the case for most days of the month), we will then calculate how many hours we still need to do based on the most likely hourly rate from our Dashboard tab. This value is then divided by the amount of hours per day we intend to work which then gives us the amount of typical work days we still have to perform in order to achieve our monthly (and yearly) goal.

Additionally, the Revenue Calculator also uses the start and end dates of the months, as well as our scheduled absences, to calculate the amount of days we have available for work during any given month. This number will then be compared to the amount of days required to see if we still have enough time to accomplish our goal. If we do, the "Days left" cell will be highlighted in green. If we don't, it will be highlighted in red. Below, we can see exactly how many days we still have as a buffer, or how many days we are short.

Due to the limitations of spreadsheets, the time of day is not taken into consideration when making these calculations. In essence, this means the spreadsheet will always count today's 8 billable hours (or however many you have selected) as "still left to do." At 08:00, this makes perfect sense since you still have a full day of work ahead of you, but at 22:00, this is technically not correct. Sadly, I can’t find a way to fix this but I've also never experienced this as a truly blocking issue. However, I do think it is something that you should be aware of and it should be taken into account when drawing your conclusions.

The yearly Totals Overview

The last section of our spreadsheet is the yearly totals overview, which contains a list of yearly totals based on the values we've entered in the billable overview. I won't cover this section in great detail since it doesn't provide much value in my personal opinion and the numbers are fairly self-explanatory (total number of hours, total revenue based on hourly rate and yearly projection based on the revenue thus far and the amount of months left in the year). The only notable exception to this, is the Total Revenue Projection (yellow) based on our current revenue thus far. This projection (as well as all the others) is made by multiplying the average monthly income by 12, not including the current month since it will most likely draw a distorted picture (unless you are in the last couple of days of the month).

How can I use it for myself?

Although I tried to keep this spreadsheet as universal as possible, it is important to remember that I made this product specifically to facilitate my personal way of working. Consequently, you will most likely need to make certain modifications to the spreadsheet in order to be able to use it in a way that best suits you. In this section, I will cover the areas that you will probably need to update and help you through the process.

Changes to the Dashboard Tab

• First things first: we have to pick a yearly revenue goal. If you have a couple of years of experience, you'll have a rough idea of what that should look like. If not, I recommend discussing this with a couple of colleagues and do some research in order to find out what you should aim for. It makes no sense to shoot for something that can never be attained and it is equally useless to set a goal that doesn't provide you with a challenge or that doesn’t allow you to cover all of your business expenses. If you're completely at a loss, you can use this easy formula to get started: `(140 hours per month) x (your preferred monthly rate) x (12 months in a year)`.
• We'll also have to add your most likely monthly rate. If you only have one, this is very easy. If you have multiple rates like I do, it makes more sense to use the one that you will be using most often throughout the year. If there isn't a clear winner, I'd add the "mean" rate (not the highest, not the lowest) to make sure you don't overestimate your revenue calculations. Alternatively, you could also enter your lowest rate if you'd like to play it safe.
• We need to know how many hours you will be working per day (on average) so we can calculate how many working days we need to perform in order to reach our goals. In most cases, this will be 8 (a typical day of work in most countries), but it is worth noting that not everybody works 8 hours each day. If this is the case, this should be taken into consideration because otherwise, all of our calculations will be off and the spreadsheet will lose its value. In my experience, there is an easy way of filling out this field: take the (average) amount of billable hours in a typical work week, then divide them by 5. Unscheduled deviations, one-time absences or anomalies, should not be taken into account here. We'll cover those in the calendar overview of the Revenue Calculator tab.
• Depending on where you are in the world, you might have to (or want to) change the currency which is used in the spreadsheet. Technically speaking, this has absolutely no effect on the efficacy or validity of the calculations, but I do understand that it often makes more sense to see the values represented in a familiar way.

Changes to the Revenue Calculator Tab

• Depending on the year in which you will be reading this post, you will have to update the start and end dates of every month of the year. This must be done correctly, since these dates will be used in our calculations. Be sure to check if it's a leap year!
• You will also need to add any and all absences that you intend to take. You don't necessarily need to fill these out for the entire year, but over the years I found that it helps me to fill them out as much as possible, as soon as possible. That way, you'll be able to avoid unpleasant surprises. If you are starting to use this spreadsheet in the middle of the year, you can ignore holidays that have already passed, since those are no longer taken into account in the calculations.
• Since we are calculating revenue, you will naturally have to update the different hourly rates that you charge your clients. If you have only one, that's fine. If you have multiple, be sure to add them all, so we can calculate your total monthly revenue as accurately as possible.
• If the year has already started, you should also enter your hours worked for all the previous months of the year.
• If you have any recurring or one-time revenue, you'll have to add those as well. Use the respective rows and the appropriate months (columns) to do so.
• As with the Dashboard tab, you might also want to make some changes to the currency used in the calculations, if this is something that is important to you.

And that's it! You're all done and ready to start using your very own revenue dashboard. From now on, you should make it a habit to update your billable hours on a regular basis so you always have a clear view of your progress. I do it once per week during most of the month, but I switch to once per day in the last couple of days of the month. By doing it this way, I found that it significantly increases the odds that I accomplish my monthly goals. This is entirely up to you though, as it all depends on your personal preferences and your management style.