Every entrepreneur needs to make estimates for their revenue.
Back-of-the-envelope is easiest, but quickly becomes a mess and lacks the smarts you’d want.
And, building your own Excel model is not everyone’s hobby.
Therefore, I made one for you. A Portfolio Calculator.
What for
Gaining an overview of your business when doing a review.
With this calculator you can quickly fill in the expected number of sales for your different products to see whether or not you drive enough sales.
Even when all products have different ways you make estimates.
The Calculator can handle it when you want to fill in one product in hours per work-week and another in units per calendar month.
Seeing whether or not your new plan makes sense.
It could be an interesting product or service to develop and market, but you’ll know more clearly when you do a quick calculation on how much revenue you can realistically expect from a product.
Perhaps it’s a no brainer. Or, the number of sales is implausible, or the margins on your time are simply too low. You better know that before you start.
Spotting opportunities for improvement, by playing with the number of sales or your price points.
Which products drive your revenue? How do different products in your portfolio stack up next to each other?
It gets your numbers in order. Then it’s up to you to make a wise decision.
Who for
As with any tool, it has its best use-cases. There are too many types of business to make a one-size fits all approach.
That said, it is rather flexible.
It’s a perfect fit for a one-person shop with products or a freelancer offering (productised) services. But you can even add employees to it.
The tool adapts to it.
You can fill in what you sell (time or units) for a number of different time scales.
Fill in specific projects, or take an average amount of what you sell over the year.
How to USE THE CALCULATOR
Follow the link in the button.
From there Download a copy (File → Download → Microsoft Excel (.xlsx)).
When opening, depending on your Excel settings, you possibly need to enable edits (you’ll see a notification bar if that’s the case).
A few notes
Using the calculator is rather straight-forward. A few things to note in advance:
1 VAT
All numbers are excluding VAT. If you sell to consumers make sure you adjust the numbers.
2 work-time vs calendar-time
As you know, there are 52 weeks in a year, but you can’t and don’t want to have to work each one. Most people work about 45 weeks a year (after you deduct holidays and sick days). But, I often budget with 40 to be on the safe side.
That makes roughly 10 work-months out of 12 calendar-months. Although, to get to the actual work-month the calculator works with, it divides the number of work-weeks by 4.
The calculator takes into account that you might sell every calendar-month, but you’ll need to do the work in your work-weeks. You see that reflected in the margins and percentages in the time you spend.
3 protected sheet
I’ve - as Excel calls it - protected the sheet. Simply to make sure you don’t accidentally delete a formula. But of course, unlock it to edit the sheet and expand on it. The password is portfolio.
4 averages
The tool allows you to fill in the averages for the whole year. It doesn’t let you plan per quarter for example or have a rising amount of sales per month.
It does allow you to change those number really simply to map out different scenario’s. It's good at helping you see what mix of products to strive for, not calculate a path.
Setup
First, fill in your setup information.
Fill in your goal net revenue.
Net revenue is the revenue you have left of your sales after you pay your variable costs. Those are the costs that scale with how many of your product you sell.
So the goal net revenue is how much you want to take home per year to cover overhead (or fixed costs) and your gross income before taxes.
Don’t know how much this is? Fill in my Hourly Rate Calculator. That starts with a net income per month, and by using the tax formulas and what you have in overhead costs, generates your net revenue goal.
Fill in how much your work.
Fill in how many hours you work in a week that you work (so not an average of work-hours over all the weeks in a year).
And fill in how many weeks in a year you work.
Note, these last two numbers are only relevant if you’re also interested to see where your time goes and fill in the time you spend on the projects/products.
Filling your portfolio
Revenue
Now we’re ready to really start adding the different products to your portfolio.
For each product your add:
How many (Q) you sell of it.
What exactly you sell (hours, weeks, months, units)
Over what timeframe (per week, month, year, project) you expect that number (Q) to be sold?
The price (or gross revenue per hour, day, week or unit)
The variable costs: Actual costs you make for every Q (hour, day, week or unit) you sell. This is can be material costs, acquisition costs, human time (you pay for) to produce or sell it.
Time spend
Then, optionally, you can also add the time you spend on each product. This helps you see whether or not it even fits in your year, and which products give you the best return on your time-investment.
Fill in how much time you spend in that same timeframe that you plan to sell that amount of products. The timeframe is naturally the same as the one for the amounts when you’re selling time. When selling units it switches to “per unit”.
Some examples
(with made up numbers for the sake of illustration):
Billable hours. You sell 10 work-hours per work-week, of €100 per hour with €0 variable costs. The time you spend on it per work-week is that same 10 work-hours.
A specific project. 40 work-hours for the project, and since this specific project is unique it happens once per year. (NB: It could very well be that you spend 5 work-hours per work-week for 8 weeks on it. But since it doesn’t last the whole year, fill in 40 work-hours for the project.) Each hour costs €100 per hour, no additional variable costs. But you need to spend a little more hours on it, 50 work-hours per year.
A productised service. 4 branding tracks (thus units) per work-month. Selling for €1990 and costing your €500 for each in hiring freelancers. You spend 5 work-hours per track.
Physical Products. 200 water bottles per calendar-month, priced at €29 each with variabel costs of €14, to produce these you spend 0,5 work-hours per water bottle.
Adding more products
You can (after you unprotect the sheet), add extra rows for extra products or services.
Simply insert a new row underneath the fifth and copy paste an existing product-row over it.
