For most business professionals, Excel is an essential tool for budgeting and forecasting activities. But inefficient and outdated Excel techniques still abound because many of those professionals don’t take advantage of the numerous built-in tools Excel offers. If you’re spending too much time working on budgeting and forecasting tasks, you’re probably not receiving the results you need to best assist in organizational performance planning. Avoid lost time, errors, and reports that are not as accurate as they should be by brushing up on the latest tips and features for using this powerful budgeting software.
More specifically, you’ll learn to manage multiple versions of the same model more effectively and solve complex, multi-variable equations with ease. Handle the inevitable circular references that appear in budgeting workbooks and obtain more accurate capital budgeting results by joining this session. You’ll even be able to use regression analysis to improve the quality of your budgeting and forecasting spreadsheets. By taking advantage of the tools discussed and demonstrated in this session, you can reduce the amount of time you spend in budgeting and forecasting activities and get better results at the same time.
Important Copyright Information
Dear Valued Customer:
We have received quite a number of questions regarding the permissibility of copying and distributing our print transcripts, as well as questions regarding the
conferencing of calls to other sites by our listeners. The main questions include:
May I photocopy my transcript packet and send it to others?
May I fax or email a copy of my transcript packet to one or more colleagues to use?
The quick answer to all of the above questions is “No.” If you or others you know are doing any of the above, you are infringing on the audioconferences’ copyright.
Probably you weren’t aware this is a legal infraction, but it is, and it is punishable by law through damage awards, etc.
To explore low-cost alternatives for legally having more than one copy of this transcript, please contact our Customer Relations Department at (800)223-8720. Our
representatives will be pleased to help while staying in compliance with copyright law.
P.S. We trust that our audience of professionals such as you will respect our legal copyright “on the honor system.” But if we learn of willful violations, we may have
to seek legal remedies.
P.P.S. If you know of any unauthorized copying or distribution of attendee packets or dial-in information, please let me know about it in confidence so that we may
follow up with the party and set them on the right path. (Your name will be kept confidential.)
I would now like to introduce your speaker for today, Thomas G. Stephens.
Order Now To Read Full Version Along With Conference Material
Thomas is a certified public accountant and a shareholder in K2 Enterprises, a leading technology consulting and training organization. At K2 Enterprises, Tommy develops and presents continuing education programs to business professionals across the United States and Canada. To date, he has lectured nationally on subjects such as internal controls for small businesses, technology strategies, computer hardware and software applications, tax strategies and compliance, and financial accounting standards and applications. Over the past 22 years, he has presented over 2,100 educational sessions to over 61,000 participants nationwide.
Tommy earned a B.S. in Business Administration (with a major in Accounting) from Auburn University in 1985, and an M.S. (with a major in Finance) from Georgia State University in 1992. In additions to being a CPA, he is a Certified Information Technology Professional and a Chartered Global Management Accountant. He currently holds memberships in the American Institute of Certified Public Accountants and the Georgia Society of Certified Public Accountants and is a past member of the Institute of Internal Auditors. In addition to his 14 years of experience at K2 Enterprises, Tommy has worked in public accounting practices, served as an internal auditor for a Fortune 50 company, and worked as a chief financial officer of an Atlanta-based business. You may contact him directly at firstname.lastname@example.org.
Thomas, welcome to the program. We are now ready to begin.
Thomas G. Stephens: And encourage you to jot down my email address or and/or Twitter handle if perhaps you miss those the first time around. That way you will know how to reach me after today’s session is over. I welcome, I appreciate, I encourage your questions. As indicated, we will have a dedicated Q&A session near the end of this session. Let’s – if something comes up later on, if you’d like to follow up with me for any reason I’ll be more than happy to converse with you. Always feel free to contact me via email with email address is email@example.com or if you’re so inclined you could follow me or direct message me on Twitter where my Twitter handle as you can see is @TommyStephens.
Specific learning objectives for today’s program. Again, we are focusing on Excel as a budgeting tool. So, let’s focus on the features that are in Excel that are, shall we say somewhat budgeting-centric. Included to those will be Excel Solver feature, how do we take advantage of Solver to optimize results in complex budgeting and forecasting models. Let’s also be sure that you’re aware of and can use Scenario Manager that will help you manage multiple versions of the same budgeting spreadsheet. Oftentimes we find ourselves with different iterations or different versions of a budget the best case versus the worst case, et cetera, et cetera. And we typically or probably using a file Save
As methodology now to try to track the different versions of the budget. Scenario Manager I think you will see and will agree with me at the end of our time together, is a superior alternative. Let's make sure at the end of our time together that you are able to incorporate regression analysis into your budgeting and your forecasting activities and that you understand why this is a critical feature and perhaps let's even add on to that that you have understand that it's not necessarily difficult to do. I know a lot of us have some bad memories about regression analysis from our college statistics courses.
Let's talk about new forecasting features that were added to Excel 2016 and let me pause on this point for just one moment. Today’s program is an Excel program and what I mean by that is it really doesn't matter what version of Excel you are running. Everything we talk about today will be fully applicable to all of the more recent versions of Excel 2007 through Excel 2016 unless I specifically tell you otherwise. There are some new features that had been added into Excel 2016 that are really, really powerful and useful in a budgeting context. I recognized that many of you are not yet on Excel 2016 but I'm going to show you a couple of these features nonetheless, so that if and when you do make the upgrade in to Excel 2016 you know how to take advantage of these tools.
Two additional learning objectives: at the end of our time together today let's make sure that you are aware of and can work with Excels XNPV function and XIRR functions. These are my estimation are superior alternatives to the traditional NPV and IRR functions in capital budgeting situations. And then finally, let's address the issue of circular references in our workbooks, budgeting and forecasting oriented workbooks tend to have the circular references crop up in them and we need to understand how to address these. As you can tell we've got a pretty ambitious agenda in front of us. So I'm going to dispense with PowerPoint and we are going to be working live at Excel for the rest of our time together.
Let’s get into our first example, our first conversation and that is working with Excel’s Goal Seeking function. Now to be perfectly honest about it I'm not a huge proponent of goal seeking. In fact, the only reason I show goal seeking in this session is let's call it as a means to an end. When we get into our next function while the Solver tool Solver will be much more meaningful to you if you understand what Goal Seeking is all about. In this particular example, suppose that we have been – we are working for a budget obviously and we have been told that we need to plan on sales such that we have a volume of sales that will yield a gross profit of $6 million. Furthermore, suppose that we know, based on historical data, that our gross profit percentage is typically in the range of around 29 percent. So the question then is what level of sales do I need to generate in order to get a gross profit, total gross profit of $6 million? Now again, this is extremely simplistic, overly simplistic.
Likely most of us could do this with simple sixth grade math. We could take $6 million of gross profit divided by 29 percent and that would back into our required sales calculation. On the other hand we could also use Goal Seeking to do this for us. As you see in cell B7 I have a formula of B4 times B5, total sales times the gross profit percentage of 29 percent. Now all I have to do, in order to take advantage of a Goal Seeking feature is from the Data tab of the ribbon I'm going to from the What if Analysis section I'm going to choose Goal Seek. So Data tab of the ribbon, What if Analysis, Goal Seek. And I'm telling Excel now in this illustration Excel I need you to set cell B7 to a value of $6 million and do so by changing the value in cell B4. So go set cell B7 to a value of $6 million by changing whatever is in cell B4, click OK. Excel does the calculation and tells us, of course, roughly $20.6 million. Now again, I don't need Goal Seek to do that calculation for me. Most of you likely would not need Goal Seek to do that calculation for you. But if we don't talk for just a moment about Goal Seeking then some of the other tools down the street, down the road in the session probably won't make quite as much sense.
Let's get to perhaps a more realistic example of working with Goal Seek. And I think as I do this I'm going to go in and open up a little tool that's going to make something a bit easier for you to see as we work through some of our other illustrations today. This is actually an annotation of a project that I worked on a number of years ago was doing a little fundraising for my alma mater, they were trying to endare, I'm sorry endow a chair for a professor and the question was how much money did we need to raise in order to endow that chair so that over the next 30 years now the salary of the professor would be fully funded by this particular endowment.
Now, we are making some assumptions in this case, as you can see. That we're going to pay the professor an initial salary of $160,000. We are going to assume annual cost of living in merit raises of 4.5 percent and we are going to assume a 7.5 percent earnings rate on the investment. So the question is, if I raise a million dollars will I have enough money to fully fund this endowment. And as you can see and what effectively amounts to an amortization schedule on the right hand side of the screen raising only a million dollar will not fully fund this particular chair. Now in the past, the way many of us have likely tried to solve this problem is let's be honest with each other, trial and error. That is we would jump in and we would change the million dollars to $2 million. Is $2 million enough money? We scroll down effectively what amounts to, again an amortization schedule. We find out that $2 million is not enough money. And we would continue to plug in numbers until such time as we found whatever the magic number was.
We could solve this a number of different ways. In fact, since for all practical purposes what we are looking at on the right hand side of the window is an amortization schedule. We could use Excel’s present value function to solve it. But I've chosen in this case to solve it via Goal Seeking and let's show you how that's going to work. I'm going to go all the way down here to the ending balance in the account which right now is negative $9.2 million and, of course, that's at the end of 30 years and I am from What if Analysis, again, I'm going to choose Goal Seek. Okay, Excel this time set the value that’s in cell H34 to 0 and do that by changing the value in a specific cell and of course that specific cell is cell B4. Go make cell B4 whatever it needs to be such that if we're earning 7.5 percent of the investment and we've got 4.5 percent annual cost of living in merit raises, all of the all of the mathematics behind this are going to tell us that we need to, in this case, raise, as you can see $3,51,765. That’s the answer according to Goal Seek, little over $3 million. If we indeed raise that $3 million then see that at the end of our 30-year period we are in fact looking at a balance of 0.
Now, as we think about Goal Seeking and many people are going to look at that eulogy that's awfully simplistic and I would agree with that statement. But if I didn't show you a Goal Seeking then would we now start to talk about Excel Solver feature. Go, the Solver tool might not be as useful to you. So let's give you a little bit of background on Solver. Solver, oh let's call it Goal Seeking on steroids. Solver allows us to solve complex multivariable equations that have constraints embedded in them. Let me say that again, complex multi-variable equations that have constraints in them. It's very easy in a budgeting environment to go to your sales manager and ask, “Okay, what are sales going to be next year?”
And maybe the sales manager says, “$50 million.” Now in that case, let's suppose we are just coming off of a year where total sales were pick a number, $35 million. Even if the sales team could generate the extra $15 million in sales, would we as an organization be able to fund the extra $15 million dollars in sales? Because that is going to require a ramp up the accounts receivable that if we're selling inventory is likely going to require us to stock more inventory that is probably up and/or at least potentially going to require additional salespeople and the expenses associated with that. Maybe we are doing the cash flow estimate off of that and we determine that if we are indeed going to move from $35 million to $50 million dollars in sales, that's going to require an increase of, pick a number, $8 million in working capital.
And perhaps, we are already fully extended on our line of credit. We don't have the ability to borrow any more money from the bank. Our debt-to-equity ratios are already against whatever constraints that have been imposed upon us. You see when we talk about budgeting, we are always talking about the allocation of scarce resources and we are always going to be subject to constraints. What we just talked about a few months ago with Goal Seeking doesn't allow us to model constraints. Solver allows us to build complex multi-variable equations that are subject to constraints. Let's show you how you work with it.
First off, as I'm looking it shown, as I'm looking at this model to make you aware of something, if we go to the Data tab of the ribbon under What If Analysis that's not where you find Solver. Remember under What If Analysis is where you find Goal Seek and also Scenario Manager which is something else we're going to talk about in a few moments. To find Solver that is going to be on the Data tab of the ribbon. And hopefully, you are going to see it all the way on the far right hand side where you currently see I have my cursor highlighting the Solver icon. If you are following along at your desktop right now and you do not see Solver sitting out there, don't panic. Solver is an Excel Add-In. It's a Microsoft-provided Excel Add-In that your computer up that is you're the one who did the installation of Microsoft Office. The good news there is that the files associated with the Solver Add-In probably got copied on to your computer when you installed Microsoft Office in the first place. The bad news is if you didn't go and check a box to activate Solver then even though the file is sitting on your computer you won't have access to it. So if you're looking at your computer right now or perhaps after this conference is over, and you don't see Solver here are the instructions for activating Solver. Click on the File tab of the ribbon and from the File tab of the ribbon let's go all the way down to Options. And from Options, let's click on Add-Ins. So File tab of the ribbon, Options followed by Add-Ins. Now when we click on Add-Ins I will go and tell you that Solver what we call an Excel Add-In. You'll notice right down here toward the bottom there are different types of Add-Ins. Currently, my computer set to manage Excel Add-Ins, that's what I need to do. But if I were to click that drop-down arrow I see some other options there. I want to manage the Excel Add-Ins and I will do so by clicking the Go button. I click Go and then in this dialogue box I need to make sure that the check box next to Solver Add-In has been checked. If you get to this point and you don’t see the Solver Add-In as a checkbox in this particular dialog box then most likely your IT staff set your computer up for you and that probably means that you do not have the right to go in and activate the Solver Add-In. In that scenario, you'll talk with your IT staff and convince them to turn on this feature for you. They will need to copy the file onto your computer and check the box to add it in. For most of us, I think if we're self-managing our own computers Solver is probably already activated or if it's not, it's going to be nothing more than just simply going and checking this box. By the way, as long as you're inside this dialog box also check these two boxes right up at the top. One says Analysis Toolpak and the other says Analysis Toolpak-VBA. Those are going to become important a little bit later in our conversation today.
So step number one to using Solver is to make sure that you have access to it. Step number two is to create your model. In this case, I'm trying to create a model that I can use to tell me what my right product mix would be in order to maximize gross profit. Now that sounds like a very, very simple exercise because as we can see one of our products of Task B as I've labeled it here has a gross profit per unit of 200. And that I can sell a maximum of 500 of these during any planning period. Let's assume we're on a monthly planning period here. So what I would do, of course, is sell all I could of Task B and then sell all I could of Task A and then any resources I have left over I would concentrate on selling Task C. If it were only that simple. I have taken the liberty in this model of adding in multiple constraints. The first constraint that we're looking at is when we look at Task A, B, and C respectively as you can see we can sell 500 of each of those items during the planning period. But we have an overall capacity constraint as indicated in cell F2 of 750 units. Furthermore, for every 1 of Task C that I sell I reduce my capacity on Task A and Task B by 50 percent of that amount. And likewise, for every unit of Task B that I sell I further reduce my capacity of Task A by one-half of that amount. This starts to get into the complexities that we oftentimes are faced when we're trying to solve multi-variable complex equations in Excel, particularly in a situation like this where we have constraints that we have to model against. And unfortunately what happens in so many cases like this is the way we. the average user let me say it, that way the way average user tell attempts to solve these types of problems is really just through trial and error, not unlike the first example that I showed with Solver a few moments ago. We just start plugging in different numbers in cells B6 through D6. If we will go to Solver, click on Solver we get to build out this model subject to the constraints, and let me step you through it. I've already taken the liberty of doing the typing in advance because the last thing you wanted to watch me to do in a short session like this is tied. So what we're saying is we have an objective cell.
Our objective cell right now is cell F7. Go set cell F7 to a maximum value. So let's maximize cell F7. Now if F7 were Income Tax of course, then we would say minimize it or if we needed to make cell F7 a Specific Value we could say make it a Specific Value. But in this case, we're trying to maximize total gross profit so obviously we choose the max function. Go maximize cell F7 by changing cells B6 through D6. Recall that with Solver a few moments ago we can only change one cell. Here I can change multiple cells, in this case, B6 through D6. These are the cells that indicate the total quantity being sold for each of the items during each planning period. So for Tasks A, B and C what is the quantity that we are selling? Go maximize cell F7 by changing cells B6 through D6 subject to the following constraints. And here is where I've gone and modeled all of our constraints. Maybe by contract we have to sell a minimum of 50 units of each task. So B6 has to be greater than or equal to 50. C6 has to be greater than or equal to 50. D6 also has to be greater than or equal to 50. The total amount that I'm selling, that is the amount in cell F2, cannot be greater than my total overall, I'm sorry the amount in F6 cannot be greater than the total overall capacity as indicated in cell F2, that is 750 units. So F6 has to be less than or equal to 750 units. And then, of course, there are other constraints that are modeled in there.
If you can get to this point, that is, if you can identify your constraints and express your constraints as formulas inside Solver you've got the problem right, right now. Maybe we are not dealing with something like this. Maybe we are dealing with a debt-to-equity ratio can exceed 62 percent or maybe we are dealing with I can only borrow an additional million dollars on the line of credit. Whatever type of constraint you are facing if you can identify those and express those as formulas as I have done in this case, then once you do that, all you have to do is click the Solve button down here at the bottom. Click Solve. Solver has gone through and it is now identified the appropriate product mix that allows me to maximize total gross profit subject to the constraints that we identified. And as you can see I'm going to sell 50 units of Task C, 475 units of Task B and 225 units of Task A. That is the one and only solution in this case that's maximizes gross profit at $139,375.
Candidly, the biggest challenge you will face when working with Solver is identifying the constraints. Again, if you can identify the constraints the rest of this stuff is actually pretty easy. And so, I hope that you’ll take a strong look at Solver and remember if you don't see it up here on the Data tab of the ribbon, don’t panic. It's probably just simply a matter of either A going back into Excels Options and checking the box that says that you want to use Solver. Or B, discussing this with your IT staff and getting your IT staff to copy the files directly onto your computer. Again Solver is a Microsoft-provided Add-In. So we’re, oftentimes, we use the term Add-In people read into that oh, I've got to go spend some extra money. No, you already have it or you already have access to it. It's just making sure no matter – making sure that it's actually activated on your computer.
I'm going to close out of that demo file and let's go to our next discussion which is using one of Excel’s best features for budgeting purposes, that being Scenario Manager. In a budgeting environment, we oftentimes have multiple scenarios of essentially what amounts to the same budget. I've got, perhaps in a simplistic world I've got my best case scenario and my worst case scenario and my most likely case scenario and then perhaps I've got monthly or quarterly updates to the budget as we roll throughout the year. And for some, the idea of the monthly or the quarterly update might seem a little bit foreign. In fact, I'm a very strong proponent of the notion that budgets should not be cast in concrete but rather budgets should be living and breathing documents that change as economic conditions change. For instance, if I built my budget assuming that the interest rate on my line of credit was going to remain 4 percent throughout the course of the year, and then I got a call from my friendly banker that says Gee, Tommy I'm sorry but we're going to have to bump the interest rate up to 5 percent. I need to go in and immediately change that in the budget because I have no chance of making my numbers. In that case, I've got a macroeconomic event that I have no control over which virtually guarantees me that I'm not going to make the numbers. I need to change my budget right then and there to reflect this reality.
So what oftentimes happens, the practice that we see in most situations is those who in fact do want to manage multiple versions of the budget or typically giving that to what I like to describe as a File Save As process. They create their best case budget and then they run a File Save As on it and they save it off as a different file name and then they create their worst case budget and do the same thing on the worst case budget and the most likely case budget and this in multiple Excel workbooks, each Excel workbook representing a different cut, if you will, on the budget. And there's nothing necessarily wrong with that except, let's be honest, it's not the most efficient way of operating. Because if you have let's say six different versions of the budget saved as six different workbooks and then you determine that you need to go in and make, let's call it a structural change in the budget. That is you need to insert a row, for example. Insert or change a formula or something like that. To be consistent you've got to go make that change in all of the saved workbooks.
Again doesn’t sound like a lot of efficiency there. It also sounds like an opportunity to make a mistake by changing the budget in file or changing the five of the six workbooks but accidentally leaving the sixth workbook unchanged. Now we do not have apples to apples comparisons across all of the budgets. So what I'd like to do is see if I can manage all of that inside a singular Excel workbook. And we absolutely positively can do this by taking advantage of a feature in Excel known as Scenario Manager. Scenario Manager, like Solver, like Goal Seek, like most of the things we're going to talk about in this session has been in Excel literally for as long as I can remember. I honestly cannot think of a day when Scenario Manager was not part of Excel. We will find Scenario Manager on the Data tab of the ribbon under What If Analysis. Why? Well because the Scenario Manager is there to help us manage these What If Scenarios. And when I click on Scenario Manager, in this case, you'll see that I already have three different scenarios established for this particular budget. I've got my worst case. I've got my best case and I’ve got my most likely case scenarios. Now before I show you the interaction between Scenario Manager in the workbook it's probably useful for me to give you a very, very quick overview of how this workbook is put together. And I'm not necessarily advocating this approach for your budgeting workbooks but from a teaching perspective this methodology works pretty well.
You can say for example in column B I've got my, all of my budget assumptions for, in this example, January of 2017. What's more important though is that each of the shaded cells, for example the 37 days in accounts receivable collection period, the 24 days sales in inventory, the increase or decrease in other current assets. $4,000 in depreciation expense. Each of those shaded cells represents an assumption. It's a number that I key punched into Excel. Each of these unshaded cells, for example, calculated accounts receivable, investment in inventory, calculated monthly purchases, those are as their names indicate calculations. Now what’s important about this is if we look at the $13,000 in calculated accounts receivable and I'll click on my budgeted balance sheet now. I can actually see that that number on the budgeted balance sheet zooming in a little bit for you, that number is $13,000. So what’s happening is going back to our assumptions tab, as these calculations change and they will change when I change for instance the accounts receivable collection here and changing the accounts receivable collection periods of 43 days bumps that calculation up to $15,000. So as that calculation changes it, of course, also changes over on the budgeted or the forecasted financial statements.
Okay so, understanding kind of how this thing is put together, again, I think will be is useful to really understanding what Scenario Manager does for us. Scenario Manager allows me to manage the assumptions on a worksheet. More specifically, Scenario Manager allows me to save and quickly recall up to 32 variables, up to 32 assumptions on any single spreadsheet. So when I go and choose under What If Analysis Scenario Manager and say that I want the best case scenario, the best case scenario when I click Show has just changed up to 32 of the variables or 32 of the assumptions on the spreadsheet which means all of the calculations that depend either directly or indirectly on those 32 variables have also just changed. We're in our best case scenario right now, if I close out and go and look at the balance sheet, for example, I see that our working capital, I'm sorry, a total current assets $955,000.
Now if we're in that budget meeting and it's late on a Friday afternoon and everyone’s tempers are starting to flare just a little bit because we are all tired of working on this budget and someone wants disabled yeah, I understand that's the best case scenario but can you show me the worst case scenario? All I've got to do is click back on assumptions, go to What If Analysis, Scenario Manager. Here's our worst case set of assumptions. I click Show and now all of the variables that have been, that are associated with the worst case assumptions are injected into the spreadsheet. And if I go to my balance sheet I can see now that total current assets in that scenario is $780,000. Get what’s going on there right, we're able to very, very quickly go in and recall sets of assumptions. So the question then becomes, how do we save those sets of assumptions in the first place? Well let's go to What If Analysis, let's go to Scenario Manager and let's actually create a new scenario.
I’ll do so by clicking Add to be able to add a scenario and I will call this, oh perhaps our First Quarter Update. Now, remember when we are adding a scenario to Scenario Manager, that scenario can drive if you will, it can store and manage up to 32 assumptions on a given spreadsheet. On a given worksheet I should say. And the 32 assumptions or the up to 32 assumptions can be different for each and every one of the scenarios. In other words, it’s not absolutely necessary that we go in and say oh, I want to change B6, BA 2152, 33, 34 and 48 across all of the different scenarios. I can change different assumptions within each scenario.
In this example though, to keep things somewhat consistent, I will just change the same cells in this scenario as I changed in the last scenario that I saved. That’s where Excel is getting me cell references is that it remembers the last scenario you saved and it’s assuming that you want to manage the same sets of assumptions. We certainly can go in and add some comments if necessary. I would encourage you to do that though I’m not going to take the time to do it right now. Always document your spreadsheets. Make sure people understand where the numbers come from.
I’ll click Okay. And now I merely need to indicate for each of those assumptions that I want to change, what value do I want in each of those cells? Let’s say, for example, on the Account Receivable Collection here which is the first one. Maybe we are in the place where the economy is really going big game busters right now and people are paying the bills on time, so we got 31 days. But say we’ve got great relationships with our suppliers, we don’t need to stock that much inventory, we can get replenished real quickly. So, I’m going to take that down to 18 days. Let’s say we’ve got great terms with our suppliers and we can stretch them out, maybe they’ve given us net 60 terms, we are going to pay you five days early. So, we’ll say 55 days in accounts payable. Enter straight on the line of credit. We got great news from our banker. You now have the three percent and total sale for the month of January is going to be $1.2 million.
There are also a couple of additional assumptions down here. Let’s get to those. Maybe we don’t have a tremendous amount of competition in this particular market. So, our gross profit percentage is estimated to be 51 percent. And I’m going to pay my people well. We are going to pay them some bonuses. They’ve been working really hard. So, I’ll set total salaries, wages and salaries to $350,000. That’s all there is to creating a scenario. You just go in and say I want to add a scenario. Here are the cells that I want to change and here are the assumptions that I want in those cells.
When I want to see the results of my effort, I click on first quarter update. In fact, before
I do that, let me close out of there for a moment. Let’s go back to our balance sheet just to mark that number again. So, we’ve got $780,000 in total current assets under whatever scenario we happen to be in right now. Now, let’s go back to our assumptions. Let’s go to Scenario Manager and let’s say that we want our First Quarter Update scenario to be shown. So, I select that scenario and I click Show and that causes all of those assumptions be in place and I can see where I made a key punching error if you want to go but that is $12 million instead of $1.2 million but that’s okay.
And we now go to our balance sheet. What do we find? We got $6.5 million in total current assets. Now, again if we got somebody in the room who wants to quickly go back to a different scenario, all we have to do is return to the scene of the crime that is in this case, the Assumptions tab, What If Analysis scenario manager and I want to see my best case scenario. Click Show and now all of the numbers have changed in the best case scenario.
What an absolutely fantastic tool Scenario Manager it is. What an absolutely fantastic tool for helping us to quickly toggle from one version of a budget to another. But it gets better. Because I think there is a very, very strong probability that someone on our team is going to ask us, “You know Tommy, I’d like to see a report that summarizes all of the assumptions from all of the scenarios and shows me all of the results from all of the scenarios. Well, fortunately there is such a report. When we go into scenario manager, we choose the scenario summary. Just click on the Summary button. And now I am being asked okay, tell me what result cells do you want to monitor? In this case, I’m monitoring cell B62 and B63.
As you can see near the bottom of the screen, I’ll scroll up a little bit to make that easier for you. B62 is net income. B63 is ending cash. So, let me know across all scenarios what the results were for net income and ending cash. And ultimately all I have to do is click Okay and Excel generates for me this report that as you can see in cell – I’m sorry, in columns E, F, G and H, we’re looking at our most likely case, our worst case, our best case and out first quarter update scenarios. We see what these specific variables are associated with each of those scenarios and we see at the bottom, the results that are associated with each of the scenarios.
Life gets a lot easier in budgeting and forecasting environments that we are prepared to take advantage of Scenario Manager. We have been working with that in the past and we strongly encourage you to go and look for that. And one of the great things there is that’s not an add-in. You don’t have to activate it or anything like that. It’s just automatically there for you.
Let’s take a quick glance at another tool embedded in Excel and it’s been in Excel for a long, long time, that I think can significantly improve the results, the accuracy of what we are getting with our budgets and forecast. In this particular scenario, I’m looking at some historical data obviously. And I’m looking at monthly sales compared to what we spent on advertising in the prior month. Now, I could be doing this for any number of reasons. Perhaps as we kind of scroll through this data, we are looking at about three years of data here. Perhaps we are looking at this and we are saying you know, there appears to be a general trend of the more we spend on advertising, the higher sales are. Well, we certainly hope that’s the case and if that’s not the case, why are we spending any money at all on advertising? So, maybe my sales teams come to me and they said that they think that they can generate $3 million in sales next year but they are only going to need to spend $18,000 to do so.
Now, without building any formulas or anything of that nature, is that realistic given the historical results that we have here? We are seeing that to generate approximately $1.9 million of sales, we are having to spend in the ballpark of $22,000 or thereabouts. So, is it realistic to say that we are going to bump sales up to $3 million but cut advertising expense? That doesn’t sound that realistic to me. I could use regression analysis and that’s what we are talking about at this point. I could use Excel’s regression tools to help me audit or check or verify other people’s forecast. I can also use regression analysis to help me predict the future based on historical results. And then, I can use some extensions if you will of regression analysis to do all of this stuff for me as well.
Let’s take a look at that. I don’t want to get hung up on the term regression. Let’s take a look at the relationships between these numbers. I think that’s probably a better way of phrasing this. Perhaps the easiest way of seeing this is to simply select this data and plot this data on a scatter plot chart. As we plot that data on just an ordinary Excel scatter plot chart, also known as an XY Chart, you can clearly see on the horizontal axis at the bottom, you can clearly see that the more money we spend on advertising, the higher total sales are. In this example, the presumption is that advertising expense is the independent variable and sales revenue depends on advertising expense. So, the more you spend on advertising at least in theory, the higher total sales happens to be.
Now, if you recall from your college days in statistics class that you likely hated, you can imagine a straight line running right through the middle of all of those dots such that that line was not too terribly far away from any single dot. That’s the definition of a linear regression model. And in fact, the easiest way I think of getting this in Excel is to right click on any of these dots. By the way, I should confess to you I’m a right clicker in Excel. I think Microsoft is doing a fantastic job of anticipating my next move. My next move in Excel is almost always on the right click popup menu. Whether I’m working with pitted tables or charts or anything else, my next move is almost always on the right click popup menu. In this case, I want to add a trend line to this chart. And when I add that trend line, there is the line that runs right through the middle of all of those dots such that that line is not too terribly far away from any single dot. Moreover, in this format trend line dialogue box or task panel on the far right hand side, you’ll look and you’ll see a couple of options right here that says Display the Equation and the R Square value on the chart. I’m simply going to check those two boxes and I have just built the world’s easiest linear regression model, because I now have the regression formula sitting on this chart.
And that regression formula basically in this case is telling me that I can estimate future sales as being approximately $10,538 plus $85 and some change for every dollar I spend on advertising expense. And because that R square value is so high, it’s close to 1, more specifically it’s greater than 0.8 R square and just so you know, the measure of the fit of the data. Because that is between 0.8 and 1.0 or negative 0.8 and negative 1.0, we can say that we have a reasonably good fit of the data. Therefore, I can use this regression model with some level of confidence.
So, now again, going back to this original set of data, if my sales people are coming at me and saying, “Hey, we think we can generate $3 million and spend only $18,000 in advertising expense”, I could go back over here, plug $18,000 in, multiply 18,000 times $85 and approximately 18 cents, add to that $10,538 and find out there is no way in the world they’re going to get $3 million in sales by spending only $18,000 in advertising expense.
Excel in its simplest environment makes regression models really easy to build using just a chart. Again, check the simple chart adding the menu trend line to the chart. But the challenge here is what if you needed the more sophisticated regression model, and I’m not going to say that this was hard by any stretch of the imagination. But now, I want to regress total sales not only against what we spent on advertising in the prior month, but also advertising two months prior. And I can’t do that through the chart approach a moment ago. To make this happen, from the Data tab of the ribbon, I’m going to go all the way out here to the far right hand side where we found Solver a few moments ago but this time I’m going to choose Data Analysis.
You recall when I talked about Solver, I said you need to check some boxes labeled
Analysis ToolPak and Analysis ToolPak DDA. If you don’t check those boxes back under Excel’s options, you are not going to see the data analysis tool pack available to you. And that’s going to be a shame because there are some fantastic functionality inside the data analysis tool pack. Including the ability of having Excel build for you a more sophisticated and more complex linear regression model. I click Data Analysis and I can choose the scroll down in this set of tools, I’ll choose the Regression Tool. Now, click Okay. And now, I need to simply indicate to Excel which of the variables are the Independent Variables and which show the Dependent Variables. Well, in this case are Y range that vertical axis that is the Dependent Variable that is going to be, of course, monthly sales. Because we are saying that sales is dependent upon advertising expense.
Our independent variable is advertising expense and notice what I’m doing is selecting both of those columns more specifically. Oh I’m sorry. Put that the one replaced. My apologies. Quickly fix this for you. Let’s go back and grab the Dependent Variable which is sales expense. That’s A2 to A31. So, what I’m saying in this case is that total sale that is as indicated in A2 to A31 is dependent upon the values in sales B2 to C31. I’m going to tell Excel that I want to put this regression analysis on a new work sheet and I click Okay and my regression is completed for me. Now, relax. We are not going to go through what every single one of these numbers mean. Time simply won’t permit that but I would quickly highlight for you the numbers that are probably most important for our discussion purposes today.
Number one, the R square value is actually higher than it was before. It’s up to 0.93 which tells me I’ve got an even better fit of the data than what we had before. It means I should have a more accurate forecasting model coming out of this Regression Analysis. Number two, how would I then use this Regression Analysis? I can now forecast total sales as being negative, not even $1,683 plus $73 and some change for every dollar we spend on advertising expense in the prior month, plus $17 and some change for every dollar we spend on advertising expense two months prior. So, I could use this more sophisticated regression model to get an even perhaps a more accurate projection of what total sales would be at any specific amount of expenditure in advertising expense.
And it gets better. Because Excel actually builds in some forecasting functions for you. And this function that I am about to show you, it’s just a simple forecast function. It’s been in Excel again, for as long as I can remember. Excel do me a favor, go build that linear regression model such that we are regressing column A against column B and then forecast for me total sales if we have advertising expense of $25,000. So, this forecasting function that we are looking at right now actually builds the regression model and does the forecast for us. And if I wanted to go in and change and say okay, what would sales be if it was under that regression scenario if we spent $30,000 in advertising expense. I just change of course the $25,000 to $30,000 and I’ve got my answer.
Now, the challenge that something like the forecast function presents to us is that the forecast function is built on a linear model assuming those seasonality in the data. Yet many of us work in environments where there is seasonality in the data. If you are in a public accounting firm, for example, a significant portion of your revenues are earned during the first four months of the year. If you are a ski resort, obviously a significant portion of your revenues are earned during the winter months. If you are a beach resort, then perhaps the significant percentage of your revenues are earned during the summer months. Many of us are in businesses where there is seasonality. And what I would point out is very quickly just a couple of other functions that you might want to take advantage of, the trend function. The trend function can be used to forecast the future based on historical results perhaps identifying seasonality in the data. And then others of other functions out here, for example the growth functions can do the same thing. Growth function is using an array formula to help forecast the future based on the past.
I won’t go into any great degree of detail on either of those two functions other than just to call them to your attention and suggest that perhaps you hit Excel’s Help system and read up on those functions and how you can work with the growth function and the trend function perhaps also in addition to the forecast function to help you do a better job of forecasting revenues or forecasting anything in the future based on historical results. Now, again, all versions of Excel have those functions, have those features. The two things that I’m about to show to you are new in Excel 2016.
First is a new function called Forecast.ETS. Forecast.ETS specifically is here to help us identify trends in the data based on the passage of time and to build our forecast based on what specific month we are looking at. So, in this example, I want to build a revenue forecast for the month of April 2016. Now, again this is only an Excel 2016 outside Forecast.ETS. The ETS stands for Exponential Triple Smoothing and that is just a way of identifying seasonality in the data. And notice that this Forecast.ETS function is now asking me very specifically for a target date. So, we’ve got to go tell it exactly what time period we want this forecast to be built for. I want the forecast to be built for the date values setting in cell A36 which as you can see is April 30th 2016. And I want this forecast to be built by looking at historical sales data, that is the data in B2 through B35, compare to the months where that data were limited differently. Compare it to the months where those results were achieved which of course cell A2 to A35. Press Enter and now I’ve got my forecast incorporating any seasonality that the Forecast.ETS function found in the data.
You would get a different number if you used the forecast function. You would likely get a different number if you used the trend function and also the growth function because all four now of these functions are using different calculation or calculating algorithms to get the result. But here is the challenge that I see with this. So, Excel is now telling me that its best guess for revenue on April of 2016 is $1.9 million. The probability of that being correct is virtually zero. Why is it we go to budget battle with numbers we know in advance are wrong? I mean I recognize there are best guess but we know in advance those numbers are almost guaranteed to be wrong. I would rather go to battle with some numbers that I had say 95 percent confidence in. And that’s what we get to do if we use Excel 2016’s new Forecast Sheet’s function.
Forecast Sheets as you’ll see momentarily gives us the opportunity of going in and building our forecast such that Excel is going to provide to us it’s best guess. But it’s also going to provide to us a 95 percent confidence interval. Again, this is in Excel 2016 only. From the Data tab of the ribbon in Excel 2016, remember, we’ve been playing around on this What If Analysis all session. This time though, we are going to Forecast Sheet. And when I choose Forecast Sheet, Excel immediately looks at the Data, it created a Graph for me where the blue portion of that graph, that’s the historical data. The red portion of the graph, that’s projected data and notice how on the projected data I’ve got an upper line, a middle line and the lower line. The middle line is the best guess line. The upper line and the lower line represent the 95 percent Confidence Intervals.
See that I’m being asked in this dialogue box how far out do I want to go? And maybe I don’t want to go all the way to December. Maybe I just want to go through, oh I don’t know, September 30th for example. And then I’m being asked for Options. And one of the Options as you can see right there is what Confidence Interval do I want? The default is 95 percent. I’ll keep it at that. I’ll ultimately just click the Create button in the lower right hand corner. And now, what Excel has done is created a brand new worksheet that shows me for the months of May through September what the best guess is and that’s currently highlighted column what the lower boundary is and what the upper boundary is. So, for example, for the month of September, Excel is telling me that it’s best guess is that our total sales revenue will be approximately $1.93 million but it’s 95 percent sure we’re going to be between $1.848 and $2.013 million. I would much rather go to budget battle with numbers that I had 95 percent confidence in as opposed to numbers that I had virtually zero confidence in.
We’ve got two quick topics to talk about in the limited amount of time we have left. The first one I intend it to be brief on. In fact, both of these I intend to be quite brief on. My
Master’s degree as you heard at the outset of today is in Finance, more specifically concentration in Corporate Finance. And all I’m going to tell you with respect to capital budgeting is simply this. Don’t use Excel’s NPV function and don’t use Excel’s IRR function. Now, the theorist in me would say never use Internal Rate of Return anyway but I recognize a lot of organizations use Internal Rates of Return to calculate, for capital budgeting scenarios. Instead of using NPV or instead of using the IRR function, let me encourage you to use a feature in Excel or a function in Excel called XNPV and XIRR. And the reason I encourage that is notice that the XNPV function as well as the XIRR function allow you to model. They allow us to model the fact that cash flows don’t always occur in nice round monthly or quarterly or annual increments.
I can go in and very specifically indicate the dates on which each of these cashflows occur. And then the XNPV function is a really simple function and so go calculate the NPV, discount the cashflows by 6 percent, more specifically discount the cashflows in cells A2 to A6 by 6 percent based on the date that are in column B. That is a far superior function because it means that we don’t run the risk of accidentally double discounting the first cashflow which is in essence what the tradition NPV function does. And if you are a fan of Internal Rate of Return instead of or in addition to NPV, then use the XIRR function instead. The XIRR function calculates your internal rate of return but it does so based on the actual passage of time, okay? Both of those are far superior to using the traditional NPV and the Internal Rate of Return functions.
The last thing that I will mention in our limited amount of time together is how to handle iterative calculations. Iterative calculations circular references right as they are also known. These are the things that pop up when for example like we have on the screen, I’ve got state income taxes deductible in the federal return and federal taxes deductible on the state return. Good luck for during that one out. Or my company is doing really, really well so I have more cash. Because I have more cash, I put more money in our money market which generates more interest income which means I have more cash which means I put more money in the money market, which means I have more interest income. There is an example of a circular reference.
Now, circular references are generally bad spreadsheet design. I would tell you, anytime you see a circular reference you should attempt to engineer it out of the spreadsheet. But in some cases such as this one where state tax is deductible on the federal return and federal taxes deductible on the state return. Hey, I didn’t write the tax laws. I can’t just engineer that out of the spreadsheet. Instead, if you cannot get rid of the circular reference, then from the File tab of the ribbons, choose Options. Out of the tab of the ribbons choose Options, choose Formulas and then choose this option that says Enable Iterative Calculations. Click Okay and that will then go to the process of solving the circular reference. And actually as I was doing that, I realized I opened the wrong workbook that demonstrates that. So, while I’m talking through this, I’m going to open a different workbook and we’ll solve the iterative calculation there....