1 00:00:00,050 --> 00:00:02,960 Another best practice for formatting is to put 2 00:00:02,960 --> 00:00:07,620 all the assumptions in your spreadsheet, also known as inputs, on one tab. 3 00:00:07,620 --> 00:00:11,560 Assumptions are hard coded numbers that impact formulas in other cells. 4 00:00:11,560 --> 00:00:15,080 If you are making a budget, it might be what your monthly income is. 5 00:00:15,080 --> 00:00:17,650 Here we're looking at someone's budget. 6 00:00:17,650 --> 00:00:23,820 We have what their income is, how much they spend on various expenses each month, 7 00:00:23,820 --> 00:00:26,960 and then some extraordinary expenses, these are on the Input tab. 8 00:00:26,960 --> 00:00:31,910 And then we have an Output tab, which shows the results 9 00:00:31,910 --> 00:00:36,570 of that by month. 10 00:00:36,570 --> 00:00:42,340 So, this person right now has an annual gross income of \$40,000. 11 00:00:42,340 --> 00:00:46,780 And let's say their boss told them they're gonna get a raise next month, so 12 00:00:46,780 --> 00:00:50,480 they wanted to see how that's gonna impact their funds. 13 00:00:50,480 --> 00:00:55,880 So let's go see here, in total disposable income for 14 00:00:55,880 --> 00:00:59,120 the year, we have \$8,630. 15 00:00:59,120 --> 00:01:03,929 What happens if they get a \$5,000 raise? 16 00:01:03,929 --> 00:01:07,479 Their tax rate might change, but let's assume it stays flat. 17 00:01:09,633 --> 00:01:14,526 Well, now they have \$13,130 in disposable income. 18 00:01:14,526 --> 00:01:19,810 But you can see that I'm not changing anything on this page. 19 00:01:19,810 --> 00:01:23,440 Everything here is coming from assumptions that I can change here. 20 00:01:24,820 --> 00:01:27,980 Another example is maybe you're forecasting your retail business revenue 21 00:01:27,980 --> 00:01:32,310 and want to understand how the number of units you sell impacts your profit. 22 00:01:32,310 --> 00:01:36,100 The idea is to structure your spreadsheet in a way that will allow you to maintain 23 00:01:36,100 --> 00:01:37,510 it more easily. 24 00:01:37,510 --> 00:01:40,572 A lot of spreadsheets where you are forecasting based on data in 25 00:01:40,572 --> 00:01:42,781 the spreadsheet are set up with that in mind. 26 00:01:42,781 --> 00:01:46,844 Where you have one tab with all the assumptions, known as an input tab, 27 00:01:46,844 --> 00:01:50,070 that drive the other tabs, known as output tabs. 28 00:01:50,070 --> 00:01:53,890 This is important because forecasting has so much uncertainty. 29 00:01:53,890 --> 00:01:55,640 When you are preparing a forecast, 30 00:01:55,640 --> 00:01:59,620 you'll want to have the ability to discuss the inputs that are driving your forecast. 31 00:01:59,620 --> 00:02:01,570 Do these assumptions make sense? 32 00:02:01,570 --> 00:02:05,130 Let's think about what could change, and see how that impacts our outputs. 33 00:02:05,130 --> 00:02:08,150 This is why the organization of your spreadsheet for 34 00:02:08,150 --> 00:02:10,710 forecasting purposes is crucial. 35 00:02:10,710 --> 00:02:13,860 You're probably gonna have to run a bunch of scenarios. 36 00:02:13,860 --> 00:02:17,710 In this example, you can see how I have an Input tab, 37 00:02:19,420 --> 00:02:23,340 and then two Output tabs that are driven by assumptions in the input tab. 38 00:02:24,350 --> 00:02:30,986 In this case, we have an output by month, Where you see all the different months 39 00:02:30,986 --> 00:02:35,260 going forward for the different years in our forecast, and outputs by year. 40 00:02:36,810 --> 00:02:40,400 Let's look at some examples where changing the assumptions impact the outputs. 41 00:02:40,400 --> 00:02:45,910 Let's start off by looking at how average sales prices can impact our outputs. 42 00:02:45,910 --> 00:02:51,448 So I'm gonna go over here to the output by year tab and look at Year 3. 43 00:02:51,448 --> 00:02:57,991 \$237,000 in revenue is what we're forecasting for Year 3. 44 00:02:57,991 --> 00:03:03,257 I'm gonna go back to the input tab, and change the assumption for 45 00:03:03,257 --> 00:03:08,265 belts, average price per unit, from \$100 to \$200. 46 00:03:08,265 --> 00:03:13,237 I'm going to copy that, and paste it across all the different years. 47 00:03:13,237 --> 00:03:19,590 Then I'm gonna go back to the Output/Year and see, wow, sales have increased a lot. 48 00:03:19,590 --> 00:03:21,198 So let's just do that over again. 49 00:03:21,198 --> 00:03:25,963 We've got \$100 as the assumption, and 50 00:03:25,963 --> 00:03:28,932 we see \$237,000. 51 00:03:28,932 --> 00:03:36,089 And we change it back to \$200, and we got \$360,000. 52 00:03:36,089 --> 00:03:39,866 So if we're able to successfully increase the price of the belts that we sell, 53 00:03:39,866 --> 00:03:42,053 our revenue is gonna drastically increase. 54 00:03:44,333 --> 00:03:45,660 Let's look at the expense side. 55 00:03:47,850 --> 00:03:51,520 Earlier, I went back and forth between the OutputYear tab and 56 00:03:51,520 --> 00:03:54,420 Input tab to see the impact of the sensitivity. 57 00:03:54,420 --> 00:03:59,730 Another little thing you can do to save yourself some time, if you like, 58 00:03:59,730 --> 00:04:04,140 is you can make a cell on your inputs tab equal to something on the outputs tab. 59 00:04:06,250 --> 00:04:11,060 Let's put it equal to our Year 3 net income in this example, 60 00:04:11,060 --> 00:04:14,640 which is about \$12,500. 61 00:04:14,640 --> 00:04:18,520 Let's see what happens if we're able to successfully reduce our rent, so 62 00:04:18,520 --> 00:04:22,090 we'll start off at \$500. 63 00:04:22,090 --> 00:04:24,835 No impact on Year 3 because I changed the Year 1 assumption. 64 00:04:24,835 --> 00:04:30,650 \$600, cuz of some sort of increases, usually mandatory in lease agreements. 65 00:04:32,440 --> 00:04:36,245 And look at that, if we're able to reduce our rent expense, 66 00:04:36,245 --> 00:04:40,975 then, unsurprisingly, our net income in Year 3 goes up significantly. 67 00:04:40,975 --> 00:04:43,395 So if you're looking for ways to raise profit, 68 00:04:43,395 --> 00:04:48,115 you might advise your boss to seek out a different facility to operate in. 69 00:04:49,125 --> 00:04:53,647 I'm gonna change these back to the original assumptions, which were \$1,000, 70 00:04:55,715 --> 00:04:59,062 \$1,100 and \$1,200. 71 00:04:59,062 --> 00:05:01,680 Again, this way of structuring things is helpful, 72 00:05:01,680 --> 00:05:05,990 because you know where all the assumptions are that can impact your calculations. 73 00:05:05,990 --> 00:05:09,640 You don't have to remember to go to several different places in a spreadsheet 74 00:05:09,640 --> 00:05:11,130 when you're making updates. 75 00:05:11,130 --> 00:05:15,020 Also, if you get promoted and hand your spreadsheet off to someone else, following 76 00:05:15,020 --> 00:05:19,310 these organizational best practices will make the transition much smoother. 77 00:05:19,310 --> 00:05:22,410 All you have to do is focus on making updates to one tab.