Ecommerce Budgeting | Free Template

If you would like, go ahead and subscribe to our YouTube channel so you can get future videos from LedgerGurus and be notified when we release them.

0:40

Let's start by looking at the structure of this ecommerce simple budget template. Now the structure only has seven default worksheets. This could easily evolve into a more complex budget, and I'll show you how as we walk through this.

But I'm going to start in the center of this budget where we're going to look at the P&L. In essence, all of the budgeting information rolls up into a profit and loss statement, which allows you to easily compare to actuals. What you'll see to the left here is, after you've created the budget you could go through the year, record your actuals, and see what that comparison is. And then you also have a variance. We'll talk a little bit about the variance as well. But let's start by building the budget, then we'll come to some of these actuals.

1:30

So everything to the right of the P&L worksheet is going to roll up into it, starting with your income worksheet, where you'll capture your sales, your cost of goods sold, and then your expenses. And then I have actually broken out one of these expense items, the payroll expenses, into its own worksheet.

Now as a structure, I'll show you a format I like to use. I like to have the different worksheets reflect what is going to be in your accounting software. And if you find that the simplicity of the information requires further build out, I've got two ways of going about that. The first way is, as you'll see here in the income, to simply have line items roll up from other tables below. And if the complexity requires more than you can have on the same worksheet, I recommend breaking out line items or small subtables into their own individual worksheets. And you'll see how that works as we walk through that.

2:42 Building the income worksheet and forecasting revenue

So let's start with the income worksheet and work on building that out.

Now a lot of sellers we work with sell on multiple channels, so let's just model in a couple of channels. Let's say you're a seller and you have three channels. So you have Shopify, you have Amazon, and maybe you have a little wholesale going on as well. So I'm going to go ahead and build out the names of each one of those subtables.

Now some of our customers will track the discounts at a summarized level. So you could create those discounts at the channel level, you could also roll those up into a summary level. We've seen some customers that prefer to see all the discounts rolled up at the top, some would prefer to see those by channel. The reason I have them broken down by channel is it allows, you can basically run the reports on your channels and put that information in accordingly.

So I'm going to go ahead and put in some fake information real quick on these channels, and then we'll see what happens.

4:01

Okay, let's talk about what I've done here with Shopify, and I'm going to show you a simple way to forecast revenue.

Now I could have a whole video on revenue forecasting because it can get really complex really quick. But if you're new to the budgeting game, one of the easiest ways to do it is to simply take last year's previous revenues. Oftentimes I'm recommending that you do this near the end of your calendar year or your fiscal year, so the last couple months you might have to estimate, or you might have to update your budget at the very end of the year to reflect actuals. And then simply project a growth uplift across all time periods.

This isn't a perfect methodology. You may have acceleration of growth, you may have different seasonality, you may want to get more granular. But if you just want to do something real simple, just do a wholesale uplift.

5:04

Now what I do is I take those numbers and I just put a formula in of that number, previous year's number, times one plus the growth projection.

If you wanted to get more granular, and you will as you go, I would probably break this table out into a separate worksheet so you could model seasonal growth, accelerated growth. So maybe you're saying, hey, I expect thirty percent growth in January and accelerating one percent every month. Maybe you want to break things down into units and get really granular. Maybe you want to tie this to your marketing budget.

But if you're just getting started with a simple budget, this is a real easy methodology for uplifting your sales projections. If you're just getting started and you want a simple methodology, using a wholesale percentage growth projection is an easy way, and not totally inaccurate way, of projecting future sales.

6:09

The other thing I would say is, as you're looking at this number, I would look at your year over year trend of the past year and get a sense of, where are you trending, and put a number that you're comfortable with. If you're not comfortable with the market you're going into, then go a little bit low. If you're really bullish about your prospects, go higher on that number. And again, as your first budget, this isn't the worst way to start it. It could get you reasonably close to where you need to be.

The biggest thing you need to know about sales projections is your profitability is really going to depend on a level of accuracy. And so I would say you want to be overall comfortable with the annual projection number. So if you said, hey, we did 655,000 via Shopify last year, we think we're going to do 30% more, and you feel really confident in that number because of the trending of your sales leading into that year, then go for it. Because everything is going to derive, all of your profitability is going to derive from accurate sales. If your sales are way off, then it will ruin your model.

7:24 Modeling Amazon's line items

All right, I'm going to go ahead and do the same thing for Amazon. Now here we've got some Amazon data.

Now the hardest thing about modeling Amazon sales is all the stuff that goes into an Amazon transaction. A lot of people just look at Amazon sales and they see the bottom line down here, not realizing that there's a whole lot of other information.

