Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Tabs let you separate data and calculations into different parts of a spreadsheet. One best practice is to use one tab for collecting "assumptions" — these are the inputs that let you test out different scenarios — and another tab for seeing the results or output of those scenarios.
Example Files
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up