Transform Your Profit Forecasting Overnight as a Solopreneur with this Free Spreadsheet

Unlocking Solopreneur Profit Predictions with Excel

Are you a solopreneur struggling to predict your business profits each month?

What if I told you that there is a method that can not only simplify this process but also give you crystal-clear visibility into your financial future?

Stay tuned, as in today’s episode, I’ll be sharing a practical Excel-based system that I’ve developed to forecast the profitability in my own business: EasyTECH.

By the end of this episode, you’ll have a step-by-step framework that you can implement on your Mac to gain valuable insights and make informed decisions about your solopreneur journey.

I’ll unpack all of this after the intro.

Welcome to the Macpreneur Podcast

Hello, hello, and welcome to episode 95 of the Macpreneur podcast. Whether it’s your first time or you’re a long-time listener, I appreciate that you carve out some time in your busy solopreneur schedule. I’ve created Macpreneur to help as many solopreneurs as possible save time and money running their businesses on their Macs.

Now, in order to give you the most relevant Mac productivity tips and information, I need to know how well you’re currently dealing with the three killers of Mac productivity, namely, unnecessary clicks, repetitive typing, and file clutter. 

For that, just visit macpreneur.com/tips and answer a few questions, which will take you less than two minutes. After submitting your answers, you’ll receive personalized time-saving tips based on your results. 

Once again, visit macpreneur.com/tips and start boosting your efficiency today.

The Challenge of Financial Forecasting for Solopreneurs

As solopreneurs, one of our biggest challenges is managing the financial health of our business without a full finance team behind us.

If, like me, your solopreneur business offers coaching or consulting services, being able to forecast its profitability can be a huge challenge, especially in the absence of either a retainer strategy or subscription-based offers and products.

The key to overcoming this challenge lies in creating a system that forecasts both revenue and expenses.

Introducing the Excel-Based Forecasting System

And today I will share with you a robust Excel-based forecasting method that I’ve developed for EasyTECH, which you will be able to adapt to the specific needs of your solopreneur business.

You can get a copy of this Excel template by visiting macpreneur.com/freebie95.

If you don’t have Excel, but you have a Google account, you can upload the file to Google Drive and edit it with Google Sheets without any problem. 

The Numbers app from Apple, on the other hand, isn’t able to cope with some of the Excel features that I’m using.

Diving Deep into Revenue Forecasting

Okay, so let’s start with revenue forecasting, and for that, I have two dedicated sheets. One sheet is for tracking and predicting revenue on a weekly basis, and another sheet provides a monthly overview of both revenue and expenses.

I decided to be able to see things on a week-by-week basis because it helps me easily see how booked I am in any given week and determine whether I can accommodate new requests or not.

Before doing that, I only looked at my calendar, failing to realize that for most training sessions, there is a bunch of pre-work that is required the week before, and which doesn’t appear on the calendar. And there is always some post-session work that usually takes place in the same week as the work is done.

On top of that, some weeks there are legal holidays, plus school holidays, conferences, and so having a weekly view and estimate of the available days is super helpful for me.

Now when it comes to revenue forecasting, imagine a table with 53 columns, one for each week, with the week number and the date for the first day of the week on top.

Then there are a bunch of rows, one per revenue stream, and in the case of EasyTECH, we have a row for consulting, another one for training, and then a bunch of individual rows for different clients. The reason for separating some clients is twofold. First, the hourly rate is usually different, either based on a contract or imposed by the Luxembourgish government.

The second reason is that it helps me understand which revenue stream or client is the most booked, and also I can have a rough approximation of the contribution to the total revenue for each of them.

So, based on the hourly rate, I can calculate an approximate half-day revenue per revenue stream or per client, and that is on the left side of the table.

Then, I enter the number of half-days that are booked on a weekly basis because training sessions are often a multiple of half-days.

In the case of BNI, I earn commissions when a new member joins the chapter that I manage, or when an existing member renews. The amount is slightly different between those two cases; however, for simplicity’s sake, I’m using a single value that is roughly the average of both.

And since those commissions are only invoiced on a monthly basis, I enter the expected number of renewals and potential new members for the first week of each month. As a side note, you can reuse the same strategy if your business deals with retainers or if you are hosting a mastermind or monetizing an online community.

Occasionally, I also provide one-hour-long training sessions for BNI members in Luxembourg, and I count those as separate units rather than a fraction of a half-day.

Expanding on Revenue Streams and Forecasting Techniques

And so, to get the forecasted revenue for a given week, I use a function in Excel called SUMPRODUCT, which takes two columns, one containing the half day or per unit revenue, and the other containing the number of half days or units that are booked and forecasted in a given week.

This function multiplies the values that are on the same row and then sums everything up. To have an approximate monthly revenue forecast, I sum either 4 or 5 consecutive weeks and show the value above the weekly forecast. Additionally, I sum 3 months at a time to have a quarterly forecast.

The monthly revenue forecast is then reused in another sheet where I’m able to compare it to four other things.