Now you could model just your net sales if you wanted to, and pull that through, because it's going to be a lot easier to get to that information than it is all this other information. But you may want to look at different levers that go into Amazon. For example, you may want to put more into FBA and therefore need to model those fees differently. Your shipping charges, you might want to change it up.

It really is going to depend. If you're going to get very granular in terms of modeling all of the fees and incomes that go into Amazon, you're probably going to want to break out this table into a separate worksheet. Now I show you all these details instead of just the net proceeds, which a lot of sellers just think about, well this is what I earned from Amazon, because I want you to think about how you're going to approach modeling that.

8:52

But again, I'm going to use a similar pattern. I'm going to probably just do a percentage uplift across the board to come up with my income projection. But like I said, you may want to look at all of these different levers and think about, hey, maybe I want to reduce my shipping charges, maybe I want to do more FBA. Think about all the different levers that you could pull that could either bring in fees or income into that channel, and model appropriately.

So to finish, I have some wholesale revenue modeled out here. I take the previous year's numbers and I'm doing an uplift. And now I have an income forecast or budget for the year.

Now what I've done here is very simple. And what I would say is you want to spend a good amount of time on your revenue model. Even if you're using a simple uplift across the board, you're going to want to make sure you understand how you're getting to those numbers and that you have a confidence in that. What is it that's causing 25 percent across the board? Does it tie back to marketing, linking marketing spend to your sales? So you could spend a lot of time, get very complex with your income model, and it's worth taking the time to really dial it in, because your profitability is going to depend on this.

10:26

Your expenses are easier to predict and to measure. Costs can also be easier to measure and predict. But income, it's very unpredictable. And so the more time you spend, the more realistic you're going to have that model. You're never going to get accurate, like what you forecast will never be a reality, but you can get pretty close, reasonably close, and have a good model for forecasting.

The other thing I would say is I've broken down three sales channels. I would break down all of your sales channels, at least all of the ones of substance, and consider the granularity that I have here, so that you can really think about not just the net proceeds from that sales channel, but what is happening there. Maybe in the process of budgeting you'll realize, man, we have way too many returns, what can we do to reduce returns. Part of the budgeting process should not only be helping you understand where to spend and what to [unclear], but how to improve your business.

11:33 COGS, fulfillment, and merchant fees

Let's move on to COGS now. So cost of goods sold, again we could get very detailed very quickly here, but I'm going to start very simple and you can decide how you can go from there.

With the cost of goods sold I have three different areas. I have product cost, fulfillment, merchant fees. There could be other fees in here as well. The product cost is the one that's probably going to be the bulk of your COGS, and so you're going to spend the most time here.

If you have very consistent product COGS as a percentage of the sales price, you could just put in this product cost and apply that to the income. Now the reality is, the way that I have this set up in the template, it is modeling that as a percentage of the net income here. What you'd probably want to do is model that as a percentage of the sales line items. So instead of looking at the summary line items which include factors of fees and discounts and refunds, you would probably want to look at your sales line items, 12, 21, 35.

12:50

This is a very simple model. You'll want to go back and look at your COGS history and say, is your COGS consistently a percentage of sales, and if it is, go ahead and use that model.

Now if you want to do something that's more calculated, this is probably going to be another table if not another worksheet. If you find you have different cost of goods for different line items, you're probably going to model out your product inventory and what that cost is going to, to that degree. You're probably going to want to model what your income is going to look like.

So I'm giving you something very simple as a way to get started, and it's not a bad idea, especially if you have a small number of SKUs. If you have pretty consistent costs as a percentage of your sale, you can just slap in that number and get to that very quickly. But if you look at your history and you see it doesn't add up, you're going to want to get very granular. That modeling is a video unto itself, it's probably a conversation to itself, it's probably unique business to business. But that's how I would approach it getting started.

14:07

The next item I want to look at is fulfillment costs. Now this opens up a bit of a can of worms.

Technically if we look at what we have modeled here with Amazon, we have COGS in here. A lot of people don't break this information out in their accounting, they just treat the net as their sales income. But if you're going to do it right, and the way we like to do it using A2X, and if you're not using A2X with Amazon you need to, we would break out a lot of these fees into COGS. And some of those shipping charges would obviously for Amazon go in there.

But what I'm looking at here is like in the example of Shopify, where you are fulfilling directly, you're receiving directly inventory before you're sending it out, and modeling that. So what you would want to do is look at, again I've got previous years, you could determine, I've baselined this off of the Shopify growth numbers for the future year, and have the same uplift.

15:24

Again, you want to look at things. Do you expect shipping fulfillment costs to go down, go up, stay the same as a percentage, and model accordingly.

