Streamline Your Billing Excel Sheet for Staffing with airSlate SignNow

Billing excel sheet for Staffing

hi my name's danielle steinberg and what i'd like to take you through today is how to build a rolling forecast model now i know there's lots of fancy software out there that will do it for you but i'm going to show you a method just using plain old standard excel standard excel formulas and maybe just a little bit of formatting so it's going to look like this when we finished it so it's just going to be a chart so we can see our actual and our forecast we can put in our assumptions that go in here and it's going to roll out forward at any point in time it will roll forward for 12 months so let's get started so i've set it up for you like this so we've got our start date here so our start date is based on this number here and then everything rolls forward from there so we say a current month plus 365. we've also set this up with actual and budget so i've used some if to say if it's less than or equal to the current month give me actual otherwise budget and i've also used some flags here so flags are a pretty common method in modeling for working out whether something falls within the forecast period or not so at the top here we've got our budget uh then we've got our actual so what i'm going to do first of all is just populate this with some formulas and i'll say if this row here is equal to the word budget we'll pick up a budget otherwise we'll pick up the actual there we go so we'll copy that across so that creates a block of formulas like that so we can see that it will automatic pick up either the actual or the budget and then of course it's nice to have a little thumb over like that and we can copy that down there we go all right so the next thing that we need to do is uh use we want to use our historical data now you might use some drivers uh sometimes we have a different driver for every single line item in this case we are going to use uh the forecast we're going to use some regression to look at the historical data and then use that to forecast going forward so we're going to use the last couple of months of data so i'm going to start from here because obviously you can't it's quite hard to forecast when you don't have any history so i'm going to use that those two numbers there to create a forecast that goes out over the next year or so but before we do that i might just go back and talk about the forecast function so you know if you've got some data that looks like this we can go in and create a line chart now if i were to look at that create a linear trend you can see exactly what the forecast is going to look like and i can do that by dragging it down but a much better way to do that would be to use a forecast function so forecast will basically uh forecast our numbers directly along that trend line so the x-axis will be july the known wise will be the history and the known x's are the corresponding points on the x-axis so i can just copy that down like that so that gives us a formula that will forecast along the linear trend which is which is fine uh alternatively uh that's kind of a and you know that that method has been around in excel for for a long time there is another way of doing that and that is just to use a forecast sheet this is a relatively new addition to excel and that formula there you can use which is um you can just use it as a forecast.ets which will take into account the seasonality of the historical data so i probably prefer to use that formula the forecast dot etf so that's going to take into account some of the seasonality so rather than just forecasting along a linear trend so i'm going to go in here and use a forecast dot ets i find it easier to use the the dialog box so the target date is going to be that number there so i'll go ahead and put my dollar signs the values are going to be these two okay so what i can what i'm going to do here is anchor the first column because i want it to roll forward so i put the dollar sign in front of the e but nowhere else there we go so the timeline though is similar except i'm going to put the dollar sign in front of the 2 and also in front of the e so we've got to get the dollar signs in this case is really important i'm just going to leave the seasonality blank there we go so if i've got my dollar signs right i should be able to copy it across and down let's just do a bit of a test there we go okay so you can see that it's picking up the numbers correctly so it's rolling forward like that okay all right looking good so i can use that formula there and i can copy it all the way across okay wow and it's going to go all the way we're going to actually forecast out for around about two years except i don't actually want two years i only want it to show for a 12 month rolling forecast so i'll get to that in a moment the first thing i'm going to do is take a little bit of a look at ah okay you can see here we've got a negative now that's a bit of a problem so you can see uh the formula is doing what we asked it to do which is to uh forecast and it really doesn't make sense though to have a negative value and this often often happens with forecast functions so i'm going to use a max formula around it and say max comma zero so that is going to give me the maximum value between the formula and a zero okay so that got rid of that problem the next thing we want to do is only forecast out for 12 months so it doesn't make sense for us to show a forecast here because we already have actual data for it we only want to forecast from the forecast period forwards and that's where these ones and zeros come into it so i'm going to take the formula that we've already built and i'm going to multiply it by the zeros and the ones again i'm going to get my dollar signs right there we go so that's a that's a zero because i don't want to include that in my rolling forecast control shift right arrow there we go so that has forecasted uh a long and only taken into account the the 12-month period going forward and i've set up some little calculations down below and that is uh is what has been used for my my chart that's been set up so the dotted lines there i did that by using a dash type of a dotted line which is kind of nice when it shows the forecast period and then a solid line for the actual and the budget there we go and the last thing i'm going to do there is to just go in and add in some sparklines because the sparklines just add a nice bit of visual uh to the end okay i'm going to change the color i'm going to sort of make get it to match my color scheme and i might just make it a little bit heavier there we are maybe this is a little bit too heavy okay there we are and you could also choose the high point and the low point perhaps and drag that down and there we have it so the thing that i really like about this method is that it's completely dynamic so next year or next month even when you come along and you want to make a change so if you want to change if you want to change the current month you would say i'm looking at the third and you'll see that that will uh that everything will flow through and then if i were to add in some data for my actual everything will flow through beautifully and the other thing that i really like is that if i were to change that to 2022 there we go you can see everything's linked throughout and everything has completely updated as well of course you'll still need to update the numbers but building a model like this is completely flexible and dynamic and it means that once i know it takes a little bit of work to set up but once you've got it set up it's going to save you so much time in the end