Forecasting operating and non-operating expenses

- [Instructor] Costs and expenses can be the difference between a highly profitable company and one that is barely getting by. In this example, I'm going to walk you through how we can use our model to forecast variable costs such as raw materials, direct costs such as manufacturing overheads, indirect expenses like rent, utilities and marketing and flex them based upon our sales forecast and other operating assumptions. I'm also going to share with you some really great Excel techniques that will enhance your model's user interface and make it easier to interact with. Let's go over to our costs and expense forecast schedule. In this schedule, you can see that I've cleanly laid out my forecast into four key segments, cost of goods sold, rent, utilities and marketing. If we had a more complex business, we could get even more granular. When we forecast costs and expenses, please do note that there are many different ways we can go about it. We can forecast top down and bottoms up. We can forecast based upon activity drivers, batches or production runs. We can forecast directly off of the sales forecast. We could even do statistical modeling. Now, in our example, I'm going to keep the forecast methodology relatively simple so that you can easily grasp what I'm doing on the modeling side. Let's begin with cost of goods sold or COGS. Here you can see that I have raw materials, direct labor and manufacturing overhead, all the way to the right, I often like to document my assumptions or other notes. Why? Because remember from a prior video, everything we do should be based upon data, assumptions, interviews, research or something else that is defensible. In the case of raw materials, I'm capturing the cost of cocoa, sugar, dairy, packaging, and more. Looking at history, perhaps we've realized an average rate of 38%, but based upon trends in the cost of cocoa powder, this rate could increase and drive raw materials as a percentage of revenue up to 42%. However, we're also renegotiating with suppliers and may be able to get cost of goods sold down to 32%. I've built my model in such a way that I can easily toggle between these mid, high and low ranges. Further, because I've made these blue fonts indicating that they're hard coded inputs, I can easily override any of these assumptions. For example, instead of 42%, I could change this to 44% and my model updates accordingly. To the left, you'll notice these toggle option buttons. These are called form controls and they enhance the user interface of financial models. I can put this worksheet in front of anyone and they'll likely be able to grasp what I'm doing. They can click on the low, on the mid, on the high and change the assumptions as they want to. Now, how did I create this? You'll note up at the top that I have the developer ribbon. Now, if you've never activated this before, you're going to need to do that. So I'm going to go to file, options, customize ribbon, and over here on the right, you're going to see a list of all of my ribbons. Make sure that you have developer checked and click okay. Now, up here on the developer ribbon, right in the middle, you can see insert form controls. What I'm going to do is I'm going to create a brand new worksheet and show you how to do this from scratch. I'm going to go to insert, option button, and I'm going to draw a horizontal rectangle like this. It says option button number one. Well, I don't really care for that text right here, so I'm going to put my cursor at the left and hit my delete key and get rid of that text. It is still labeled or named option button one, but the text is no longer here. I'm going to make this border very, very snug around it, and then I'm going to move it into the middle of my cell. I'm going to copy, control+C, control+V to paste, control+V to paste. Now, when I do a right click, I'm going to go down here to format control, and when I do, it's going to give me this option for a cell link. I'm going to put the cell link over here to the left at C4. When I click okay, you'll note that nothing happens until I click that first button. It's a one. Second one, it's a two, third one, it's a three. So I can now control the input of this cell based upon which one of these option buttons is selected. Now, I'm going to do something else. I'm going to go back to insert, grab my button, and I'm going to insert three more. This is option four. I'm going to hit my delete key to get rid of that text, make that very snug, and I'm going to, again, going to move this into the middle of my cell. Finally, copy, paste, paste. Well, what do you think is going to happen when I click this button, this button, and this button? Well, now it thinks it's four, five, and six instead of one, two, three. And then also having separate buttons down here. I'm going to go to my insert icon. I'm going to click here on group box, and I'm going to put a box around these buttons and then insert group box and a box around these buttons. Finally, I'm going to do a right click on one button, go to format control, and now for my cell link, I'm going to put it over here to the left. Okay. Now, when I click this, this is a one, a two and a three. This is a one, a two, and a three. Now, even though these group boxes are here, I don't like how they are sitting on top of my field. I'm going to go to home. I'm going to go over here to find and select, selection pane, and here you're going to see that I have group box seven and eight. I'm going to turn off seven and eight. They still exist. They are just not visible. So now I have a very nice user interface. Why does this matter? Because when I go back over here, you're going to see that I have a series of option buttons, and all of these are separate from the ones above and below. Now what I'm going to do is I'm going to go back over to this new sheet and I'm going to create a few columns. I'm going to make this, let's say 32, 38, and 42, 32, 38, 42, and now what I'm going to introduce you to is one of my absolute favorite functions, which is the choose function. The choose function says, let's use an index number. An index number is an integer, one, two, three, et cetera. And then I have value one, value two, value three, et cetera. So what I'm going to do is I'm going to link this index number to this number right here, one, two, and three. When it's one, I want it to be 32%. When it's two, I want it to be 38%. When it's three, I want it to be 42%. Close my parentheses, hit enter. Let's change this to be a percentage, and now you can see 32, 38, 42. This is exactly how I want it to work, and this is exactly what you're seeing over here. When I put my cursor on my active cell, this is saying, let's tie it to the link that is at G7. When this is one, make it I7. When it's two, make it J7. When it's three, make it K7, and each one of these active cells is driving what you see in the forecast hereafter. So in other words, when low is selected, it's one, 32%, 32% forecast, mid, it's 38. 38 is active, 38, forecast. High, 42, 42% active, 42 forecast. And finally, what am I doing over here? I'm using conditional formatting, manage rules, and what this is saying is when a certain cell is equal to M7, make it green. So is this equal to 42? No, it's not. 38, no it's not. 42, yes it is. Make it green. So under this scenario, under the high scenario where raw materials are 42% of revenue, direct labor is 17% of revenue, and manufacturing overhead is 10% of revenue. Our cost of goods sold is set at 69% of revenue, but that's not all. Here in line five, you see that I have another set of inputs. To the right, I state that this is COGS percentage adjustable by inflation factor or anticipated price changes. Under these current circumstances, I am assuming no adjustment, but if we want to incorporate a 4% inflation factor on top of my existing assumptions to the left, all I need to do is to change these values to 104%. And let's take a look at what happens. It is growing this 69% of revenue for cost of goods sold by 4% and has now created new assumptions for my forecast. On my financial forecast, you'll note that my gross margin goes down to 28.2% and my total costs of goods sold jump up to 10.6 million. If I go back and I take this inflation factor away, let's take a look at what happens. Back on my financial forecast. Gross margin jumps up to 31% and total cost of goods sold falls to 10.2. Rent. Rent is pretty straightforward. It's a fixed expense that is paid in advance. I have three levels, 15, 20, and 25 that I'm considering based upon my potential need to expand our footprint at 10985 New Stone Boulevard. Utilities. Let's assume we've looked at historical rates for gas, electric, water, and refuse. We have these ranges of costs. However, we expect utilities to go up and down based upon production volume and activity. In other words, when Barrington is in peak season, spending will go up. In non-peak season, spending will go down. So at line 21, I make the comment that rates are based upon the sales forecast seasonality not to drop below, in this case, 75%. And so you can see that around Valentine's Day, Easter, Mother's Day and Christmas, we end up having a pop of utilities, but outside of these months, they go down. And finally, we have marketing. While many companies may want to tie their marketing spend to known marketing initiatives, we might not have that information. So instead, we know Barrington is likely to market more heavily during peak months and much less during non-peak months. So we adjust the marketing spend by a seasonality factor driven by sales. Once we've built all of our assumptions on the cost expense forecast, we can then bring them into the financial forecast. You'll note that for rent, utilities and marketing, I am using just simple formulas to bring all of this information in. As for cost of goods sold, I'm using an X lookup. X lookup says, lookup a value within an array and return a different array. So in this case, the labels, raw material, direct labor, and manufacturing overhead, those are my lookups at B16, B17 and B18. Find it within the range of these labels on the cost expense forecast schedule within B7 to B9 and pull the appropriate values in column O, column P, and column Q and so on. Then it multiplies this variable rate by L14 or my total sales, and then it divides it by one plus a price increase. Why am I doing this? Well, if I go over to my assumptions page, you'll note that I have this price increase right here. In this case, it's zero. But if I go up here, you're going to note that I have these price increases. I may want to increase price without increasing cost. It removes the price increase from my raw materials, my direct labor, and my manufacturing overhead. Remember, nearly everything we do in forecasting is based upon assumptions. What's happened, what's happening, what we expect to happen, and adjusting accordingly. Building a way that makes assumptions clean, traceable and easy to defend can make a huge difference in your forecast models.