Input and Output Tabs

Another best practice for formatting is to put 0:00 all the assumptions in your spreadsheet, also known as inputs, on one tab. 0:02 Assumptions are hard coded numbers that impact formulas in other cells. 0:07 If you are making a budget, it might be what your monthly income is. 0:11 Here we're looking at someone's budget. 0:15 We have what their income is, how much they spend on various expenses each month, 0:17 and then some extraordinary expenses, these are on the Input tab. 0:23 And then we have an Output tab, which shows the results 0:26 of that by month. 0:31 So, this person right now has an annual gross income of $40,000. 0:36 And let's say their boss told them they're gonna get a raise next month, so 0:42 they wanted to see how that's gonna impact their funds. 0:46 So let's go see here, in total disposable income for 0:50 the year, we have $8,630. 0:55 What happens if they get a $5,000 raise? 0:59 Their tax rate might change, but let's assume it stays flat. 1:03 Well, now they have $13,130 in disposable income. 1:09 But you can see that I'm not changing anything on this page. 1:14 Everything here is coming from assumptions that I can change here. 1:19 Another example is maybe you're forecasting your retail business revenue 1:24 and want to understand how the number of units you sell impacts your profit. 1:27 The idea is to structure your spreadsheet in a way that will allow you to maintain 1:32 it more easily. 1:36 A lot of spreadsheets where you are forecasting based on data in 1:37 the spreadsheet are set up with that in mind. 1:40 Where you have one tab with all the assumptions, known as an input tab, 1:42 that drive the other tabs, known as output tabs. 1:46 This is important because forecasting has so much uncertainty. 1:50 When you are preparing a forecast, 1:53 you'll want to have the ability to discuss the inputs that are driving your forecast. 1:55 Do these assumptions make sense? 1:59 Let's think about what could change, and see how that impacts our outputs. 2:01 This is why the organization of your spreadsheet for 2:05 forecasting purposes is crucial. 2:08 You're probably gonna have to run a bunch of scenarios. 2:10 In this example, you can see how I have an Input tab, 2:13 and then two Output tabs that are driven by assumptions in the input tab. 2:19 In this case, we have an output by month, Where you see all the different months 2:24 going forward for the different years in our forecast, and outputs by year. 2:30 Let's look at some examples where changing the assumptions impact the outputs. 2:36 Let's start off by looking at how average sales prices can impact our outputs. 2:40 So I'm gonna go over here to the output by year tab and look at Year 3. 2:45 $237,000 in revenue is what we're forecasting for Year 3. 2:51 I'm gonna go back to the input tab, and change the assumption for 2:57 belts, average price per unit, from $100 to $200. 3:03 I'm going to copy that, and paste it across all the different years. 3:08 Then I'm gonna go back to the Output/Year and see, wow, sales have increased a lot. 3:13 So let's just do that over again. 3:19 We've got $100 as the assumption, and 3:21 we see $237,000. 3:25 And we change it back to $200, and we got $360,000. 3:28 So if we're able to successfully increase the price of the belts that we sell, 3:36 our revenue is gonna drastically increase. 3:39 Let's look at the expense side. 3:44 Earlier, I went back and forth between the OutputYear tab and 3:47 Input tab to see the impact of the sensitivity. 3:51 Another little thing you can do to save yourself some time, if you like, 3:54 is you can make a cell on your inputs tab equal to something on the outputs tab. 3:59 Let's put it equal to our Year 3 net income in this example, 4:06 which is about $12,500. 4:11 Let's see what happens if we're able to successfully reduce our rent, so 4:14 we'll start off at $500. 4:18 No impact on Year 3 because I changed the Year 1 assumption. 4:22 $600, cuz of some sort of increases, usually mandatory in lease agreements. 4:24 And look at that, if we're able to reduce our rent expense, 4:32 then, unsurprisingly, our net income in Year 3 goes up significantly. 4:36 So if you're looking for ways to raise profit, 4:40 you might advise your boss to seek out a different facility to operate in. 4:43 I'm gonna change these back to the original assumptions, which were $1,000, 4:49 $1,100 and $1,200. 4:55 Again, this way of structuring things is helpful, 4:59 because you know where all the assumptions are that can impact your calculations. 5:01 You don't have to remember to go to several different places in a spreadsheet 5:05 when you're making updates. 5:09 Also, if you get promoted and hand your spreadsheet off to someone else, following 5:11 these organizational best practices will make the transition much smoother. 5:15 All you have to do is focus on making updates to one tab. 5:19