Create Your Free Pro Forma Template for R&D Effortlessly

Free pro forma template for R&D

welcome to proforma modeling we're going to start by forecasting this income statement it's a simple income statement that we can forecast by itself without a balance sheet or cash flow statement we're asked to do so and then answer a few of these questions up here so the way this is set up we have the historical performance of The Firm we have the historical income statement up here in the upper left quadrant I've drawn these golden lines on here just to separate this worksheet into quadrants and here's where our history is we're going to calculate some ratios and then we're going to assume that those ratios can be extended into the future we're told in the problem that's a safe assumption and then we're going to use those drivers to forecast our actual income statement you may be thinking well I could just forecast each line and perhaps with this one you could but as our models get more complicated we won't be able to so let's learn the process proc on this simple model the first thing we're asked to do is calculate the ratio called sales growth year-over-year well I can't calculate it in this cell because I don't know what last year's sales was so I'm going to have to come over here and begin here one way to calculate sales growth is to say what was this year's sales minus last year's sales divided by last year's sales if I do that I get 4% I have to use a lot of parentheses I have to refer to cell C10 twice so I'm not going to do it that way convince yourself by pausing the video that the math is the same if I take this year's sales divided by last year's sales minus one math is the same but it's simpler and it still gives us 4% if I copy that formula to the next year and say okay what we're 2021 sales ID 2020 sales minus 1 imagine that exactly the same it's almost like this is a classroom example for us to learn this on a simple model won't be the way with with real firms that we look at but let's enjoy these simple ratios while they last so my driv going forward if sales growth has been 4% for all the history that I have I could assume it's going to be 4% for the next 5 years the problem tells me I can assume that I could type in a 4% on each one or I could do some kind of math if if these weren't the same we could talk about different ways to average them or Trend them or what have you a real quick way for me to fill all those cells using the cell before is just to highlight them all and say equals cell to the left and then hit control enter and it copies that formula across all those cells so we've looked at our history we've going counterclockwise to calculate our ratios counterclockwise to calculate our drivers or enter our drivers and then counterclockwise up here to the forecast we'll say if we know the sales growth and we know what last year's sales were then it's just that times 1 plus the sales growth and that formula should give us sales in 2020 so let's do this again let's look at cost of goods sold as a percent of sales cost margin some people call that so if I just want to take the cogs from this first year divided by the sales from this first year 331 is 60% of 551 now on this income statement this company is reporting their cogs as a negative value nobody thinks that they actually got paid to get Goods to sell so we know that that 331 is an outflow the 551 is an inflow we can simply add them together to get BR gross profit but what we need to think about on the ratio is if we calculate a - 60% then our cost margin is a positive 60% let's not get wrapped up in the the positive and negative sign of that I could have put a negative sign here but then when I came up here to do the math in cell f11 I would have had to use another negative sign I think we just avoid a lot of Errors if we do it this way bring the negative sign come over here carry the ratio forward as our driver and then come up here now this time I don't want to look at last year's cost of good sold I want to look at the sales times this negative per to get my cogs I want to look at these two numbers and make sure that they're somewhat on Trend with each other and it looks like they are so let's keep going let's look at sgna as a percent of sales Carry It Forward populate our drivers and then take sales times that sgna could be copying those out to the right but I don't need to so my overhead rate as a percent of sales is 37.2% these questions are just up here to make sure that you realize these negatives uh shouldn't be part of your cost margin or things like this won't really use these we'll need numbers like this on our dashboard later uh so for example example if we're talking about tax rates and we just want to be as quick as we can and say the taxes divided by the earnings before taxes gives us a 20% that doesn't mean that the firm's effective tax rate is a negative - 20% just means it's 20% so again let's not get too worried about these numbers I just want you realizing that the negative is just there to make things go faster for us when we're calculating when we're forecasting I got a little bit ahead of myself I haven't been calculating the subtotals but when I put the subtotal there for earnings before taxes I'm just going to multiply it by this -20 and then I'll have the taxes so let's see if this works if we do we sum these up sum these up then I get the 42 in taxes and I can sum these up the last thing for me to do is to calculate the dividend payout rate which is the dividends divided by the net income copy and paste and set all these equal to the cell before them so my dividends would be my net income times the dividend payout rate and it will show up as a negative notice this firm reported all of their outflows as negatives and then if I sum these alt equal is the shortcut to do that but it doesn't work very well on these Columns of numbers sometimes we can use it so if we've got all of these on Trend which they appear to be and the signs all appear to be correct I'm looking at the cells to the left leading up to 2022 I should be able to highlight that entire block at controlr and copy everything forward so the dividend payout rate it looks like was 10% and the retained earnings were 1777 in this simple classroom example our sales growth was constant over both years that we were able to measure it and so were these other metrics they were all constant over several years and we just assumed that they would stay constant going forward and it took the most straightforward path to forecast this income statement in the next few problems you may be asked things like assume that the sales growth is constant for two more years but then doubles and so we just come in here and put an eight after that and we'd get a new answer or assume that there's an Administration change and after 2022 tax rates go to uh minus go to 40% well I wouldn't put a 40% in here I'd put a minus 40 so that our math would work correctly so you can imagine that we might tie a dashboard to this and this would become an input cell where we could change from our dashboard how that sales growth was going to change in two years or how that tax rate was going to change next year we don't always have to carry these ratios forward as drivers we'll build a dashboard to change how they're treated see you in the next video