# Excel 2007: Creating Business Budgets

## with Curt Frye

In Excel 2007: Creating Business Budgets Curt Frye shows business owners and managers how to use Excel to create useful budgets that help them manage resources prudently. The course demonstrates how to use Excel spreadsheets to track cash on hand, and how to project income and expenses based on scenarios. It also shows how to take information from various sources to create a single Excel table, and then use PivotTables to analyze that data. Exercise files accompany the course.
Topics include:
• Tracking income and expenses by category and contract
• Using balance sheets
• Designing worksheets to assist decision making
• Creating income statements
• Calculating loans payments and interest
• Creating cell references to other worksheets
• Summarizing data in a chart
• Building alternative budget scenarios
author
Curt Frye
subject
software
Excel 2007
level
Intermediate
duration
1h 3m
released
Jun 16, 2010

Keep up with news, tips, and latest courses.

• ### FAQs

Search the closed captioning text for this course by entering the keyword you’d like to search, or browse the closed captioning text by selecting the chapter name below and choosing the video title you’d like to review.

Introduction
Welcome
 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 Hand
Creating an Excel table to track cash on hand
Tracking income and expenses by category and contract
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 Repayments
Calculating payments for a fully amortized loan
Calculating interest and principal components of a loan payment
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 Statements
Introducing the balance sheet
Introducing the income statement
Introducing the cash flow statement
Creating a management income statement
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
4. Managing Budgets
Referring to cells from other worksheets and workbooks in formulas
Summarizing budget data by creating a PivotTable
Analyzing budget data in a PivotTable
5. Defining Alternative Budget Scenarios
Deciding which scenarios to create
Creating and applying scenarios
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
Conclusion
Goodbye
 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

## Suggested courses to watch next:

Excel 2007: Financial Analysis (2h 18m)
Curt Frye

Excel 2007: Business Statistics (4h 19m)
Curt Frye

Are you sure you want to delete this bookmark?

### Bookmark this Tutorial

#### Description

{0} characters left

#### Tags

Separate tags with a space. Use quotes around multi-word tags. Suggested Tags:
cancel

### bookmark this course

{0} characters left Separate tags with a space. Use quotes around multi-word tags. Suggested Tags:

Error:

go to playlists »

# Create new playlist

 name: description: save cancel

# You must be a lynda.com member to watch this video.

Every course in the lynda.com library contains free videos that let you assess the quality of our tutorials before you subscribe—just click on the blue links to watch them. Become a member to access all 98,609 instructional videos.

Your organization has a limited access membership to the lynda.com library that allows access to only a specific, limited selection of courses.

You're logged in as an account administrator, but your membership is not active.

Contact a Training Solutions Advisor at 1 (888) 335-9632.

# How to access this video.

If this course is one of your five classes, then your class currently isn't in session.

If you want to watch this video and it is not part of your class, upgrade your membership for unlimited access to the full library of 1,894 courses anytime, anywhere.

You can always watch the free content included in every course.

Questions? Call Customer Service at 1 1 (888) 335-9632 or email cs@lynda.com.

You're logged in as an account administrator, but your membership is no longer active. You can still access reports and account information.

To reactivate your account, contact a Training Solutions Advisor at 1 1 (888) 335-9632.

# Need help accessing this video?

Call Customer Service at 1 1 (888) 335-9632 or email cs@lynda.com for help accessing this video.

site feedback

Thanks for signing up.

We’ll send you a confirmation email shortly.

• new course releases
• special announcements

Keep up with news, tips, and latest courses with emails from lynda.com.