Collaborate on Excel Pro Forma Template for Product Quality with Ease Using airSlate SignNow

Excel pro forma template for Product quality

If you're looking to learn about pro forma income statements, then you've come to the right place. We're about to cover exactly what a pro forma income statement is, and how to calculate one for your own situation, which I know is extremely exciting. My name is Brandon. I'm the CEO of Poindexter, which is a software that has helped thousands of businesses create their own pro forma's. So we have a little bit of experience in this space, but, today we're going to focus more so on the distinction between creating a pro forma for existing businesses versus new businesses, because the approach for each is much different, and I don't see a lot of videos or tutorials talking about those differences. So, we're going to cover them here today. We'll start by talking about exactly what a pro forma is. So a pro forma income statement is simply a future version of an income statement. And just as a refresher, so, our income statement is just a summary of our financial performance over some period of time. And so a future version of that is how we want our business to perform in the future. So, it kind of represents our financial goals, if you will, or what we want our business to look like at some point in the future. And when that point in the future is can be determined by you or your particular needs. So there's not really a hard and fast rule on that it's just simply the fact that it is a future version of the income statement. And how we calculate the income statement is going to depend upon two different situations, or two different considerations. And as we already mentioned, it's going to matter whether you're doing this for an established business versus a new business and most videos focused on established businesses. So we'll cover those first, and then we'll move on to how it works for new ventures, which is slightly different. So the benefit of existing businesses is that you already have financial performance, financial data being generated by the company. And so you should already have an income statement. And if you don't have an income statement, that is a huge problem. I would stop this video and go get one of those together first because, it's going to be, that's going to be a bigger problem than creating a pro forma. So, with that benefit of already having the data in hand, all we have to really do is decide when in the future are we looking to establish a goal or an outcome for this particular forecast or this particular pro forma. So for now we'll just choose six months from where we are today. And then typically the way this works for existing businesses is pretty straight forward. So you focused on revenue and what we do is we establish some benchmark or some goal that we'd like to achieve with revenue. So an increase of 20% let's say, and all we'd have to do is say that within that six months, we want to achieve a 20% increase. And so that 20% increase times, our current revenue would equal $1,200 in this scenario. So $1,000 plus 20% is $1,200. So, pretty straight forward. That's generally how we reach our revenue forecast for the pro forma. But the thing is about this is that it doesn't really tell us what the hell this means, right? So we have an increase of 20% and we want to achieve it in six months. Great. That's, good that we have a timeline and a goal, but you know, what does that tell us about what we need to do today or each day to achieve this goal? And so I like to actually convert the, revenue forecast into something that is tangible or more tangible. And so, if we say for instance, that we're selling each product here in this fictitious scenario for $10. And so that would equate to 120 sales. And so 120 sales is actually a lot easier to get our heads around as far as, okay, you know, how many customers need to come through the door, you know, and then we can start backing into how many new leads do we need to generate from our marketing channels? So it gives us a lot more actionable insight into what we need to do to actually realize that increase of 20%, which is simply just 20 more customers over the course of that six months. Now we can start to focus on the costs. And, typically the way this works for most tutorials are just going to tell you to do, one thing which is creating a, what is called a common size format. So converting the costs into percentages based on revenue. So if we look at revenue itself, right, this top line revenue, that is 100% of the common size income statement. So when we look at costs, it is simply a factor of dividing the costs by revenue to arrive at some percentage. So we can convert costs of goods sold into a percentage based on revenue. And then that way everything is translated into terms of revenue. And typically what you would want to do if you have an existing business is look at this, common size format over a certain number of months or certain number of periods to get an average, if you will. And then what you can do is you can take this average and you can multiply the new revenue here by this common size amount and you would arrive at some number based on the growth in sales. So it's treating, it's almost treating all of these costs as if they grow in line with sales when you do it this way, which I don't really like that much. And I'm not sure why it's recommended very often that you do that, because typically what you want to do is you'd want to consider which of these costs are going to be fixed and which are going to be variable. And fixed costs tend to stay pretty flat over certain periods of time or within certain windows of time. So things like, you know, your rent or your employee salaries, like things that take a while to actually increase because you have to move to a new building or you have to onboard a new employee, which takes time. So the fixed costs are going to stay pretty flat, but variable costs are really the ones that are going to grow along with revenue. So as you sell each new unit of product, you're going to need, you know, maybe some material costs that go into that or you're going to need some shipping that takes place because you ship each unit to a customer. You know, things that literally are directly related to the delivering and production of the goods or services that you're providing to customers. And a lot of the times those variable costs sit very heavily up in the cost of good sold section, but they're all are also some variable costs that take place in the SG&A or, or sales, general and administrative section as well. So it's really, if you want to get a detailed perspective on what these costs are going to be in the future, I would actually go out and identify which individual line items are fixed versus variable and forecast them appropriately. So keep the fixed cost the same. If you don't expect to do things like move into a new building or hire new employees, keep those costs fixed, but change the variable costs and that will give you a better sense of how things are going to react within at least a short timeframe. If we're looking at a few years out, that might be a different story because generally, you know, we can't go from $1,200 in sales to $100,000 in sales without almost all of our costs growing. So just consider the difference between fixed and variable. And I think that you're going to get a much more detailed sense of how your income statement is going to react in the future. So from there we can calculate all of our profit margins and then we, that's all we have. So that's the pro forma income statement for an existing business and the general approach that you should take. If you wanted to do this on a month by month basis, you would simply just approach it the same exact way. So, you know, from six months here we could forecast out another 20% for the next six months and arrive at another, forecast for 12 months out from now. So just keep that same process going and that's essentially how you approach it. So now we're going to talk about creating a pro forma for a new business, which is slightly different because there are a lot more unknowns with new businesses. We don't have that historical data that we can use to forecast our pro forma income statement, which is a problem, because where do we start? Right? We don't have the information, the same exact process that we use for existing businesses we cannot use here. So what I recommend we do is approach things from a bottoms up perspective. This is what most investors are going to want to see, right? What are the actual activities that you're going to be doing that are driving revenue directly but not focusing on a revenue number and then breaking down how many units we need to sell or customers we need to get as a result of revenue. But instead using the revenue driver to forecast for us, or give us some insight into what revenue will look like at some point in the future, because we have a lot more control over what the revenue drivers are than we do what the actual dollar amount of revenue is. And so once we get an idea of whether we want to forecast revenue based on, you know, our marketing activities or maybe cold calling for instance, we can start to look at what the transaction actually looks like. So we have our revenue drivers, which we have much more control over than the dollar amount of revenue. And then we have the, essentially the monetization model that we're looking at and how we extract value from customers to provide them the value that we give them in the form of goods or services. Right. So how does that transaction looks? So how does it take place? For instance, is it a simple transaction where customers give you money and they get goods or services in return? Or is it something like a subscription model? When does it take place? So just because we have a number of customers that we expect from marketing doesn't mean we actually monetize those customers right away. So based on the interaction, the first interaction that we have with customers as a business, when in time do we monetize them and then how much of course, so the dollar amount that they actually pay. And once we get a sense of when all these factors start to take place and how they take place, then we can get a sense of what our actual revenue is. And just to make this a little more straight forward, we'll actually do a quick example here. And so we will say that we will forecast new customers for the first month here of 100 they will pay an average price of again of $10, and then based on those two factors, we simply multiply them together and we get revenue of $1,000. So pretty straight forward. And then once we actually want to go about forecasting future months, we again, we focus on the revenue driver because that's the thing that we have control over, right? So we want to increase, if we're going to increase anything, it's going to be based on revenue driver, which is the exact opposite way that we did for existing businesses. So we're going to focus on the things we control add a certain percentage of growth and then arrive at revenue based on that growth. So pretty straight forward, much different, and I know then we covered in the existing business scenario, but that is generally how most investors would suggest that you approach this. And so you can take those revenue numbers, input them in the revenue line item in your pro forma income statement, and then we can start to take a look at what the costs are. And so this is going to be very different depending upon your business model, your industry, you know, the capital expense requirements. It really is going to have an almost unlimited number of factors that determine how you fill in the costs. Because again, we can't just simply look at historical common size formats and convert our costs into a percentage of revenue because there is no historical costs. So we actually start, need to start googling, for instance, what are the startup costs for this business, or start up cost budget. And I actually found a pretty helpful resource here by Fundera that lists out the common small business startup costs. So I will include that in link in the description here. But generally you're going to want to do a lot of googling for your particular industry or your particular business. If you can get more specific, the better to get an understanding of what your costs are going to look like. So then you're going to want to start grouping these costs, once you have a list of them, into two different categories. And the first is the cost of goods sold. So these are the direct costs of producing revenue. Okay? So these are the things that without these costs we would not be able to provide our goods or services. And the second is the cost of running the business or the sales, general administrative, also known as operating expenses, things that support the general running of the business. So group, once you have a list of all your costs and group them into those two categories and then that's going to give you a better sense of what the various profit margins are, going forward. And so once we get those costs grouped into two different areas, then we can start to look at very similarly to what we did with the existing businesses, what the variable costs are versus fixed costs. Because your variable costs are going to be a lot more important going forward or in the future. Your fixed costs are going to matter more in the beginning because it's going to comprise a higher percentage of your overall costs, but your variable costs are going to affect the scalability of your business model. So variable costs are things like sales commissions or materials for instance, and your fixed costs might be something, again, like rent. But when we look at it in the beginning, in the early days of business here at let's say a hundred units, your variable costs are going to be very small percentage of your actual costs because you might not be doing that much business yet. But your fixed costs are going to stay the same no matter what. You still need to pay salaries. You still need to pay rent, right? But as you grow the business within a certain, at least window of the size of your business, at least you are going to increase the amount of business you're doing in terms of revenue or the number of units you're selling in this example. And your variable costs are going to become a higher and higher percentage of your overall costs while your fixed costs are going to diminish as far as their overall percentage. So as you grow, your variable costs become more important. They actually affect the overall scalability of your business. So in the early stages especially, you're going to want to identify what those variable costs are and forecast them, ing to the growth in revenue. And you're going to want to be very conservative about how that happens. Because again, the bigger, the small percentages, small percentage changes in variable costs will have a much larger and larger impact as you grow the business. So they might not seem like a lot in the beginning, but when you actually scale the business, they're going to have a drastic impact and could really affect your overall profitability and assumptions underlying whether the business is even viable in the first place. So now all we need to do is out our income statement as we group our cost together into cost of goods sold versus operating expenses and fill them out ing to how they grow over time. And then we can fill in our profitability and we have our pro forma income statement. So it's a lot more research focused here. But generally this approach is really the only way to go about it because again, you don't have any costs yet. So this is the best way we can do that, is rely on what others have done in our industry. So now all we need to do to actually forecast this out further is understand how to use sophisticated financial models or how to build sophisticated financial models, which is very time consuming process. But generally most forecasts or pro forma's, are built within, something like Microsoft Excel. But if you are doing this for a new business, you can also try our software Poindexter, which will do what we've just done in a fraction of the time that it would take in spreadsheets and certainly reduce your stress levels, I think overall, when starting the business. So thank you. I hope you found this helpful, and if you'd like to leave some comments in the comment section, I would be happy to respond.