Review of Forecast

Chris Benjamin, MBA & CFO
A free video tutorial from Chris Benjamin, MBA & CFO
Instructor & Management Consultant
4.1 instructor rating • 87 courses • 75,206 students

Learn more from the full course

The Complete Introduction To Accounting and Finance

The Original Course on Accounting & Finance! It's easy once you know the basics. Learn introductory accounting & finance

07:54:25 of on-demand video • Updated November 2020

  • Learn From A Top Rated Instructor Who Has Been Teaching On Udemy Since 2013 and Taught Over 100,000 Students!
  • Understand Basic Accounting Concepts
  • Accounting Terminology
  • Accounting Fundamentals
  • The Audit Process
  • The Accounting Cycle
  • Debits and Credits
  • Accounts Receivable
  • Accounts Payable
  • Forecasting and Budgeting
  • Interpreting Financial Statements
  • Balance Sheets
  • Income Statements
  • Statement of Cash Flows
  • Inventory Accounting and Cost Accounting
  • Waterfall Reporting
  • How to Raise Funds For Your Business
  • The IPO Process
  • Exit Strategies
English [Auto] This video I want to walk you through the sample forecasts that provide it. And each of these tabs down below should be provided as its own PTF just to manage the size of this. I want to walk you through it just because there is so much information on here that I think it's kind of it's difficult to wade through it ourselves without kind of having a background on what's happening. So we'll kind of go by tab and this is kind of a common structure of a financial forecast. Again we use the term forecasts or budget loosely it's all the same thing where we're projecting forward and trying to come up with our projected financial statements. So the first thing we always start with is the assumptions and really the assumptions tab is where everything happens. I mean if you have your assumptions properly laid out the remaining part of the budget just becomes an Excel exercise of properly mapping your financials back to the assumptions or to each other. So we're on the assumptions tab and I'll just note first of all Usually I like to go TAB TAB so I create a separate tab for each item. So a balance sheet PNL cash flow inventory GNN et cetera. So it's nice and it's organized and it kind of follows the same sequence that we'll go through. I'm just going to zoom in here a little bit so it's easier to see for everybody. So in this case we have revenue in the sample houses. So they have a wholesale So this one was broken out by quarter and what they did was this is the sales mix so 10 percent of their sales would be wholesale 90 percent would be to retail. The other formatting kind of thing that I do that I like to use to because this is anything and rad is a cell that you can change. So for example here we have the 10 percent as red and the 90 percent would update automatically based on the formula rate here. So you go through and the people would enter their information the product so that's the sales mix of wholesale retail the product mix of base and upgrade and model and how much they'll sell each and then down here they have sales units So for example Year 1 1 0 et cetera. They go for each wholesale retail and then for each model how many units they predict they'll sell here. They have their pricing for each of these line items and kind of see where this is going. They predict the mix of each the sales mix the product mix how many of each they'll sell. And then down here they have the price per unit. All of this information then drives your revenue on your PNL and you'll see that when we get to it here we have other products so the similar kind of format the pricing that they sell at the wholesale retail mix how many units I think they'll sell. And again this kind of falls a logistical kind of pattern and you know we talk about revenues first. Now we're talking about cost of goods sold in sales and marketing costs. So that makes sense and kind of spend the most time here and then we'll quickly look through the other tabs. So so the houses was actually doghouses that's why you might see some weird terminology in here. So cost of good solid members are Coggs. So they have different cost of goods sold. They sell other things other than doghouses obviously you know pressures bedding et cetera and then they have total costs now. Keep in mind this is all tailor designed for this company for your own company you would do the same thing you would build in assumptions that make sense so. So don't get too hung up on. Did you need this line on this line. This is more tailor made to a specific company quantity discount. So these are important so here they give discounts so these are all for him that will be built then into formulas on the financial statements so when they hit certain numbers here like five hundred one units the discount will automatically be calculated at. And here you can see the numbers are already kind of built in here and there's some formulas it takes a bit of Excel know how to build one of these but I want you to at least see what's involved in kind of the in-depth nature of forecast. And then there are some other product costs get sold. Sales and Marketing month one these are onetime start up costs a collateral marketing research e-commerce Cascio. So some kind of fixed cost if you will. Semi-annual expenses that broke out and then the ongoing expenses as Theo which is search engine optimization if you don't know Internet advertising public relations and then they just have a fixed cost for each quarter. This could be tied to units of sales. I encourage companies to do that but it's only if it makes sense if that's how it would work for you then they have other specific costs that show up in a specific month. So they're here so that these can be tied to the formulas and that the the way you would build the financial statements as he would have it look up on here. So in month 8 it would pull this cost whereas a month 7 it wouldn't. And then that goes on. So there's a sort of there's other specific costs that are linked here that will then go to the PNL some other advertising expenses. Again don't get There's a lot of numbers on here we're not going to go through each of them. It's more just the concept that you would build and pretty much everything you can think of that's a variable that needs to be built in here. This street is fairly intense and there's a lot of information but again this drives all the rest of the entire financial model. So DNA there's some salaries for different people in different positions in here. Other GNAS standards things like rent phone utilities accounting etc. all for a cost. There's some balance sheet assumptions so receivables as a percentage of sales payables as a percentage of operating expense begetting payables cost per. These types of things. So these will be built into your balance sheet. So for example receivables as a percentage of sales. So the formula on the balance sheet will be to take care sales and know that 10 percent of that is going to end up as an accounts receivable and then there's some further balance sheet assumptions these are for the equity section. How much investment is made to date around investor and then future rounds when are they going to happen and how much are they. So using all this information this then drives the rest of the model going now to the Summary tab. This summarizes all the financials so this is completely driven off the three main financials and it's just a nice tight three year summary for this company. So they after all their financial modeling they can look at this. One thing that I mentioned in the presentation is you can always check your balance sheet needs to balance seeing it see if from the assumptions tab there's a lot of information that goes into this and when you're building your financials would be easy to miss something or link something incorrectly. But if you look here at least your balance sheet ties you know that that's a good indicator that that at least that portion was done correctly you could do some quick ratio analysis on this to see if things make sense. You can look at things like your current assets are growing. Here we see the net income as negative a little bit less negative highly positive which is fairly standard in a startup. So this company broke out revenue and Coggs in detail and then on the piano they just summarize that they want to show the detail level. So you'll notice this is now by month whereas the assumptions were built by quarter saw this is doing is spreading the quarter over three months. Not the best way of doing it I would say. Typically you want a 4. If you're going to forecast by months do everything by month. But in this case it works that it serves its purpose. So this again is the revenue in the Coggs by item per month and then all three years if you scroll across or hear what the totals for each year and this is where someone can come to get a detailed look at each month the revenue on the COGS sales and marketing as well. Broke it out in detail here by and I think the PNL them they just have the totals because they didn't want to PNL that had all this detail because you can imagine all this detail plus all the revenue and Coggs detail would be a lot to consume if you want that level detail you can come here and look at it or you can just go to the PNL and see the rolled up version. Again all of this is linked back. So for example if we click on a cell here the formulary it is assumptions be 73. So it's linking back to Selby 73 on our assumptions tab and that goes through out here. You'll notice the divided by three that's the spread cost over a quarter. So JNA same thing. This is kind of the detailed breakdown with the totals on the piano. Same thing it's looking to the assumptions and then spreading those costs over three months. So this company did one something that you don't often see what they actually forecast that their inventory separately which I think is a smart thing to do if you're in a business that carries inventory. You just don't see it often done which is a shame. But part of it is that it does get a little bit complex but essentially they go month by month. They know how much they're going to sell how many they need to make with the mold and the mold depletes. So they've done a good job here of calculating how many units they can produce as well as how many they're going to sell then you get to the three financial statements so typically you would actually do the piano first. They might just have them a little bit out of order. Let's just go to the piano first. So pre means pre kind of year one is kind of their starting point. You see some divide by zero else and that's just because there's no dollars up here. Rather than axe out those formulas just leave them man in case you did have numbers. But again this is all completely driven off of. You'll see these are coming from that revenue and cost of goods sold tab down here sales and marketing comes from our sales and marketing tab. And then he have some DNA which comes from our gene ATAC. So the purpose of those three tabs here was to give you the detail and now the PNL is bringing it all together as a scroll across the Gannett's by month. And then you have your totals by year and then you might recognize this number which was then back our summary tab right here. So you can tell based on how much detail you need to see at any given point there's there's a few places you could go to find that out. Look at the balance sheet that will jump to the cash flow statement. Same thing it's a very summery level. There's cash flow here. We see cash dwindling with cash jumps up and it looks like there is a capital investment because capital investment jumped up so that makes sense. Cash jumped about not quite 300000 but that's what the investment was because they probably had another net loss so you can look at this your accounts receivable if you remember was a an assumption that was driven off of how much sales there was. So that's what these formulas are calculating. At the end of the day your balance sheet balances and lastly is your statement of cash flows. This is completely driven off of those other financials so you have your cash which will come from down here because it starts at the beginning. Your net income from your PNL will change and A-R and a P which comes through your balance sheet long term assets will come from the balance sheet equity also from your balance sheet. And then you have your injections of capital are reflected on your balance sheet in the equity section and then that is then feeding this and pulling and showing you where you have your cash at the end of each period and as you can see that cash is kind of dwindling and that's why it was important to have the 300000 here to make sure that they maintain the positive cash balance. So with that that's a forecast in general it takes a lot of work to build this. Most of the effort is on the assumptions tab properly laying these out thinking through the numbers than anyone who's fairly skilled with both excel and accounting can put together the financial statements themselves. But this is where the real brainpower comes in.