The other thing you want to look at is if you're using a 3PL. There's a lot of fees that go into a 3PL, and you want to consider, based on what you're doing with that 3PL, is that fee structure going to increase, decrease. Again you could turn this into a separate worksheet in a hurry depending on the sophistication of the costs that are going into play. Or if you find that there's a fairly consistent ratio, a percentage of fulfillment costs per sales, then it's easy to align that cost structure to your sales structure as I've done here.

16:23

All right, the final area that I have here is merchant fees. Now you could argue that a merchant fee is an expense, you could argue that it's a cost of goods. We're going to put forward that it's a cost of goods, that hey, you have to use merchant accounts to be able to sell, and the fees that go along with that are going to be considered part of a cost of goods sold.

With merchant fees, usually there's going to be a percentage of sales. The sophistication here is saying, is it all your sales, is it a subset of your sales, do you have more than one merchant, are you using PayPal and Stripe and what are those fee structures. Most likely you can dial this into a percentage of sales. So I've got this at three percent, I've just hard-coded that in, and then three percent of Shopify sales, because Shopify is going to be direct so you're probably going to be using a merchant account, and therefore that's where those fees will come in.

So again, you could spend a lot of time and really dial this in, or you try and simplify. This isn't going to be a huge area of cost, so I wouldn't spend too much time. You want to get your model pretty close to what you've seen in previous years and then model accordingly.

17:18 Checking your gross margin

Now let's go over to our P&L and just take a look at where we are so far.

What you're going to find is that we have our income summarized here, our COGS summarized here, and now I have a gross profit margin number, 50%. So income ratio is something very important to think about as you're going through modeling your budget. It kind of tells you, do I have a good model. And usually at least 40% gross margin is where you want to be for a product based business. So 50% is decent, right. It's a good model, and you can see, hey, you know, I'm looking good.

18:06 Expenses and payroll

So this takes us to our last area of the budget, which is the expenses.

Now expenses can be both the easiest to predict but also take the most time. Easiest to predict because you really control almost everything here. There's not anything that you can't control. Sales is hard to control, COGS is more controllable, but you really have a lot of control over your expenses. But the reason it takes some time is because of all of the items that go into expenses.

18:47

So as you're building up an expense model, there's a couple things that I would talk about. The first is, where are most of your expenses going to be. For an ecommerce business it's probably going to be in your payroll and your advertising.

I have in this template already broken out the payroll expenses into a separate tab, and the reason I've done that is it gets really complex really quickly. So you can go in here and build out that information. You're probably going to want to do the same thing for your advertising and promotion, because that's such a big part of every ecommerce seller's business. And you're going to want to most likely tie that to your sales model. So you probably have some formula that you've been able to determine, or that you think works, to say if I put this much into promotion I expect this much in sales in these channels. So I don't have it in the template right now, but I would probably break that out into a separate worksheet.

Some of the rest of these you could run some reports, but very quickly I would say that most of these are going to have a separate worksheet. So you'd go through, if you said hey, my rent is locked in, I could lock that in. If your utilities are pretty predictable, if your software, hey, you don't have any plans to increase your software costs, you could keep that fairly simple. But I would say in many businesses each one of these line items very quickly becomes a separate worksheet, or at least a separate table that gets summarized here.

I'm going to put in some sample numbers here and we'll come back and talk about them.

20:26

All right, let's walk through these sample numbers, and I'm going to talk about the ones you want to spend more time on than others.

The biggest one is probably your advertising and promotion. An ecommerce business is going to spend a lot on advertising, and you could have a whole separate worksheet or spreadsheet that would have that information. I suggest you watch our video and download our ecommerce marketing budget template as an example of how you could get started. But you put those numbers there.

A lot of these others I haven't modeled in. Maybe your business has them, maybe they don't. What I would say is look at past years' expenditures, make sure that this lines up with the expense part of your profit and loss or income statement from your accounting software, and probably put it here to the side and have an uplift if it makes sense.

If it doesn't, certain things are very predictable, like insurance is reasonably predictable. It's going to go up a little bit year over year as you grow, but it's not going to be a big jump, and you can probably look at the year over year jumps. That's usually going to be aligned to a certain extent with your revenues.

21:36

Legal and professional fees, that's mostly lawyers and accountants, so you can probably have a decent sense of what that's going to be based on what you've spent in the past. I would say you get what you pay for, so if you're spending less than 500 bucks a month you're probably not getting a lot from them.

Meals, you know, what did you spend in the past, what do you want to spend. Let's come back to payroll expenses, that's probably the most complex one.

Postage and shipping, rent. Rent tends to be pretty predictable, so you can model that in based on your contract, your lease agreement, if you have that.