First, the goals for each month, which are based on quarterly goals that are set in another part of that sheet.

Second, the breakeven amount for each month, based on the forecasted expenses, which I will explain later.

Third, historical figures from the past three years, which allow me to spot any seasonality effect.

And fourth, the actual invoiced revenue. This last one is important because over the course of the year, I combine that value with the forecast to have a continuously evolving projection for the whole year.

And the way I do that is by having a rule that contains, for each month, a number between zero and one. That number is the fraction of the month that is already passed. So, for instance, let’s say that we are on February 14th. For January, the number will be 1 because it’s in the past. For February, the number will be 0.5 because it’s half the month already past, and between March and December, the number will be zero because it’s in the future.

To calculate the projection for the entire year, I multiply that number by the invoiced revenue, and then I multiply the complementary number, one minus that number, by the forecasted revenue.

That way, on February 14th, the yearly projection will take into account the actual revenue for January, plus half the actual and half the forecast for February, in addition to the sum of the forecast for March through December.

When it comes to tracking the actual revenue, I only look at three revenue streams: local consulting, local training, and the online business.

Each of those is split between three and seven subsegments of interest. So, for local consulting, I record separate values for B2B, B2C, and BNI commissions, for instance. Local training has three subcategories: office training, BNI training, and all the others. And when it comes to the online part of my business, I split revenue between coaching, digital products, affiliate marketing, online courses, live events, memberships, and sponsorships.

For local consulting and training, every time I issue an invoice, I update the Excel sheet. And for the online business, my plan is to update the actual revenue once per month.

Comparing Revenue with Forecasted Expenses

Now, in order to forecast the profitability, we need to compare the revenue with the forecasted expenses. The previous episode, episode 94, covered various strategies to efficiently track our solopreneur business expenses. Some of the tools that I mentioned allow us to easily forecast future expenses, while with other tools, we need to wait at least a full year before starting to do so. You can go deeper on this topic by checking out episode 94 by visiting macpreneur.com/episode94.

And this is done in the monthly tracking sheet underneath the invoiced revenue. There, I have split the expenses into four main categories: fixed monthly, fixed yearly, variable, and my salary.

So, when subtracting the forecasted expenses from the revenue, I can calculate an approximate profitability value month by month. And by revenue, I mean the actual invoiced amount for a month in the past, the forecast for a month in the future, and then a combination of both for the current month using the fraction of the month already passed that I explained a bit earlier.

The fixed monthly, fixed yearly, and variable expense categories each have a separate table underneath where I’ve decided to monitor them a little bit more finely.

The table with the fixed monthly expenses has five main subcategories: accountant, bank and insurance, online services, phone, and taxes. And for the fixed yearly and variable tables, there are many more subcategories that are directly linked to how I run EasyTECH and also the tools that I use on a regular basis.

I have done that in order to have a sense of the biggest expense contributors, but also to spot those that may negatively impact the cash flow for any given month.

Implementing the Forecasting System: A Step-by-Step Guide

Before concluding this episode, there’s one more thing I’d like to mention. This system is far from being an exact science.

However, having something in place, even if it’s slightly inaccurate, is always better than nothing. And remember that you can get a copy of this Excel template by visiting macpreneur.com/freebie95.

Regardless of when you get this template, start by checking that the cell A1 in the sheet named ‘Weekly’ contains the first of January of the current year. The next step consists of updating the names for the revenue streams in column D and how much revenue each can generate in column C.

Step number three: start populating the row for each stream or client with the number of sessions, half days, or hours that are booked, or that you expect to book.

At this stage, you will have the forecasted revenue on a weekly, monthly, quarterly, and yearly basis.

Step number four: open the monthly sheet, update the name of your main revenue segments and subsegments, then, for the past month, enter the invoiced amounts.

Step number five: scroll down and update the expense categories, and start populating the forecasted expenses.

At this stage, you will already have the approximate profitability of your business.

And to compare the forecast with your goals, update the quarterly goals for each main revenue segment in the top left corner of that sheet, and to have a feel for any seasonality in your revenue, update the monthly revenue for the past three years.

Recap and How to Access the Excel Template

So, to recap, by implementing this system or a similar one, you will gain a clear understanding of the financial situation of your solopreneur business, allowing you to make smarter business decisions and adjust your strategies accordingly.

If you’ve enjoyed this episode, please share it with a fellow solopreneur and DM me on Instagram. My handle is @MacpreneurFM.

So that’s it for today.

Preview of the Next Episode and Closing Remarks

The format of the next episode will be slightly different because I’ve asked a bunch of solopreneur friends of mine to share their favorite tool when it comes to the finance side of their businesses.

So make sure to subscribe or follow this podcast to get it automatically next week.

And until next time, I’m Damien Schreurs, wishing you a great day.

Thank you for listening to the Macpreneur Podcast. If you’ve enjoyed the show, please leave a review and share it with a friend right now.