IntroductionWelcome| 00:04 | Hi, I am Curt Frye.
| | 00:06 | Welcome to Excel 2007:
Creating Business Budgets.
| | 00:10 | In this course, I'll show you how to create
and analyze budgets using Microsoft Excel.
| | 00:14 | I'll begin by showing you how to
track your cash flow using an Excel table.
| | 00:18 | Then I'll demonstrate how to
calculate payments for a fully amortized loan.
| | 00:23 | I'll also show you how to determine the
interest component of a loan payment and
| | 00:26 | to calculate the maximum amount you may
borrow, given a desired monthly payment.
| | 00:30 | We'll enhance your ability to analyze
your budgets by showing you how to read
| | 00:34 | financial statements,
| | 00:36 | design worksheets to assist managerial
decision-making, and summarize financial
| | 00:41 | information using charts.
| | 00:43 | I'll show you how to create a summary
budget worksheet, analyze budget data by
| | 00:47 | creating a PivotTable, create
projected budgets and build scenarios to analyze
| | 00:53 | potential outcomes that
could affect your budgets.
| | 00:55 | In short, I'll show you how to
manage your businesses budgets using Excel
| | 00:59 | financial capabilities.
| | 01:01 | Let's get started with Excel 2007:
Creating Business Budgets.
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you have access to the exercise
files for this course, you can put them on
| | 00:04 | your desktop as I have,
or anywhere else you want.
| | 00:07 | The top folder contains subfolders
for each chapter, which contain the
| | 00:12 | exercise files themselves.
| | 00:14 | If you do not have access to these
files, you can follow along with your own
| | 00:17 | files as we proceed through the course.
| | Collapse this transcript |
|
|
1. Managing Cash on HandCreating an Excel table to track cash on hand| 00:00 | One of the most basic budgetary tasks
that you can undertake when you run a
| | 00:04 | business is to track your cash on hand.
| | 00:06 | For better or worse, that's most of the
budgetary analysis some small business do.
| | 00:11 | However, Excel gives you the tools you
can use to analyze your Cash on Hand much
| | 00:15 | more effectively than in previous versions.
| | 00:17 | In Excel 2003, Microsoft introduced the
list, which enabled you to manage tables
| | 00:21 | of data more efficiently.
| | 00:23 | In Excel 2007, these list objects
became Excel tables, which are much more
| | 00:28 | useful when performing
budgetary analysis in Excel.
| | 00:31 | First, I'll show you how to create an
Excel table based on an existing data list.
| | 00:35 | To create a table, first you layout
your data in list form and it helps if you
| | 00:42 | have headings on each column that are
formatted differently from the rest of the data.
| | 00:48 | So for example, here I have a Date,
Income, Expense, and Balance and they're all
| | 00:53 | centered and displayed in bold.
| | 00:55 | That way Excel can distinguish those
headers from the remainder of the list.
| | 01:00 | Also there's no extraneous data around the list.
| | 01:03 | In other words, this column, this row,
this column, and the row above the table or
| | 01:10 | the soon-to-be table are all blank.
| | 01:12 | That way no extra data will be included.
| | 01:16 | To create the table, I click any cell in
the list and then on the Home tab click
| | 01:21 | Format As Table and select my format of choice.
| | 01:25 | I'll use this one. In the,
Format As Table dialog box.
| | 01:30 | I verify that Excel has
identified the data correctly.
| | 01:34 | My table does have headers, that is
these values here, and click OK, and
| | 01:40 | Excel creates my table.
| | 01:42 | This table contains income and expense data,
taken at increments throughout the year.
| | 01:48 | I have my starting balance, which I
typed in just as a regular number and then
| | 01:54 | the next formula takes the value
above it, E4 in this case is the starting
| | 01:59 | balance, and then it adds the value in
the Income column, subtracts the value
| | 02:06 | here in D5 from that and then puts
the total in cell E5 and the formula
| | 02:13 | continues down the table, E5, E6 and so on.
| | 02:17 | If I want to add a new row to a table,
I can click the final cell in the final
| | 02:24 | row, press Tab, and add to it.
| | 02:27 | So now let's say that I had an expense
of $10,000 and let me see the next date
| | 02:33 | would be 10/1/2010 and my expense is
10,000. When I add that in, the Excel table
| | 02:42 | updates the data so that
this formula is now $97,742.
| | 02:45 | Now let's say that I wanted to
analyze my average daily balance.
| | 02:52 | That's often useful to see how
your cash flow is progressing.
| | 02:55 | If I wanted to find the average daily
balance, I would need to find the average
| | 02:59 | of this column here, the Balance column.
| | 03:01 | To do that I can add a total row.
To add the total row, I click any cell on the
| | 03:07 | table and then on the Design Contextual
Tab select Total Row. When I do, Excel
| | 03:13 | adds the total row, although
in this case the value is a sum.
| | 03:18 | It is all of these values added together.
| | 03:22 | That's not meaningful information, so
I'm going to change that value to an Average.
| | 03:27 | Click the cell and select Average
from the list of functions that appears.
| | 03:31 | When I do, I find that my average
daily balance is just over $91,000.
| | 03:37 | Excel tables help you summarize your
data effectively and provide a base for
| | 03:40 | more advanced analysis using PivotTables,
which we will get into later in this course.
| | Collapse this transcript |
| Tracking income and expenses by category and contract| 00:00 | Tracking your past and expected future
cash flows using an Excel table is the
| | 00:04 | most basic form of
budgetary analysis you can perform.
| | 00:07 | The next step up is to add information
about each income or expense item and
| | 00:12 | filter the table based on that information.
| | 00:14 | The Excel table here contains columns
for the date of the item, the amount of
| | 00:18 | income or expense for that particular item
and the category in which that item occurs.
| | 00:24 | So I have Maintenance, Consulting,
Bonuses, Construction Cost and so on.
| | 00:28 | Categories are very important and it's
important that you remain consistent.
| | 00:32 | So for example, for Maintenance,
which I would consider to be building
| | 00:35 | maintenance, it's important that you
distinguish that from something like, say,
| | 00:38 | a software maintenance contract.
| | 00:40 | Once you have your data in an Excel table,
you can filter it to display just the
| | 00:44 | categories you want to appear.
| | 00:46 | So let's say, for example, that I only
wanted to see the amount of money that I
| | 00:49 | made through my Consulting projects.
| | 00:51 | To do that I would create the filter
by clicking the Filter arrow on the
| | 00:55 | Category column, clearing Select All,
and then clicking Consulting. Then I'll
| | 01:02 | click OK and Excel only lists the items
under the Consulting category and in this
| | 01:07 | case they're all incomes.
| | 01:09 | If I want to find the summary,
either as a total of all of my Income as a
| | 01:14 | consultant or to find the average that
I make per project on the consultant,I
| | 01:19 | I can add a total row.
| | 01:20 | To do that, I click any cell in the
table then on the Design Contextual tab
| | 01:26 | check the Total Row box.
| | 01:28 | When I do, Excel displays the Total row
and it has a summary calculation here.
| | 01:33 | Now the balance, if you go back to my
Excel table I'll remove the filter very
| | 01:38 | quickly, this is a running balance.
So I started with $145,000 in the bank and
| | 01:43 | then I had an expensive 18,000, so
that's subtracted from there, added 14 back,
| | 01:48 | went back up to 141 and so on.
| | 01:50 | If I reapply the filter, and I was
looking at Consulting, then this number is
| | 01:58 | the sum of these four entries here.
But that doesn't make any sense because
| | 02:03 | this is a daily balance.
| | 02:04 | Instead what I want to do is perform
a summary calculation here underneath
| | 02:10 | the income elements.
| | 02:11 | So let's say that I wanted to just find
the sum. I'll click the cell underneath
| | 02:16 | the column of data that I want,
click the down arrow, and just select the
| | 02:20 | mathematical operation that I want to use.
| | 02:22 | In this case I'll just do Sum and I'll
widen out the column so we can see it,
| | 02:28 | and you see that from
consulting, I've made $101,250.
| | 02:32 | Now let's say that I want to limit
this even further, perhaps say to dates
| | 02:36 | within the first quarter of the year.
| | 02:38 | To do that, I can click the Filter
arrow, point to Date filters, point to All
| | 02:43 | Dates in the Period, and
then click Quarter number 1.
| | 02:48 | When I do, I see the values for
January and February, but the April and July
| | 02:54 | values have now been hidden.
| | 02:56 | If I remove the filter by clicking
here and clicking Clear, they come back.
| | 03:00 | Every extra piece of data you use to
describe your Income and Expenses expands
| | 03:04 | the number and types of
analysis you can perform.
| | 03:07 | Add as much information as you can to
your Tracking table and remain consistent
| | 03:10 | in your classifications.
| | Collapse this transcript |
| Summarizing cash on hand in a chart| 00:00 | When you store your income and expense
data in an Excel table, you can create a
| | 00:04 | chart to summarize how your
company's cash on hand has trended over time.
| | 00:08 | This Excel table contains columns for
the date of the item, whether each item is
| | 00:13 | an income or expense, the
category of the item, and the amount.
| | 00:18 | The column that we're interested in in
this case is our cash on hand for a cash
| | 00:21 | flowchart and those values
occur here in the Balance column.
| | 00:26 | To create a chart based on this data,
you click any cell in the table and then
| | 00:30 | on the Insert tab, you click the
type of chart you want to create.
| | 00:34 | In this case I'll create a Line Chart.
| | 00:36 | trending over time.
| | 00:37 | Just create a basic chart
with no markers or anything.
| | 00:40 | When I do, Excel creates
an exceptionally ugly chart.
| | 00:43 | In other words, you have each of these
dates and it's trying to organize things by
| | 00:47 | starting balance and so on.
| | 00:48 | So you need to do a little bit
of manipulation with the data.
| | 00:52 | To do that, you click the Select Data button.
| | 00:55 | That's on the Design contextual tab,
and now you can edit the chart series.
| | 01:00 | In this case I need to get rid of these
values here on the horizontal axis and
| | 01:06 | that is the set of values
that create this mess here.
| | 01:11 | Instead, all I want is the Date.
| | 01:13 | To do that I click Edit and then
I can define the Axis label range.
| | 01:18 | In other words, the range of cells
from which Excel will take the values to
| | 01:21 | display on the range.
| | 01:22 | Those values occur here in the Date
column and they run from B4 to B24, so I
| | 01:30 | will edit the cell reference so it
refers to the cells on Sheet1, active sheet,
| | 01:37 | cell B4 at the top, to B24,
which is at the bottom.
| | 01:42 | When I click OK, Excel updates the
values that are shown on the horizontal axis.
| | 01:48 | And when I click OK, you see that my
chart provides useful information without
| | 01:52 | all this extraneous
information being crowded in.
| | 01:55 | It's easy for humans to look at a few
data points and discern patterns within
| | 01:58 | them, but that task becomes more
difficult as the number of data points grows.
| | 02:03 | Creating a Chart illustrates trends
visually, enabling you to comprehend at a
| | 02:07 | glance patterns that might have escaped
your notice, if all you had to look at
| | 02:09 | was the wrong numbers.
| | Collapse this transcript |
|
|
2. Calculating Loan RepaymentsCalculating payments for a fully amortized loan| 00:00 | Most loans, whether between
businesses or made by businesses to individual
| | 00:04 | borrowers, are fully amortized.
| | 00:07 | Fully amortized means that the monthly
payments made over the term of the loan
| | 00:10 | pay off the principal and all accrued interest.
| | 00:13 | You can calculate the monthly payments
required to pay off a fully amortized
| | 00:16 | loan using the PMT function.
| | 00:19 | PMT is short for payment.
| | 00:21 | The PMT function has five arguments:
rate, number of periods, present value,
| | 00:27 | future value and a type.
| | 00:29 | The first three arguments, rate, numbers
of periods and present value are required.
| | 00:35 | I'll type in PMT and left
parentheses to show you the arguments in the
| | 00:40 | order that they occur.
| | 00:42 | Rate is the annual percentage rate
divided by the number of payments made each year,
| | 00:45 | usually 12 representing the 12 months.
| | 00:48 | So, if I were to type in the rate or
in this case the address of the cell
| | 00:53 | that contains the rate, I would type in B5
divided by 12. Again this is monthly payments.
| | 01:00 | So there are 12 months per year.
| | 01:02 | I have to divide the rate by 12
to turn it into a monthly rate.
| | 01:06 | Number of periods or nper is the
number of payments you'll make on the loan.
| | 01:11 | This is a 30 year term, which means
that we'll have 12 payments a year.
| | 01:16 | That means we'll have 360 payments.
| | 01:20 | Finally, for the required
arguments present value is the Principal.
| | 01:24 | It's the amount that you borrow
and that number is here in cell B3.
| | 01:30 | I added that cell to this formula by
clicking here in the formula so that the
| | 01:34 | cursor is flashing and the correct
argument was highlighted here in the tooltip,
| | 01:39 | and I click the cell.
| | 01:41 | When I did, Excel added that
cell's address to the formula.
| | 01:46 | The other two arguments indicated
here in square brackets are optional.
| | 01:50 | fv is the future value of the loan.
| | 01:53 | Future value means that you will either
have some unpaid principal at the end,
| | 01:57 | such as having a balloon payment at the
end of your mortgage or that you will be
| | 02:01 | paying more than you owe and you
want to have a positive future value.
| | 02:05 | Those two circumstances rarely occur,
so you usually are able to leave
| | 02:09 | the future value out.
| | 02:11 | The final argument, type, is zero if
your payment is due at the end of a
| | 02:15 | period that is you're paying in
arrears or one if you're paying at the
| | 02:19 | beginning of a period in advance.
| | 02:21 | Most loans are paid in arrears and result in
a zero balance at the end of the loan's term.
| | 02:26 | So you usually leave the fv and
type arguments out of the formula.
| | 02:30 | When you're done you can type a
right parenthesis, hit Enter, and Excel
| | 02:35 | displays the payment.
| | 02:37 | Now notice that the value displayed
here is negative. In other words you
| | 02:41 | will have a negative cash flow of $61,697
every month for the 30-year term of this loan.
| | 02:49 | You can always multiply the results of
the formula by negative one if you want
| | 02:53 | to see the payment
displayed as a positive value.
| | 02:56 | And finally one last note on setting up
your worksheets so that it's easier to
| | 03:00 | understand where the data is coming from.
| | 03:02 | I always put my inputs, the principal,
the rate and the term, in a separate
| | 03:08 | clearly identified part of the worksheet.
| | 03:11 | In this case I put a bottom border
along these two cells indicating that these
| | 03:16 | are the inputs and they
are separate from the result.
| | 03:19 | The PMT function only calculates the
monthly payment to cover principal and interest.
| | 03:24 | Many loans have other charges included,
such as property taxes and perhaps a
| | 03:28 | Private Mortgage Insurance for home loans.
| | 03:30 | So be sure to include those other
fees and charges when you calculate your loan payments.
| | Collapse this transcript |
| Calculating interest and principal components of a loan payment| 00:00 | In the previous movie I showed you
how to calculate the amount of a monthly
| | 00:04 | payment on a fully amortized loan.
| | 00:06 | In this movie I'll show you how to
calculate the amount of principal and the
| | 00:09 | amount of interest in each loan payment.
| | 00:12 | Calculating the amount of principal
you've paid on the loan enables you to
| | 00:15 | determine the amount of equity you
have in a purchase, which is your down
| | 00:18 | payment plus any loan principal paid,
plus appreciation of the property if any.
| | 00:23 | Also what your new monthly payment
would be if you refinance the loan at a
| | 00:27 | specific interest rate
for a given length of time.
| | 00:30 | Calculating the interest paid on a
loan enables you to find the amount of
| | 00:34 | Interest you paid in a fiscal year,
which you can often write off on your taxes.
| | 00:37 | To calculate the interest component
of a payment you use the IPMT function.
| | 00:43 | The IPMT function enables you to
determine the amount of interest paid with a
| | 00:47 | specific loan payment.
| | 00:48 | In other words, for the loan payment
number 1, on this loan, I can calculate the
| | 00:54 | interest component by typing
=IPMT and left parenthesis.
| | 01:00 | The IPMT function has four required
arguments and two optional arguments.
| | 01:05 | The first argument is rate and that
is the percentage rate on the loan.
| | 01:09 | In this case it's 4.50% and it is
expressed as an annual percentage rate.
| | 01:15 | I'll type that rates' cell reference, cell
B5, in this cell and then I will press F4.
| | 01:23 | Pressing F4 turns the cell reference
from a relative reference, which can change,
| | 01:28 | to an absolute reference, which cannot change.
| | 01:30 | That is what the dollar sign in front of
the column and in front of the row mean.
| | 01:35 | Now because I'm doing monthly
payments, I want to turn this into a
| | 01:39 | monthly interest rate.
| | 01:40 | I divide that number by 12.
| | 01:43 | The next number is the period, in other
words which payment within the year or
| | 01:47 | within the term that I'm looking at,
which is in this case a year, which payment
| | 01:52 | is that in that sequence.
| | 01:53 | That number appears in cell A12, payment
number 1, and then A13 payment number 2 and so on.
| | 02:00 | To indicate that within the formula
I type in A12, but I do not make it
| | 02:05 | an absolute reference.
| | 02:06 | If I turned A12 into an absolute
reference by pressing F4, then that value
| | 02:11 | wouldn't change and Excel would
continue calculating the same value
| | 02:15 | over-and-over because it would look
at cell A12 instead of A13 for payment
| | 02:18 | number 2, A14 for payment number 3 and so on.
| | 02:23 | The next argument is the number of periods.
| | 02:25 | In this case it's monthly so I
have the term of 30 years in cell B7.
| | 02:33 | I don't want that to change so I will
press F4 and because I have 12 payments a year,
| | 02:39 | I will multiply
that value by 12 to get 360.
| | 02:44 | The last required argument is the
present value and that's simply the loan
| | 02:48 | principal, the amount you borrowed.
| | 02:50 | That occurs in cell B3, so I'll type B3
and F4 to make it an absolute reference
| | 02:55 | so it doesn't change.
| | 02:57 | The final two arguments are optional
and you usually won't go with them.
| | 03:01 | The first of those is fv . That is Future Value.
| | 03:05 | The future value argument is used to
indicate whether you have some money left
| | 03:08 | over at the end, in other words a
balloon payment, and the type argument
| | 03:12 | indicates whether you make your
payment at the end of a period.
| | 03:15 | That is in arrears or at the
beginning of a period, in advance.
| | 03:20 | If you make your payment in advance you
enter the number one. If you make your
| | 03:24 | payment in arrears, which is the default,
and pretty much every amortized loan
| | 03:30 | allows interest to accumulate for a
month before you make your first payment,
| | 03:34 | you would enter 0 or you can leave it
blank because it's the default choice.
| | 03:38 | I'm done here so I can type a right
parenthesis, hit Enter, and Excel calculates
| | 03:44 | the interest component.
| | 03:46 | Now for the principal, it's almost exactly
the same thing except I use the =PPMT function.
| | 03:53 | So I have =PPMT, which is Principal
Payment, and I enter the same arguments.
| | 04:01 | I have the rate, which is in cell
B5. I don't want that to change.
| | 04:05 | So I press F4, divided by 12, because it's a
monthly rate. I'm making 12 payments per year.
| | 04:12 | The period is in cell A12.
| | 04:15 | Again don't change it to an absolute
reference so that that element can vary as
| | 04:20 | it's copied down the Excel table.
| | 04:22 | The number of periods in the
loan is B7, again 30 years.
| | 04:27 | Make it into an absolute reference
pressing F4 multiplied by 12, because we have
| | 04:33 | 12 payments per year.
| | 04:34 | Then finally we have the present value
in cell B3, $500,000, F4 so it doesn't
| | 04:40 | change, right parenthesis, look it over
one last time and hit Enter and there I
| | 04:45 | have both the interest and the
principal components of the payment.
| | 04:49 | So that's how you determine the
interest and principal components of
| | 04:52 | individual payments.
| | 04:54 | You can also determine the cumulative
interest and principal you've paid on a
| | 04:57 | loan by using the
CUMIPMT and CUMPRINC functions.
| | 05:03 | These two functions require you to
enter the periods for which you want to
| | 05:06 | calculate interest and principal paid.
| | 05:08 | For example, if you barred on the
same terms and wanted to determine how
| | 05:12 | much interest and principal you'll pay over
the first year, you can create these formulas.
| | 05:16 | First we'll do the interest.
| | 05:18 | So it's =CUMIPMT, Cumulative Interest
Payment between two periods, the rate is
| | 05:27 | in B5, absolute reference divided by 12,
the number of periods in the loan is
| | 05:33 | in cell B7, that's 30 years, absolute
reference multiplied by 12, because we're
| | 05:39 | making 12 payments per year. I have
the present value which is in B3, again
| | 05:45 | absolute reference, the start period,
that's the first payment in the loan, so
| | 05:49 | it's period number 1, the end period is 12,
the 12th payment on the loan, and then the type.
| | 05:57 | In this function type is required.
| | 05:59 | We'll assume that you're paying in
arrears, in other words that the payment is
| | 06:03 | due at the end of the month.
| | 06:04 | So we'll type 0, right parenthesis, and
we can see that at the end of the first
| | 06:11 | year you will pay $22,335
minus one cent in interest.
| | 06:17 | Here's the same thing for principal.
=CUMPRINC for Principal, B5 which holds the
| | 06:26 | rate divided by 12 to make it monthly,
number of periods in B7, that's the
| | 06:30 | number of years, multiplied by 12
because it's monthly. The present value is in
| | 06:39 | cell B3, the start period is 1, first
payment on the loan, 12 is the 12 monthly
| | 06:45 | payment, that's at the end of the
first year and the type of the loan, you're
| | 06:50 | paying in arrears so that's 0.
| | 06:53 | Type right parenthesis, hit Tab,
after I press Tab I can format this number
| | 07:00 | using the Accounting format and you see
that in the first year I paid a little
| | 07:04 | bit over $8,000 in Principal.
| | 07:07 | Calculating the amount of principal
and interest you have or will pay off on
| | 07:10 | your loans reflects both the equity you
have in your purchases and the amount of
| | 07:14 | money you can write off in your taxes.
| | 07:16 | Be sure to watch your interest expenses closely.
| | Collapse this transcript |
| Determining the maximum amount that can be borrowed using Goal Seek| 00:00 | When you put together a budget for your
business, you must pay careful attention
| | 00:03 | to the amount of cash you have on hand.
| | 00:06 | Many businesses choose to capitalize
their operations by borrowing money and
| | 00:10 | making monthly payments over a 2-5 year period.
| | 00:12 | Once you've figure out how much of a
payment you can afford per month, then you
| | 00:15 | can determine how much you can borrow.
| | 00:18 | You can use the payment or PMT
function to determine the monthly payment
| | 00:22 | required to pay off a loan, given an
interest rate, term of loan, and
| | 00:26 | the amount borrowed.
| | 00:27 | So for example, here I have the PMT
function and it has principal, the interest
| | 00:33 | rate and the term of the loan,
in this case expressed in years.
| | 00:38 | The first argument is the interest
rate here in the B5, 9%, but because we'll
| | 00:44 | be making monthly payments, we need
to describe that as a monthly interest
| | 00:48 | rate, so I divided it by 12.
| | 00:50 | Next we have the term of the loan.
| | 00:52 | Again, as I said that's in B7 and it's
three years, but because we were making
| | 00:56 | monthly payments, we need to
multiply that value by 12. So we'll get 36.
| | 01:01 | And finally, we have the present value
of the loan, which is the amount borrowed,
| | 01:06 | and that's here in cell B3, $500,000.
| | 01:09 | Now my goal is to determine how much I
can borrow at an interest rate of 9% over
| | 01:15 | three years, in other words with 36
monthly payments, and bringing this monthly
| | 01:19 | payment here to my budgeted amount of $14,000.
| | 01:24 | So to do that I will go to
the Data tab and open Goal Seek.
| | 01:30 | In Goal Seek I need to indicate which
cell I want to set and that is cell B9,
| | 01:35 | the one that's highlighted.
| | 01:37 | So the value, my target value
has to be in negative number.
| | 01:41 | Now note that the value in B9 is
expressed as a negative number.
| | 01:44 | That's because you'll be paying
that amount each month as an expense.
| | 01:48 | It's a cash outflow from you, so
you express it as a negative number.
| | 01:53 | As I said before, I want my monthly
payment to be $14,000. So I type it in
| | 01:58 | as -14,000, once again an outflow,
and finally we are going to change the
| | 02:06 | amount that we borrowed, currently
$500,000 in cell B3 to generate that
| | 02:11 | payment per month of $14,000.
| | 02:14 | So we'll do that by changing cell B3.
| | 02:16 | Everything looks right. I'll click
OK and Goal Seek has found an answer.
| | 02:22 | So for a payment of $14,000 over three
years at a rate of 9%, we'll be able to
| | 02:26 | borrow just over $440,000.
| | 02:30 | I can either keep the value by clicking OK,
or I can get rid of it by clicking Cancel.
| | 02:37 | When you want to discover which
inputs will cause a formula to return to
| | 02:39 | a target value, you can save a lot of
time in guessing by using Goal Seek to
| | 02:43 | find the value you want.
| | Collapse this transcript |
|
|
3. Budgeting Using Financial StatementsIntroducing the balance sheet| 00:00 | Companies create financial
statements to provide information to potential
| | 00:03 | investors and creditors, helping
current investors evaluate the health of the
| | 00:07 | performance of the company and
summarize the company's resources.
| | 00:11 | One of those documents is the balance sheet.
| | 00:13 | And in addition to using the balance
sheet as information for investors, you can
| | 00:18 | also use it to help you create your budgets.
| | 00:20 | In other words, you can do projections
into the future based on the amounts that
| | 00:24 | you would like to see next year or
anticipate seeing in each of these entries
| | 00:29 | based on the projected
performance for the next year.
| | 00:32 | There are several financial documents
that you can use, but a balance sheet
| | 00:36 | summarizes the company's assets and liabilities.
| | 00:39 | Current Assets are cash or items that
could be converted to cash within a year
| | 00:43 | and Current Liabilities are
liabilities that must be paid within a year.
| | 00:47 | So in this case we have two current
assets just grouped here under Operating
| | 00:52 | Assets and those are Cash
and Accounts Receivable.
| | 00:54 | In other words, this is the amount of
money you have in the bank and this is
| | 00:58 | the amount of money that you anticipate
receiving and putting into the bank in the near term.
| | 01:03 | A balance sheet typically shows the
current year and the previous year.
| | 01:08 | You can use that information to see how
your company's performance is trending
| | 01:11 | between those two years.
| | 01:13 | If you go back to previous balance
statements, then you'll be able to extend
| | 01:16 | that trend back over several years
and use that in your budgetary analysis.
| | 01:21 | So in the previous year, we've had the
amount of cash on hand dropped by about
| | 01:25 | $70,000, and you can almost immediately
see that one possible reason for that is
| | 01:31 | because your Accounts Receivable has
gone up by about triple that amount.
| | 01:36 | In other words, for whatever reason
based on your customer's ability to pay,
| | 01:41 | their timeliness, and the timing of your
sales, the amount that you are owed has
| | 01:45 | gone up substantially more than it
did at the end of the last fiscal year.
| | 01:48 | Then you go into your other assets
which include your Inventory, Prepaid Expenses,
| | 01:53 | those can be prepaid
legal fees, they can be prepaid rent or
| | 01:56 | mortgage payments and so on,
| | 01:58 | and then finally, you have
Property and Equipment and Depreciation.
| | 02:02 | Depreciation is the decrease in the
economic value of whatever assets you own,
| | 02:07 | and Property and Equipment are just
what you own, computers, desks, office
| | 02:12 | equipment, that sort of thing.
| | 02:13 | Then you get to the Operating Liabilities.
| | 02:15 | First, you've the Accounts Payable.
| | 02:17 | That's the amount that you owe.
| | 02:18 | And in this case your company has been
good about paying its bills and you owe
| | 02:22 | $33,000 less than you did in the previous year.
| | 02:25 | Accrued Compensation is your payroll expense
and that's stayed steady between the years.
| | 02:30 | Nobody got hired or
fired and nobody got a raise.
| | 02:32 | Then you've your Interest Expense.
| | 02:34 | That's based on the amount that you've
borrowed, and you'll notice that in this
| | 02:37 | case that has gone down by
about half, from 70,000 to 36,000.
| | 02:42 | And finally, you have the Income Taxes
owed, and previous year it was 18,000,
| | 02:47 | this year is 11,000.
| | 02:49 | And then you can total
up your Total Liabilities.
| | 02:51 | In this year, your liabilities are
substantially lower by about 11% from what it
| | 02:57 | was the previous year.
| | 02:59 | Then if you subtract your Total
Liabilities from your Total Assets you get your
| | 03:04 | Net Operating Assets.
| | 03:05 | In other words, what do you have to work
with in terms of financial capital, and
| | 03:09 | that amount has gone up
substantially since 2009.
| | 03:13 | Finally, the balance sheet shows how
a company has funded its operation.
| | 03:18 | This company issued a substantial
amount about two times more in short-term
| | 03:23 | notes and a note is payable within a
year, and then you have no long-term
| | 03:28 | debt which are described as bonds, so
there's no change in no amount in either case.
| | 03:34 | For relatively low total debt,
even if that debt has gone up by about
| | 03:38 | one-and-a-half times over the previous year.
| | 03:40 | That's still not a lot for a
company with this level of revenue.
| | 03:44 | And then finally, you can discuss the
capital stock, which is the amount of
| | 03:48 | stock owned by stockholders in
the company, and retained earnings.
| | 03:53 | Retained earnings are your net
operating assets, minus your Total Debt,
| | 03:58 | minus the capital stock.
| | 04:00 | In other words, that's the amount of
money on your bottom line, the amount that
| | 04:04 | you really have to work with.
| | 04:06 | A balance sheet provides a general
overview of a company's financial health.
| | 04:10 | When you analyze a company's health,
you pay particular attention to how the
| | 04:13 | company's assets, liabilities and
sources of capital have changed over time.
| | 04:17 | You can also use this
document to create a budget.
| | 04:19 | I'll show you how to do that later.
| | Collapse this transcript |
| Introducing the income statement| 00:00 | The financial document public
companies use to report their income and
| | 00:03 | expenditures is called an income statement.
| | 00:06 | In a way it's the most disappointing
of the financial documents, because it
| | 00:09 | usually doesn't go into much detail.
| | 00:12 | I'll go into more detail when I
discuss how to create an income statement you
| | 00:15 | can use it in the budgeting process,
but let's take a look at the sample income
| | 00:18 | statement for a fictitious company.
| | 00:21 | The top line is Revenue.
| | 00:23 | This is the amount of money that the
company has made through sales, either of
| | 00:27 | goods, services, or both.
| | 00:30 | Then you get your operating expenses,
what did it cost you to make that money.
| | 00:34 | So you have cost of revenue, which
can include the cost of goods sold.
| | 00:38 | In other words, how much did it cost you
to make what it is that you're selling?
| | 00:42 | Inventory costs, such as storage,
warehouse rental, and so on, and then also any
| | 00:48 | sales or volume discount that
you've provided to your vendors.
| | 00:52 | Companies will often offer a premium to
other companies that buy their products
| | 00:56 | if they buy in a certain quantity.
| | 00:58 | You can include those volume
discounts as part of your cost of revenue.
| | 01:02 | Then you've other expenses
including research and development.
| | 01:05 | What are you spending to move into
new areas or create new products?
| | 01:09 | Sales and marketing.
| | 01:10 | Self-explanatory, and your
general and administrative overhead.
| | 01:14 | In other words, what does it cost
to run and administer your business?
| | 01:18 | When you add everything up, you
have your total operating expenses.
| | 01:21 | Then when you subtract your
operating expenses from your revenue, you get
| | 01:26 | your operating tncome.
| | 01:27 | Now you need to include your other
forms of income. In this case the company
| | 01:32 | doesn't have any investments, which
means that its income before income taxes is
| | 01:37 | exactly the same as this operating income.
| | 01:40 | Again, expenses subtracted from revenue.
| | 01:44 | Then you have your expected income tax
bill, which will be $29,000, and you have
| | 01:49 | your net income, the bottom line.
| | 01:52 | You can learn a lot about a company by
combining these numbers in what are called ratios.
| | 01:56 | I described ratio analysis in my other
course, Excel 2007: Financial Analysis.
| | 02:02 | In this case, I'll just indicate that
the company's net income has increased by
| | 02:07 | about 100% over the past
year, from $47,000 to $86,000.
| | 02:14 | All that analysis tells you a lot
about the company and the way they're
| | 02:16 | generating their income.
| | 02:18 | The income statement offers the broadest
possible view of a company's income and expenditures.
| | 02:23 | If you're preparing a budget you'll
need a more detailed picture to make
| | 02:26 | accurate projections.
| | 02:27 | I'll show you how to do that later.
| | Collapse this transcript |
| Introducing the cash flow statement| 00:00 | Managers must closely monitor the
amount of cash they have on hand, but they
| | 00:04 | must also examine the company's cash
position in terms of money they owe and are owed.
| | 00:09 | The financial statement that describes this
position is called the cash flow statement.
| | 00:13 | Even though the names are similar,
you should not equate the worksheet
| | 00:17 | the tracks transactions and daily balance
for bank account with the cash flow statement.
| | 00:22 | The two documents are entirely different.
| | 00:24 | A ledger for bank transactions
is concerned only with that account.
| | 00:28 | A cash flow statement encompasses
what you owe and what others owe you in
| | 00:31 | addition to your cash on hand.
| | 00:33 | In a cash flow statement, assets such
as inventory and accounts receivable
| | 00:37 | are written as negative numbers, because you
haven't converted those assets into cash yet.
| | 00:41 | So for example here, you have
accounts receivable, inventory and prepaid
| | 00:46 | expenses, which are all negative numbers.
| | 00:47 | Accounts receivable, if you look at
it in a balance sheet, is considered an
| | 00:52 | asset and it is a positive number,
because you can convert it as a current
| | 00:57 | asset into cash within a year.
| | 01:00 | In this case, because accounts
receivable is not a cash yet, you represent it
| | 01:03 | as a negative number.
| | 01:05 | Same thing for inventory, which
represents the unsold goods you have in stock,
| | 01:09 | and prepaid expenses, such as any
legal retainer fees that sort of thing.
| | 01:14 | Next you have accounts payable,
interest expense and income tax.
| | 01:19 | On a balance sheet these are considered
to be current liabilities because you'll
| | 01:22 | have to pay them within a year
and are written as negative numbers.
| | 01:26 | In the cash flow statement that deals
only with the amount of cash you have on
| | 01:29 | hand then your accounts payable is
positive, because that's the money you
| | 01:33 | haven't paid to your vendors.
| | 01:34 | Interest, same way.
| | 01:36 | It's the money that you have not
paid to your lenders, and income tax is
| | 01:40 | any residual income tax burden that will
probably be paid after the end of your fiscal year.
| | 01:45 | You can add all of those amounts
together to get the total of your operating
| | 01:51 | assets and liabilities.
| | 01:53 | Your operating cash before depreciation
is calculated by adding these two values.
| | 01:58 | So for example, if you started out
with a net income of quarter of a million
| | 02:02 | dollars and added -$542,000, you
would end up with operating cash before
| | 02:08 | depreciation of about $292,000.
| | 02:12 | And again, with depreciation, here is
a difference between a cash flow sheet
| | 02:17 | and a balance sheet.
| | 02:18 | Depreciation is a prepaid expense.
| | 02:21 | In other words, the value of an
office building that you might own or any
| | 02:24 | computer equipment that you might own
will go down as the economic asset ages.
| | 02:29 | That means that you can count depreciation
for cash flow purposes as a positive amount.
| | 02:35 | In this case, you have $425,000 worth
of depreciation, which when you added to
| | 02:40 | operating cash before depreciation, you get
a positive cash flow result of over $132,000.
| | 02:47 | Next, you need to factor in any cash
flows for investments during the year and
| | 02:51 | in this case investment
doesn't mean buying stock or bonds.
| | 02:54 | It means investing in capital
purchases such as office buildings, computer
| | 02:59 | equipment, and so on.
| | 03:00 | And let's say in this case, you had $
300,000 worth of equipment purchases.
| | 03:04 | Next, you need to include
your cash flows and financing.
| | 03:07 | Let's say you took out a
loan that was due within a year.
| | 03:10 | That is considered a short-term debt
increase and suppose the amount was $30,000.
| | 03:16 | That again is cash that you
have in your account to use.
| | 03:18 | Now suppose you wanted to do some
office renovations and you borrowed a
| | 03:23 | half-million dollars or 500,000 to be
paid off in the term of over one year.
| | 03:27 | That would be your long-term debt increase.
| | 03:30 | Next, you have your capital stock issue.
| | 03:32 | That is the stock that you've sold
and received funds for and finally this
| | 03:36 | negative number here is dividends.
| | 03:39 | Dividends is what is paid out to
stockholders and it represents an expense per
| | 03:44 | share, although this is the total.
| | 03:47 | So for example, let's say that you
had 10,000 shares of stock in your
| | 03:51 | company. If each of those shares received a
$10 dividend, then that total would be $100,000.
| | 03:57 | You add all those numbers together
to get your total cash flows from
| | 04:00 | refinancing, and at the end you
add cash flow from operations, which
| | 04:05 | is everything up here.
| | 04:06 | Cash flow from investment, which is
here, and finally, your tTotal cash flows
| | 04:12 | from financing, and that gives you
an increase or a decrease in cash.
| | 04:16 | At first the cash flow statement can
seem a little counterintuitive, but once
| | 04:19 | you understand how the assets and
liabilities contribute to your company's cash flow,
| | 04:22 | you'll have no problem understanding it.
| | Collapse this transcript |
| Creating a management income statement| 00:00 | Income statements that are meant for
public consumption such as regulatory
| | 00:03 | filings are purposely
kept as general as possible.
| | 00:07 | Even though the numbers reported in
them are accurate and are derived using
| | 00:10 | generally accepted accounting practices,
there is very little detail regarding
| | 00:14 | precise expenditures in those documents.
| | 00:16 | To create an income statement it's more
useful to you as a budgeter, you need to
| | 00:20 | go into more detail than in the
public version of the document.
| | 00:22 | In this simple example I've added
entries for specific costs related to
| | 00:26 | revenue generation.
| | 00:27 | In this case, cost of Goods
Sold and Volume Related Expenses.
| | 00:31 | Cost of goods sold is what it cost
you to make what you sell, and volume
| | 00:35 | related expenses are expenses that you have to
pay to your distributors, and also to stores.
| | 00:41 | For example, if you're paying for
shelf space, that sort of expense.
| | 00:44 | So what you do is first have your
overall top line revenue, and have revenue
| | 00:50 | related expenses, your cost of goods
sold and volume related expenses and
| | 00:54 | just as a helpful hint you can calculate
those values as a percentage of your revenue.
| | 01:00 | In other words, if your cost of
goods sold is 46.15% and you want to keep
| | 01:05 | it under 50%, then you're meeting
your goal, and again, it all depends on
| | 01:08 | your business model and what your
particular goals are for each of those expenditures.
| | 01:13 | Then you can get down to
your operating expenses.
| | 01:16 | Operating expenses are what it costs you
to run your business, exclusive of sales.
| | 01:20 | So for example, Occupancy is your rent.
General and Administrative is what it
| | 01:25 | takes for you to manage your company,.
Advertising and Marketing and Employee
| | 01:29 | Benefits are both self-explanatory.
| | 01:31 | So finally, you find the total of all
of your expenses and express it as a
| | 01:37 | percentage of revenue.
| | 01:38 | After you have both your revenue
expenses and your operating expenses, you can
| | 01:42 | figure out how much you're
earning and what you net income is.
| | 01:45 | In this case, your operating earnings
is D8, the contribution or profit margin
| | 01:51 | based on your revenue and
cost of generating that revenue.
| | 01:55 | Then you have your total operating
expenses, which are summarized here in
| | 02:01 | the D15 and you get your value, which is
255,000 plus -131,500, which leaves this number here.
| | 02:08 | Then you can substrate out your interest
expense to get your earnings before income tax.
| | 02:15 | You can then estimate your income tax.
| | 02:17 | Corporate rate is usually 34% at this level
and then from there, calculate your net income.
| | 02:23 | Once again, keeping everything as
a ratio of your top line revenue.
| | 02:28 | Unlike the income statement that's
designed for public consumption, and
| | 02:31 | a management income statement breaks cost
and revenue categories into detailed entries.
| | 02:35 | So you have both the overall context
of the business's operations and the
| | 02:39 | details that will help you identify
where you're making and spending your money.
| | Collapse this transcript |
| Creating a management balance sheet| 00:00 | When you create a balance sheet you do so to
summarize your operating income and expenses.
| | 00:05 | Even though you should keep any
publicly released documents as general as
| | 00:09 | possible, you do need to add as much
details you can to the private version
| | 00:12 | so you can call out the
information you require for your budgets.
| | 00:15 | In this case I have a
management balance statement.
| | 00:17 | And if this were a public balance
statement then I wouldn't have these breakouts
| | 00:22 | here, Cost of Goods Sold, or these two projects.
| | 00:26 | In other words, it would just be Cost
of Revenue, which would be that total of
| | 00:30 | these two lines, one for each year.
| | 00:32 | And then Research and Development would
have the total of these two lines in this cell.
| | 00:37 | And these details would not be broken out.
| | 00:40 | The private version of the balance
sheet contains proprietary information, such
| | 00:43 | as the names of projects and their
associated costs, which help you make
| | 00:47 | budgeting decisions.
| | 00:48 | Always be certain which version of the
document you are showing to your audience.
| | 00:51 | You don't want to leak internal
information to your competitors.
| | Collapse this transcript |
| Creating a budget for next year| 00:00 | After you've created your management
income statement, management balance
| | 00:03 | statement, and cash flow statement,
you can update those documents for next year.
| | 00:08 | Part of your analysis that doesn't
appear in the financial statements, but
| | 00:11 | should appear in your discussion and
presentation is your assumption regarding
| | 00:15 | sales growth and also growth of expenses.
| | 00:17 | For example, do you have a goal of
20% sales growth for next year, to hire
| | 00:22 | another 15 employees, or maybe finance
renovating an office building through
| | 00:25 | long-term borrowing?
| | 00:27 | Each of those assumptions must find
your way into your budget, but that's a
| | 00:30 | business analysis problem, not an Excel problem.
| | 00:33 | When you do settle on your numbers for
next year, enter them into a copy of your
| | 00:37 | income balance and cash flow statements.
| | 00:39 | Be sure to add another column that
calculates the difference between the two
| | 00:42 | values, indicating an increase or
decrease in the revenue or expense.
| | 00:45 | For example I've taken the balance
statement that I created earlier.
| | 00:50 | And I have added a Projected column for
fiscal year 2011 and a % Change column.
| | 00:58 | And these changes are simply my
best guesses on what I want to spend on
| | 01:03 | revenue related expenses, cost of
goods sold and so on, given my revenue
| | 01:07 | target of $800,000.
| | 01:10 | I've left my operating expenses the same.
| | 01:13 | And then I've recalculated my
operating earnings, interest expense, earnings
| | 01:18 | before income tax and income tax and
net income based on these changes here.
| | 01:22 | Be prepared to go through several
iterations when you create your final budget,
| | 01:26 | both from your own analysis and from
your discussions with your colleagues.
| | 01:29 | And then once you create your budget,
be ready to stick to it if possible or to
| | 01:34 | change it as circumstances warrant.
| | Collapse this transcript |
|
|
4. Managing BudgetsReferring to cells from other worksheets and workbooks in formulas| 00:00 | Most of the budget workbooks you
create will have all the values you need
| | 00:03 | within that workbook.
| | 00:05 | However, if you want to draw values
from other worksheets or workbooks, you can
| | 00:09 | do so by creating formulas that
refer to those outside sources.
| | 00:13 | In this workbook, which I just have
listed as a dashboard that I used to track
| | 00:17 | my balance sheet, I have my
revenue for the year 2010.
| | 00:20 | I just typed value in.
| | 00:22 | You can see it's here on the Formula bar.
| | 00:24 | So, what if I wanted to bring in
that data from another worksheet or
| | 00:29 | perhaps another workbook?
| | 00:30 | To do that, I need to create a
formula that refers to that other cell.
| | 00:34 | Here is how you do it.
| | 00:35 | First is you delete this value
and then you type in equal sign.
| | 00:40 | If the value you want is on another
worksheet in the same workbook, then you can
| | 00:45 | simply click that other
worksheet's tab, in this case, LocalData.
| | 00:51 | The number that I want
is my revenue that's here.
| | 00:55 | You can see up in the Formula bar that
Excel is creating the formula for me.
| | 01:00 | I have =LocalData!C15.
| | 01:03 | Let me talk about this reference for a second.
| | 01:07 | LocalData is the name the worksheet that
I'm on currently, which you can see here.
| | 01:12 | C15 is the address of the cell that I clicked.
| | 01:16 | That can also be a cell
range if you want it to be.
| | 01:19 | With that in place, I can press Enter,
and Excel creates the formula for me.
| | 01:24 | Now let's say that the value I
want is in another workbook entirely.
| | 01:27 | I can create a link to that too.
| | 01:29 | First, I'll press Delete
to get rid of that value.
| | 01:32 | Then I'll type an Equal sign.
| | 01:35 | Then I'll switch to the other workbook.
| | 01:37 | Now notice that on the View tab,
which is the tab you need to use, almost
| | 01:42 | everything is grayed out except for
Arrange All, which allows you to change the
| | 01:46 | arrangement of the open workbooks,
and Switch Windows, which allows you to
| | 01:50 | switch to another workbook.
| | 01:52 | You need to make sure that the workbook
from which you want to draw the value is
| | 01:56 | opened before you start this procedure.
| | 01:58 | Then you can switch to it. I call it Refer.
| | 02:03 | Here we see the same date as before,
except now it's in the new workbook.
| | 02:07 | Also, you can see that Excel has
maintained the equal sign here in the Formula
| | 02:11 | bar, so you know that you're creating a formula.
| | 02:14 | You can click the cell that you want,
and Excel creates the full reference.
| | 02:18 | I'll just walk through this again.
| | 02:21 | Refer.xlsx in square brackets
is the name of the workbook.
| | 02:27 | Data is the name of the worksheet
followed by an exclamation point.
| | 02:31 | Then you have C15, which is the cell reference.
| | 02:34 | When you press Enter, Excel creates the formula.
| | 02:38 | You can see that it contains the
reference you created just a moment ago.
| | 02:41 | Now let's say that you want to finalize
your budget, and prevent any updates to
| | 02:45 | that other cell from affecting this value here.
| | 02:48 | In other words, because this cell
contains a formula that refers to the cell
| | 02:52 | in the other workbook.
| | 02:54 | If that value changes, then so will this one.
| | 02:56 | If you don't want that to happen, if
you want that value to stay exactly as it is,
| | 03:00 | you can break the link.
| | 03:02 | To do that, you select the cell,
copy it, and then paste its value.
| | 03:10 | When you do, the formula disappears
and the value appears within the cell.
| | 03:15 | Now, no matter what happens to that
other workbook, whether the value changes,
| | 03:18 | is deleted, you can't open the workbook or
whatever, this value will remain the same.
| | 03:23 | Creating references to cells and
other worksheets or even another workbook
| | 03:27 | helps you incorporate data from outside
sources into your budgets and financial documents.
| | 03:31 | When you want to make your budget a
standalone document, you can break the link
| | 03:35 | to the outside source and work with
the data already in your workbook.
| | Collapse this transcript |
| Summarizing budget data by creating a PivotTable| 00:00 | Excel tables enable you to analyze
budget data in some interesting ways, but
| | 00:04 | they're not as flexible or powerful
as another Excel tool, the PivotTable.
| | 00:09 | A PivotTable lets you rearrange a data
set on the fly, so you can analyze it
| | 00:13 | from several different
perspectives with a minimum of effort.
| | 00:16 | To create a PivotTable, you must have
the source data laid out as a table with
| | 00:21 | column headers and no
extraneous data surrounding the table.
| | 00:24 | Excel tables are the perfect
data source for a PivotTable.
| | 00:27 | If you do use a data source that's
not in Excel table, make sure the column
| | 00:31 | headers are formatted differently than the data.
| | 00:33 | That way, Excel recognizes them as headers.
| | 00:36 | To create the PivotTable, you click
any cell in the source data list or the
| | 00:41 | Excel table in this case.
| | 00:43 | On the Insert tab, click PivotTable.
| | 00:46 | Verify that Excel has
identified the data list you want to use.
| | 00:51 | In this case, it is the
table that I named AssetEntries.
| | 00:55 | Click OK and Excel creates a new
worksheet that contains a PivotTable.
| | 00:59 | I personally always create a
PivotTable on a separate worksheet.
| | 01:03 | That way I don't have to worry about
crowding or obscuring the original data.
| | 01:06 | If I want to see that data, I
can just go back to that worksheet.
| | 01:09 | So now I need to add data to the PivotTable.
| | 01:12 | Let's say that I want to add Year
and Category to the Row Labels area.
| | 01:19 | So I have 2009, each of my accounts.
| | 01:24 | Then I can add the amount to the Values area.
| | 01:28 | In this case, I can see that in 2009, I
had $365,000 worth of current assets and
| | 01:35 | then in 2010, I had over 400,000.
| | 01:39 | The way the PivotTable's laid out right now,
we have the subtotals at the top of the group.
| | 01:43 | I personally prefer them
at the bottom of the group.
| | 01:45 | So to change that, I can
click any cell in the PivotTable.
| | 01:49 | Go up to Design, click Subtotals, and put
the subtotals at the bottom of the group.
| | 01:55 | To me, that's just makes more sense.
| | 01:57 | Also, it gives the PivotTable a
little bit more room to breathe, because now
| | 02:02 | instead of having a value here next
to 2009, we have some white space.
| | 02:07 | Now that you've taken the first step and
learned how to create a PivotTable, you
| | 02:10 | can dive in and manipulate your
PivotTable structure and formatting to gain
| | 02:14 | insights into your data.
| | Collapse this transcript |
| Analyzing budget data in a PivotTable| 00:00 | The real power of a PivotTable comes
out when you want to rearrange your
| | 00:03 | budget data dynamically.
| | 00:05 | A task that would take several minutes
if done by hand, takes just a few seconds
| | 00:09 | when you summarize your data using a PivotTable.
| | 00:12 | Now in this case, the row and column headers
provide the basic structure for the PivotTable.
| | 00:18 | You see that in the row
area, we have the year, 2009.
| | 00:22 | Then we have the individual
categories of current asset.
| | 00:25 | We have Account Receivable, Cash and so on.
| | 00:29 | If I want, I can change the row and column
header positions to change the data arrangement.
| | 00:33 | So let's say for example, I want to
put Year in the column header area.
| | 00:38 | For that, I now have 2009, 2010.
| | 00:41 | I can compare Accounts Receivable
2009 and 2010, and so on to Cash,
| | 00:47 | Depreciation and so on.
| | 00:49 | I can also add other category headers to
change the arrangement of the data and
| | 00:54 | provide even more subsets.
| | 00:56 | So let's say for example that I wanted
to go to Year and Category back to the
| | 01:00 | arrangement I had before,
but now I want to add Month.
| | 01:03 | Dragging the Month field header between Year
and Category, now I get the monthly values.
| | 01:10 | So for Accounts Receivable, 1946,
I had a negative inventory month and so on.
| | 01:17 | I can also limit the data that appears
within a PivotTable by creating a filter.
| | 01:22 | To create a filter, I go over
here to the PivotTable Field List.
| | 01:26 | If you don't see the Field List, you
can click the Options tab and click Field
| | 01:31 | List and it will reappear.
| | 01:33 | Over here, you click the
field by which you want to filter.
| | 01:37 | In this case, I'll filter by month
and use the settings to create the
| | 01:40 | filter that you want.
| | 01:42 | In this case, I'll create a selection
filter which allows me to determine which
| | 01:46 | months I want to show.
| | 01:49 | Just drag that, so you can see more options.
| | 01:53 | So let's say that I just want to
show January and February, the first two
| | 01:56 | months of the year.
| | 01:58 | For that, I can click Select
All to clear all the selections.
| | 02:01 | Then I select January and February. Click OK.
| | 02:07 | Excel limits my PivotTable to only
display the months of January and February.
| | 02:11 | Here are the months for 2009 and
below you see the months for 2010.
| | 02:17 | To remove a filter, you can go back to
the same header and click Clear Filter.
| | 02:24 | You can also create
what's called a report filter.
| | 02:27 | For that, you use this area
here, the Report Filter area.
| | 02:30 | Let's say that I want to drag Month up there.
| | 02:36 | Now, Month is no longer affecting the
organization in the body of the PivotTable.
| | 02:41 | Instead, it's here in the Report Filter area.
| | 02:43 | So I can use it to filter the data without
affecting the structure of the PivotTable.
| | 02:48 | So if I want to filter by Month, I'll
go up here, click the down arrow, and I
| | 02:54 | will select the checkbox, so
I can select multiple items.
| | 03:00 | I want to clear the All button, select
January and February to show those two
| | 03:05 | months, and then click OK.
| | 03:07 | Now notice that the values in the
PivotTable changed because I'm not summarizing
| | 03:12 | as many as I did before.
| | 03:14 | If I want to remove the filter, I can
go back, click the down arrow, and when
| | 03:20 | you're using the Report
Filters area, you need to select All.
| | 03:23 | That brings everything back.
| | 03:25 | To do that, you can click OK.
| | 03:28 | All of my data is once again
summarized in the PivotTable.
| | 03:31 | Now let's suppose that I want to change how
my data is presented within the PivotTable.
| | 03:36 | In other words, right now, it's kind of hard to
read because all the values are just numbers.
| | 03:42 | There is no sort of formatting to help
you determine where the thousands and
| | 03:45 | where the millions are.
| | 03:46 | You can change field settings, the
number format specifically, by clicking any
| | 03:51 | cell that contains a number,
then going up to Field Settings.
| | 03:55 | This is on the Options contextual tab.
| | 03:58 | In the Field Settings
dialog box, click Number Format.
| | 04:02 | Then this is simply the Format Cells
dialog box limited down to the Number page.
| | 04:07 | So if I click Accounting, I do want 2
decimal places and the dollar sign. I'll click OK.
| | 04:13 | Again, Excel formats that field.
| | 04:16 | So it's displayed in the Accounting
format and the data is much easier to read.
| | 04:21 | Changing the PivotTable's arrangement
shifts the data's emphasis, enabling you
| | 04:24 | to examine the data from
different perspectives quickly and easily.
| | 04:28 | In addition to changing a PivotTable's
field arrangement, you can restrict the
| | 04:31 | data that appears within the PivotTable,
and format its fields to focus its data
| | 04:35 | and make it more comprehensible.
| | Collapse this transcript |
|
|
5. Defining Alternative Budget ScenariosDeciding which scenarios to create| 00:00 | When you develop your budgets for the
coming year, it's usually a good idea to
| | 00:03 | take into account possible
scenarios that might occur.
| | 00:06 | These scenarios can affect the amount of
money available to your business either
| | 00:10 | as income or in terms of loans.
| | 00:12 | So you should take those into account.
| | 00:15 | What are some things that
you should take into account?
| | 00:17 | Well, potential changes in the world
and national economies. Also events within
| | 00:21 | your industry and events within your company.
| | 00:23 | For example, what if one of your
new products or services is delayed?
| | 00:27 | That might affect your orders,
which would be reflected in the Accounts
| | 00:31 | Receivable category.
| | 00:33 | Or what if a critical supplier can't give you
all the parts you need or it goes out of business?
| | 00:37 | All these considerations
must factor into your analysis.
| | 00:41 | When you're making your decisions take
a look through your financial statements
| | 00:44 | such as the balance sheet
here and find the weak points.
| | 00:47 | What were you worried about last year?
| | 00:48 | For example, were you concerned that one or
more of your customers might go out of business?
| | 00:52 | If that's the case you'll get fewer orders.
| | 00:55 | Or is it possible that the interest
expense on a long-term obligation could change?
| | 00:59 | Instead of only paying
a $60,000 next year in interest,
| | 01:03 | if you took out variable-rate
financing you could end up paying a lot more in
| | 01:06 | interest in the next year than you are in
the current year, or that you budgeted for 2011.
| | 01:11 | Finally, how much money do you want to
devote to buy new property and equipment?
| | 01:16 | Most companies can skip a year in buying
new computers or perhaps buying new printers.
| | 01:21 | But in some cases those costs will
be necessary and you'll have to may
| | 01:24 | cuts somewhere else.
| | 01:26 | Creating a budget is difficult mainly
because it's impossible to forecast the future.
| | 01:31 | When you start to think about your
budget for the coming year, try to recall what
| | 01:34 | concerned you or what excited
you during the previous year.
| | 01:37 | Those instincts will help you identify
potential changes in your cash flow and
| | 01:41 | potential for growth during the coming year.
| | Collapse this transcript |
| Creating and applying scenarios| 00:00 | After you decide on your budget
projections for the coming year, you need to
| | 00:04 | build those projections
into your Excel workbook.
| | 00:06 | You can use scenarios to store those
projections and apply them without creating
| | 00:10 | multiple sets of similar
data within a worksheet.
| | 00:12 | First I'll show you how to create a scenario.
| | 00:15 | In this case let's say that I'm
anticipating a smaller value for
| | 00:20 | Accounts Receivable.
| | 00:22 | To create the Scenario I click the
Data tab, click What-If Analysis and open
| | 00:28 | the Scenario Manager.
| | 00:32 | Click Add and now I can type-in a scenario name.
| | 00:35 | And I'll just say LowAR,
meaning Accounts Receivable.
| | 00:41 | I selected the cell that I
wanted to change, cell D8.
| | 00:46 | And now that everything
looks correct I can click OK.
| | 00:50 | And now I have the opportunity
to change the value in cell D8.
| | 00:54 | Let's say that instead of
800,000 let's say that I have 700,000.
| | 01:00 | I can click OK to create the scenario
and go back to the main Scenario Manager
| | 01:05 | dialog box, or I can click
Add and add a new scenario.
| | 01:09 | This time I am just going to click OK
and go back to the Scenario Manager.
| | 01:14 | So now I have the LowAR scenario stored.
| | 01:18 | And you'll notice it does
not appear in the workbook yet.
| | 01:20 | If I want to apply the scenario I can
open the Scenario Manager dialog box,
| | 01:25 | click the scenario I want, and click Show.
| | 01:29 | When I do Excel changes the value in
the workbook and it adjusts the formulas
| | 01:34 | based on that value.
| | 01:36 | When you are done displaying scenarios
you can close the Scenario dialog box.
| | 01:40 | Now this is important.
| | 01:41 | I need to emphasize that you can't remove a
scenario from within the Scenario Manager.
| | 01:46 | Instead, the only way to get rid of it is to
either press Ctrl+Z or click the Undo button.
| | 01:53 | Watch cell D8.
| | 01:53 | It's currently 700,000.
| | 01:56 | When I click Undo it goes back to 800,000.
| | 02:00 | If you save a workbook with a
scenario applied that value becomes the value
| | 02:06 | in the workbook.
| | 02:07 | It is saved along with the
workbook and the original value is lost.
| | 02:11 | It is very important that you undo
all of your scenarios or not save your
| | 02:16 | workbook before you're done.
| | 02:18 | You can apply a single scenario.
| | 02:19 | Can you apply many of them at the same time?
| | 02:22 | Absolutely, I'll show you how to do that.
| | 02:25 | Again going into the Scenario Manager I
can create a new scenario by clicking Add.
| | 02:30 | In this case I'll call it HighLiabilities.
| | 02:35 | And I want to change two cells,
so to do that I'll click here.
| | 02:40 | And then I'll click the Collapse Dialog button,
which allows me to go out and select cells.
| | 02:46 | I am going to hold down the Ctrl key
and click D16 for Accounts Payable and D18
| | 02:54 | for Interest Expense.
| | 02:56 | I'll expand the dialog
back up. I see D16 and D18.
| | 03:01 | And I can click OK.
| | 03:03 | Now I can enter values for the cells.
| | 03:04 | In cell D16 I currently have
250,000 of Accounts Payable.
| | 03:10 | So let's increase that. Let's say that
I have a lot of unexpected billings and
| | 03:13 | it goes up to 400,000.
| | 03:16 | And then my Interest Expense, which is
currently 60,000, let's say that I have
| | 03:20 | an adjustable-rate loan, and it goes up and
my Interest Expense doubles, say to a 120,000.
| | 03:27 | I am done.
| | 03:29 | The values look right, so I can click OK
and go back to the Scenario Manager.
| | 03:33 | I am going to create one more
scenario so I'll add it and that is HighAR.
| | 03:39 | In other words, let's say that I have
Accounts Receivable that are higher than
| | 03:43 | my projection of 800,000.
| | 03:46 | I can type-in a cell reference.
In this case it's cell D8 like it was before.
| | 03:51 | But let's say that I also
want to have less cash on hand.
| | 03:55 | That would make sense because my
Accounts Receivable goes down as that money is
| | 03:59 | transferred into cash, in other
words when I receive the payments.
| | 04:03 | So I'll type D7 to D8 and click OK.
| | 04:10 | In this case my cash is going down.
| | 04:12 | So let's say that I change
it from 400,000 to 300,000.
| | 04:18 | And my Accounts Receivable
goes up from 800,000 to 1 million.
| | 04:24 | I'm done.
| | 04:26 | Click OK and I'm back in the Scenario Manager.
| | 04:29 | Now I can apply my scenarios.
| | 04:31 | In this case let's say that I
want the worst-case, which is that I
| | 04:35 | have HighLiabilities.
| | 04:37 | I can show it and you see
that it changed the values here.
| | 04:41 | My Accounts Payable went up.
| | 04:43 | Now let's say that I have
high Accounts Receivable.
| | 04:46 | When I show this one it changes here
to a million, changes the other cell as
| | 04:52 | well, my cash went down.
| | 04:54 | And the other cells that contain formulas
that rely on that value changed as well.
| | 04:58 | But now let's see what happens
when I apply the LowAR scenario.
| | 05:03 | In other words, that scenario
affects the same cell here, D8, as the one I
| | 05:08 | just applied, HighAR.
| | 05:10 | So watching cell D8 when I click Show,
Accounts Receivable changes from a
| | 05:16 | million to 700,000, but my Cash stayed the same.
| | 05:20 | That's because the LowAR
Scenario does not affect that cell.
| | 05:24 | In other words putting in one
scenario after another one is additive.
| | 05:28 | One does not cancel out or supersede the other.
| | 05:31 | I am done displaying scenarios so
I'll click Close and then click the Office button.
| | 05:39 | Click Close.
| | 05:41 | And because I do not want to
save my changes I will click No.
| | 05:45 | Scenarios help you perform powerful what-if
analysis by storing alternative data sets.
| | 05:50 | When you generate budget projections,
scenarios let you manage these alternatives
| | 05:54 | without making your worksheets more complex.
| | Collapse this transcript |
| Editing and deleting scenarios| 00:00 | After you've add alternative data sets
to your worksheets using scenarios, you
| | 00:04 | can use them to perform what-if
analysis on your budget projections.
| | 00:07 | If you'd like to change the contents of
the scenario or get rid of it entirely,
| | 00:11 | you can do so from within the Scenarios Manager.
| | 00:13 | First, I'll show you how to
edit an existing scenario.
| | 00:16 | To do that, you go to the Data tab, click
What-If Analysis and click Scenario Manager.
| | 00:24 | All you need to do is click the
scenario that you want to edit and click Edit.
| | 00:29 | You can change everything about the
scenario from the scenario name to the
| | 00:34 | cells it's changing, to the values that go
into those cells when you apply the scenario.
| | 00:38 | In this case, I will call it a VeryLowCash.
| | 00:42 | I will still change cell D7, click OK,
and now instead of displaying the
| | 00:48 | value in the workbook,
| | 00:51 | Excel displays the value from the scenario.
| | 00:53 | In this case, I will change it down to
50,000 and press OK and now you see that
| | 01:01 | my change has been implemented.
| | 01:03 | Now let's say that I think that this
scenario is just way too pessimistic and
| | 01:06 | that my cash position is going to be fine.
| | 01:09 | If I want to delete that scenario,
I can click it and click Delete.
| | 01:14 | Now note that I did not get a
confirmation dialog box or message.
| | 01:18 | It was deleted immediately.
| | 01:20 | So if you have a complicated scenario
and you think that you want to delete it,
| | 01:24 | but you're not sure, make a backup
copy of the workbook so you still have the
| | 01:27 | scenario there and then
delete it in the current workbook.
| | 01:31 | Editing a scenario lets you adjust
your assumptions about the coming year and
| | 01:35 | save the data in your workbook
without changing the physical data.
| | 01:38 | If you find that editing scenario
would take more time than deleting it and
| | 01:41 | starting a new, you can
delete any scenario you want.
| | 01:45 | To preserve the information in that
scenario, you might want to create a summary
| | 01:48 | worksheet before you delete it.
| | Collapse this transcript |
| Summarizing scenarios| 00:00 | One tremendous advantage to working with
scenarios in Excel is that they're only
| | 00:04 | visible when you show them in the worksheet.
| | 00:06 | Of course, that's their disadvantage as well.
| | 00:08 | Unless you copy a scenario's values to
another worksheet, you can't recall them
| | 00:12 | without opening the Scenario Manager.
| | 00:14 | However, Excel does let you create
just such a worksheet summarizing all of
| | 00:18 | your scenarios in the single place
you can leave in your workbook, copy to
| | 00:21 | another workbook, or print.
| | 00:23 | To do that you create what's
called a scenario summary worksheet.
| | 00:27 | Before you create it though, you must
ensure that no scenarios are applied when
| | 00:31 | you create the summary worksheet.
| | 00:32 | I'll show you when I get over there, but
first let me create the summary worksheet.
| | 00:36 | To do that, click What-If Analysis
and once again we're on the Data tab.
| | 00:41 | Click Scenario Manager and then click Summary.
| | 00:45 | We want a scenario summary, and the
cells that are changing as a result of the
| | 00:50 | changes within the scenarios are E7 and D13.
| | 00:55 | Click OK and Excel creates the worksheet.
| | 00:59 | As the note at the bottom indicates,
the Current Values column, which is here,
| | 01:04 | represents the values of changing cells
at the time you created this worksheet.
| | 01:08 | So in other words, if you have showed
the LowCash scenario before you created
| | 01:12 | the summary worksheet, then the current
value instead of being 400,000 would be 150,000.
| | 01:19 | You can go through each of the cells.
So here the scenarios change D7 and D8.
| | 01:25 | In this case, LowCash changes cell D7 only.
| | 01:30 | That's what the gray means, and then
HighCashAndAR changes both cells D7 and D8.
| | 01:37 | Again, the changing cells
are highlighted in gray.
| | 01:40 | The Result Cells are cells that
contain a formula that is affected by the
| | 01:45 | changes you made here by changing these cells.
| | 01:47 | So for example, with the current
values the formula in cell E7 returns this
| | 01:52 | value, the one in D13, this one.
| | 01:54 | If on the other hand these scenarios
are applied, then these values result in
| | 01:59 | these formula results.
| | 02:01 | The worksheet is just a worksheet.
You can delete it, move it, copy it like any other.
| | 02:05 | Summarizing your scenarios helps you
track which scenario contains which
| | 02:08 | changes, but be very careful to
ensure no scenarios are applied when you
| | 02:12 | create the summary.
| | 02:13 | If you do, the original data
displayed in the summary will be incorrect.
| | Collapse this transcript |
|
|
ConclusionGoodbye| 00:00 | Thanks for taking the time to work
through Excel 2007: Creating Business Budgets.
| | 00:05 | I hope you've learned a little bit
more about the budgeting process and what
| | 00:08 | goes into it and how that you can use
the standard financial documents and
| | 00:11 | modify them to create
budgets for your own business.
| | 00:14 | Take care and good luck!
| | Collapse this transcript |
|
|