Software, that's an area that I would often break down into a separate worksheet. Ecommerce sellers use a lot of software and there's a tendency to want to use more. You could model in past expenses and put that in, but there's a couple things to consider. As you move to annual subscription pricing you'll oftentimes get a better deal, but that will skew the cost month to month. The other thing is, by breaking down that budget you can keep track of, oh, I'm not using that plugin anymore, oh, I'm not using that package anymore, I should really cut that out. And that's the other thing that budgeting can do as an exercise. It will remind you where you're spending your money and say, let's sharpen the pencil, let's get rid of what we don't need to spend and add what we do.

Travel, this could be for a conference or something, and not too hard to model out.

23:08

Payroll is the big one. If you look at this, there's two things that are really going to jump out for most sellers, it's going to be three actually. Payroll, advertising, and software.

So for payroll I've actually created a separate worksheet, and I've put in some sample employees. I'm just saying hey, this is a small, super leveraged company, we've got a handful of people. Maybe Sarah's the owner, we've got her salary and her benefits modeled in here, and I've got some hourly employees that are working a lot as well.

You could simply add in some line items. You put in the hourly wage, the hours worked. I've got an approximate for tax. This is a baseline of Social Security and Medicare. You'd probably want to bump this up a little bit for the state you're operating in, so maybe depending on the state, maybe it's bumping up to nine or whatever that is.

If you want to get very granular, you could model out, hey, I'm going to need an employee at this point. So this is assuming there's no changes, fairly static. But if you're looking at hiring, if you're looking at seasonality of employees, you're probably going to want to expand upon this model significantly in a hurry. But this gives you a starting point to build some things out. And then you've got your expenses.

24:39 Actuals, variance, and steering

Now let's put it all together. Now we put this all together and we're going to see our income, our expenses, you're going to see your net operating income by month. And if you have the timing of these expenses lined up appropriately, you're going to see, oh, I might have a little bit of a cash shortfall in February and December, so perhaps you need to plan a little differently there.

You'll see what your percentage of expenses as an income ratio is, and the most important numbers are your net profit margin. I've got that down here. Some businesses will have other expenses and income that they'll push in there. And then your net income. That's a great number to be aware of, because that could help you decide, all right, I'm going to take an owner draw, and this is the schedule I'm going to put it on based on what I'm seeing here in my budget, and this is how much I think I'm going to be able to draw.

25:43

Now the real power of a budget is not just the budgeting exercise. The budgeting exercise should really help you think about, what am I going to sell, how much, how am I going to achieve that number, what's it going to cost me, and what are my underlying expenses to support this.

But the budget should also be a tool that you can use to steer your business every single month. And so as you go month to month, what I would recommend is you take your budget and then you put your actual information here. I've got it very simple, I've got it down to the very simple categories. Income, COGS, expenses. Then I've got other income and expenses as well.

26:53

What you will find is there will be variance. And as you are starting to see variance in one direction or another, you're going to want to make some adjustments.

So let's just put in some fake numbers in here real quick. Let's say hey, had a great January. COGS ended up being a good number, and expenses, let's just say we were slightly over. So you go in and you see your net income was forecast to be 2,400 but it actually ended up being 7,000. Awesome, that's what you want to see. You go to this variance tab and you can see the differences, plus or minus. Where was your income and expenses over or under what you projected it to be.

Same thing, so you keep going forward. Maybe you say, oh, February was a crappy month, and because of that your COGS was also a little bit lower. Again, I don't have perfect numbers here, but your expenses actually were pretty close to on track, so you actually had a greater loss than projected. So you go in, you're going to see your variance, you're going to see not only you were down on income and COGS, but also in your expenses and your net income.

28:02

So what that's going to do is that will allow you to see what the trends are doing with that variance report, and adjust accordingly.

And what I would say is, over time, as you start to see the difference between what you budgeted and what is reality, you could go back in and adjust your budget. For a lot of businesses you could probably do this mid-year and be fine. If you're growing really fast, or you're experiencing an unexpected downturn or upturn, you may want to have an immediate adjustment in that budget, and then use this as a guide.

Because ultimately, what do you want to use this towards? You want to make sure that one, you're staying profitable. And the most important numbers that you're going to want to watch is your gross margin percentage. Are you keeping that in a good healthy range? Good gross margin usually leads to good net profit margin, and that's the thing you want to watch as you change your budget. What happens to your net profit margin? Do you see yourself going below what you want to be? Typically less than 10 percent of net profit margin is not a great place to be as a business, unless it's a deliberate choice as you're trying to plow all of your profits back into growth.

29:16

So use this budget as a tool to achieve profitability and success in your business.

Obviously there's a lot more that we could do here, so if you have questions, love to hear from you. And if you found this video helpful, comment, like it, share it, and subscribe to our channel so that you can have notifications around other videos like this.