IntroductionWelcome| 00:00 | (Music playing.)
| | 00:04 | Hi! I'm Curt Frye.
| | 00:05 | Welcome to Excel 2007:
Creating and Managing Invoices.
| | 00:10 | In this course, I'll show you how to
set up a flexible and efficient invoice
| | 00:13 | management system in Excel 2007.
| | 00:16 | I will begin by describing the data
you should collect and show you how to
| | 00:19 | design and construct Excel tables that
enable you to combine data from several
| | 00:23 | tables into a single entity.
| | 00:24 | Then we'll show you how to use a
PivotTable to put each customer's information
| | 00:28 | on its own worksheet.
| | 00:30 | If you would prefer to create your
invoices in Microsoft Word, I will show you
| | 00:33 | how to use Word 2007's Mail Merge
capability to transform your Excel data into
| | 00:37 | a series of invoices.
| | 00:39 | Finally, I'll show you a series of
advanced invoice management techniques, such
| | 00:43 | as how to include unpaid items in an
invoice, calculating interest on unpaid
| | 00:47 | items and tracking invoices you create.
| | 00:50 | Let's get started with Excel 2007:
Creating and Managing Invoices.
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you're a premium member of the
lynda.com Online Training Library or if you're
| | 00:04 | watching this tutorial on a disk,
you have access to the exercise files
| | 00:08 | used throughout this title.
| | 00:09 | This is a relatively short course.
| | 00:11 | So I put all of the exercise files
with this title into a single folder.
| | 00:15 | I have that folder on my
Desktop and I named it Exercise Files.
| | 00:18 | If you are a monthly or annual
subscriber to lynda.com, you don't have access to
| | 00:22 | the exercise files, but you can
follow along and create your own resources.
| | 00:27 | Let's get started with Excel 2007:
Creating and Managing Invoices.
| | Collapse this transcript |
|
|
1. Collecting and Structuring Invoice DataCollecting the appropriate data| 00:00 | When you create a data management and
invoice creation system, you need to be
| | 00:04 | sure the workbook tables that
store your data are designed properly.
| | 00:08 | The rules database designers follow when
they create their tables apply directly
| | 00:11 | to this type of system.
| | 00:13 | So I'll summarize those rules here.
| | 00:15 | First, every table you create should
focus on a single business object and
| | 00:19 | contain facts about that object only.
| | 00:21 | For example, a project's table like
the one I have here could contain the
| | 00:25 | Projects ID number, the Project Name,
Customer ID number, who assigned you
| | 00:30 | to the project, the Start Date, the End
Date, and any comments you have in the project.
| | 00:34 | Next, no table should contain,
what's called a repeating group.
| | 00:38 | An example of a repeating group
appears here in this second outline.
| | 00:42 | I have the Project ID and Project Customer
Name and then I have Project1ID and Project2ID.
| | 00:50 | If you try to track a customer's
projects on the same table row, you must create
| | 00:54 | table columns where you can
record each of the projects.
| | 00:57 | Suppose you set aside two columns as I
did here, but the customer wants to hire
| | 01:01 | you for a third project.
| | 01:02 | Obviously, you don't want to delete an
existing project reference and it would
| | 01:06 | be impractical to add a new table column
every time a customer hired you for a new project.
| | 01:10 | In fact, there is no good solution for
tracking multiple items using repeating groups.
| | 01:15 | Instead, I'll show you how to manage
that data by creating separate tables
| | 01:19 | and combining them.
| | 01:20 | Also, every table should have a
column that contains a unique value.
| | 01:25 | The best type of value you can use is an
arbitrary number that's incremented for each new row.
| | 01:30 | This project's table, for example,
assigns the first project the identifier 101,
| | 01:34 | the second 102 and so on.
| | 01:37 | The unique value is called a key
because it distinguishes one table row from
| | 01:41 | all other table rows.
| | 01:43 | If you've ever received the catalog in
the mail and ordered an item by phone
| | 01:46 | or over the web, you're probably
asked for the customer number that was
| | 01:50 | printed on the label.
| | 01:51 | That number is your unique identifier.
| | 01:53 | It goes with you whether you change
addresses, phone numbers or even your name.
| | 01:57 | Finally, it's good practice to give
every table column a descriptive name,
| | 02:01 | preferably one that includes the name
of the table or an obvious abbreviation.
| | 02:06 | For example, this project's table
could include field such as Project ID,
| | 02:10 | Project Name, Project Customer and so on.
| | 02:13 | If you see the field names used outside
the project's table, you know that they
| | 02:16 | came from that table.
| | 02:18 | Designing usable tables for an invoice
tracking system isn't difficult, but some
| | 02:22 | of the guidelines can seem a
little counterintuitive at first.
| | 02:25 | If you follow the practices I've
described above and use the tables in
| | 02:29 | the sample workbook as a guide,
you'll have no trouble creating an
| | 02:32 | efficient, extensible system.
| | Collapse this transcript |
| Structuring the tables| 00:00 | The first and most important step you
should take when creating an Invoice
| | 00:04 | Management System is to
determine what data you need to collect.
| | 00:08 | The most obvious table you
should create is a customer's table.
| | 00:11 | I prefer this structure.
| | 00:13 | Customer ID, the day you've added the
customer, customer's first name, middle
| | 00:18 | name and last name, their company,
if any, and then three lines on which to put
| | 00:24 | their address. Usually this would be
the street address, perhaps a building and
| | 00:28 | then maybe a mail stop, at
least for Unites States addresses.
| | 00:31 | The city, the state or region, the
postal code, the country and then the
| | 00:37 | customer's other contact information.
| | 00:39 | I use three address fields because
some of my clients in the United Kingdom,
| | 00:43 | particularly Wales, have
extremely long addresses.
| | 00:46 | If you think that you might need a
customer address four field, feel free to add it.
| | 00:50 | Also, note that I use the
customer middle name field, instead of a
| | 00:54 | middle initial field.
| | 00:56 | Doing so gives me the space to
store extra names for individuals whose
| | 00:59 | names are longer than the usual American
first name, middle name, and last name pattern.
| | 01:04 | This workbook also contains outlines
for tables that store data about your
| | 01:07 | projects, your products, and the
invoices you've created, payments you've
| | 01:13 | received, orders accepted from customers,
items that were included in those orders,
| | 01:18 | and then also the timecard
worksheet and an expense recording worksheet.
| | 01:24 | The table outlines I've described in
this movie provide a solid foundation for a
| | 01:27 | basic time and invoice management system.
| | 01:30 | In the next movie, I'll show you
how to track orders and order details
| | 01:33 | using Excel tables.
| | Collapse this transcript |
| Combining tables| 00:00 | At first glance, it might seem to
make sense to record every detail of an
| | 00:03 | order in a single table.
| | 00:05 | As it turns out, it's easier from an
information management perspective to put
| | 00:09 | the data in two separate tables.
| | 00:11 | In this movie, I'll show you how to
create those two tables and use them to
| | 00:14 | track your client's orders.
| | 00:16 | In this case I have an Order table
and I have an OrderDetails table.
| | 00:21 | The Order table keeps the overview
information about a particular order.
| | 00:25 | In this case we have the
identification number, the ID number of the customer
| | 00:29 | who place the order, the project, if any,
to which the order was assigned, and
| | 00:34 | the date the order was received.
| | 00:36 | The OrderDetails worksheet contains
information about the items that were
| | 00:40 | part of each order.
| | 00:42 | This table contains the OrderDetailID
number, which is simply the number of
| | 00:45 | the row in the table.
| | 00:47 | The OrderID, which is the number of
the order back here in the Orders table.
| | 00:52 | Here's OrderID and then here is that
number again indicating that this row
| | 00:57 | belongs to that particular order.
| | 01:00 | Then we have the product
and this is the Product ID.
| | 01:03 | It was Product #1. The quantity or
the number of items that were ordered,
| | 01:08 | individual price and the total, which is
simply the price multiplied by the quantity.
| | 01:13 | The reason you have separate tables
is so you don't have to reenter the
| | 01:16 | customer ID, project and date over
and over, which takes time and add some
| | 01:21 | potential for data entry errors.
| | 01:23 | Also, splitting these tables into two
separate entities enables you to retain
| | 01:27 | information that would have been lost
if the tables were combined into one.
| | 01:31 | Suppose a customer placed an order
for one item but then canceled the order.
| | 01:35 | If you store the order summary and
order details on the single table, you can
| | 01:39 | either delete the order entirely,
which erases all record of the contact or
| | 01:43 | delete the order items for that order
and maintain a record of the contact.
| | 01:46 | It's much easier to retain the order
and delete the Order Items from a separate
| | 01:50 | table, so that you can
maintain a record of the contact.
| | 01:53 | It could be tempting to store
several types of data in the same table but
| | 01:56 | it's more efficient to store the data in
separate tables and combine them when needed.
| | 02:00 | Because the tables share the Order ID
field, you can use that shared field to
| | 02:04 | combine values from the Orders and
OrderDetails table into a single table.
| | 02:09 | You'll see how that comes in handy
in a later movie, when we get ready to
| | 02:12 | summarize invoice data using a PivotTable.
| | Collapse this transcript |
| Tracking time using an Excel table| 00:00 | If you visit office online or look in
the Excel program, you will find a series
| | 00:04 | of time card templates that you can
fill out and either print or email to an
| | 00:07 | admin for entry into your
company's time tracking system.
| | 00:10 | I will show you a few of them.
| | 00:11 | Just go under here, click New and
then, under Microsoft Office Online, click
| | 00:18 | Timesheets to show the Time sheet templates.
| | 00:20 | So, we have the weekly timesheet with
breaks, a daily, weekly and monthly and
| | 00:24 | yearly timecard, biweekly timesheet and so on.
| | 00:27 | I will just pull the biweekly
timesheet up and create a workbook based on it.
| | 00:33 | So, you see, it has spaces for regular
hours, overtime hours and also space to
| | 00:38 | enter your time over two weeks.
| | 00:41 | Under no circumstances should you
use this template or any of the others.
| | 00:44 | They are designed for the paper-based world.
| | 00:47 | It's much more efficient to enter your
time into an Excel table and use Filters
| | 00:50 | to figure out what you want.
| | 00:52 | Rather than use any of those templates,
I have created a simple Excel table that
| | 00:56 | you can use to track your time.
| | 00:58 | We have the TimeID which is simply
the row in the table, the ClientID, the
| | 01:03 | person for whom you are doing the work,
the Project number, the Date on which
| | 01:07 | you did the work, the number of
hours you worked, and your BillingCode.
| | 01:11 | Your BillingCode refers to
items in the Products table.
| | 01:16 | So for example, if you were to do onsite
consulting, you would have a price of $120.
| | 01:21 | If you do remote consulting,
you might charge $100 an hour.
| | 01:24 | So, the ProductID, which, in this case,
is the TimeID, would be 3, at $120 an hour
| | 01:30 | or 4, at $100 an hour.
| | 01:32 | So, now let's go back to the TimeCard.
| | 01:35 | The TimeRate will contain a formula that
will use the TimeBillingCode to look in
| | 01:39 | the products table and pull out the proper rate.
| | 01:42 | And finally TimeTotal, would calculate
the number of hours worked, multiplied
| | 01:47 | by the payment rate.
| | 01:48 | So, I will just put in a few entries here.
| | 01:50 | My first client would be Client 184
and the project would be 101, the date
| | 01:55 | 10/15/2009, number of hours, I worked 3.5
hours, and my BillingCode, this was onsite
| | 02:04 | consulting, which I bill at $120/hour. That's 3.
| | 02:07 | I remember that from having looked at
the table. And the TimeRate, I look up
| | 02:12 | using a Vlookup formula.
| | 02:13 | We have the Lookup value, Cell F4 and
we are looking in the Products table and
| | 02:22 | the column that we are looking for
in the other table is column number 4.
| | 02:26 | I do want an exact match. I will
click False, so that I get an exact match.
| | 02:31 | Type the right parenthesis, the
formula looks correct and press Tab.
| | 02:36 | So, my TimeRate is 120.
| | 02:38 | And, just for consistency, I will
make that an Accounting field and now my
| | 02:44 | TimeTotal is =e4*g4 and there we have it.
| | 02:52 | When I press Tab, Excel
created a new row in the table.
| | 02:55 | You can see that I have a new TimeID
of 2, which indicates this is the second
| | 03:00 | row on the table and because there are
no values, in either Cell F5, or E5, or G5,
| | 03:10 | you get these error messages here.
| | 03:11 | As soon as you fill in this row on the
table, those error messages will go away.
| | 03:15 | Now, I'll just throw in a few more
entries. They will all be for the same
| | 03:20 | project, I did. 10/16/2009,
4 hours, same BillingCode.
| | 03:25 | I will make this Accounting. There we are.
| | 03:28 | 3, 184, 101 and let's
say this one was 11/1/2009.
| | 03:36 | I worked 8 hours, BillingCode of 4,
meaning it was onsite, which has a rate
| | 03:40 | of 100 and because I worked 8
hours, that means I make $800 that day.
| | 03:44 | Let's say that I want to filter this
table, so that I can see how long I worked
| | 03:47 | during a particular time period.
| | 03:49 | So, for example, if I wanted to see
how much I worked during the month of
| | 03:53 | October, I could click TimeDate and then
you see that I have the month of October here.
| | 04:01 | I clear the checkbox next to November,
click OK and I see only those entries
| | 04:06 | from October of 2009.
| | 04:09 | But I don't have a total, I
only have a subtotal for each line.
| | 04:12 | If I want to add a total to the table,
I click any cell in the table, go to the
| | 04:17 | Design tab and click Total Row
and I see that in October I worked
| | 04:22 | seven and half hours and made a total of $900.
| | 04:28 | Time tracking in Excel is easier than it seems.
| | 04:31 | Rather than go through all the
intermediate steps required to move from a paper
| | 04:34 | solution to a computer, input your
hours directly into an Excel table, and use
| | 04:37 | it to analyze your earnings.
| | Collapse this transcript |
| Tracking reimbursable expenses| 00:00 | When you work on a client's behalf,
you will often incur incidental expenses
| | 00:04 | that you can bill back to the client.
| | 00:06 | Tracking those expenses in an Excel
table is a straightforward process.
| | 00:09 | The first thing you should do is
create the table and ensure you have all of
| | 00:13 | the columns you need.
| | 00:14 | I prefer to use these.
| | 00:16 | First, I have the Expense ID number and
that is simply at the row in the table.
| | 00:22 | I have the date the expense was incurred.
That should usually be the date on the receipt.
| | 00:27 | The client identification number, the
project, if any, the total and any notes
| | 00:33 | that you might want to add.
| | 00:34 | You should always be sure to add notes
about each expense to help you remember
| | 00:38 | what it is so if the client asks
any questions, you can tell them.
| | 00:41 | Attempting to explain a $500 charge
two months after you made it can be
| | 00:45 | embarrassing if you didn't take proper notes.
| | 00:46 | Now one technique I would like to show
you is how to add an incremented number
| | 00:52 | to each new row in the table.
| | 00:54 | I'll show you here in the Expense ID field.
| | 00:56 | So let's say that I wanted to have
my first expense be expense #1.
| | 01:01 | I can create a formula that will put the
#1 here and then have Excel incremented
| | 01:07 | each time I add a new row to the table.
| | 01:09 | To do that I type equal row and then
the cell ID number. That is cell A4,
| | 01:16 | it's the current cell.
| | 01:17 | So what this will do in its current form
is return the number 4, because that is
| | 01:22 | the row in which the cell resides.
| | 01:24 | So if I press Return, I'll get the #4,
but because I want to start from the #1,
| | 01:29 | I want to subtract 3 from that value.
| | 01:33 | So -3, Tab and I get the #1.
| | 01:37 | And let's say the date was 12/15/2009.
| | 01:41 | Client was #1824, Project 101, and the
total was $45 and it was for parking.
| | 01:48 | Now I haven't hit Tab yet to create a
second row, but when I do, I want you to
| | 01:53 | notice what happens in the Expense ID field.
| | 01:57 | What happened was that when I press Tab
and Excel created the new table row,
| | 02:02 | it added the formula that
was in cell A4 to cell A5.
| | 02:07 | Also notice that Excel updated the
formula to reflect its new location.
| | 02:12 | In other words, the formula here
in cell A4 uses a relative reference.
| | 02:17 | In other words, I don't have any dollar
signs in front of the A or in front of the 4.
| | 02:21 | So when Excel copied the formula here to
cell A5, it updated the reference to cell
| | 02:26 | A5, but it kept -3 the same.
| | 02:29 | The row for A5 is #5, as you can see over here.
| | 02:34 | Subtracting 3, you get Expense ID #2.
| | 02:37 | Tracking reimbursable expenses is one
of those tasks we all know we should do,
| | 02:41 | but we don't always make the time for.
| | 02:43 | I like to set aside half-an-hour every
Friday to enter my expenses and to enter
| | 02:48 | any other business related
credit card charges and checks.
| | 02:51 | Taking a little time every week will
save you lots of time at the end of the year.
| | Collapse this transcript |
|
|
2. Creating Invoices Using Excel 2007Creating a PivotTable report from Excel table data| 00:00 | PivotTables are powerful and
flexible Excel analysis tools.
| | 00:04 | A PivotTable lets you rearrange, sort,
and filter a data set on the fly so you
| | 00:09 | can analyze it from several different
perspectives and with a minimum of effort.
| | 00:12 | In this movie, I'll show you how to
combine data from several tables into
| | 00:16 | a single Excel table.
| | 00:17 | You can then use the data from that
larger table to create a PivotTable.
| | 00:21 | So the question is in the Invoice
Management System that I've given you, which
| | 00:24 | Excel table do you want to
use to create your PivotTable?
| | 00:27 | None of them work so far but you can
add columns to the OrderDetails table to
| | 00:31 | create what you need.
| | 00:32 | The first thing you should do, however,
is copy the worksheet that contains
| | 00:35 | the OrderDetails table and then rename it and
add other data to the table that it contains.
| | 00:41 | So I will right-click the
OrderDetails worksheet tab. Click Move or Copy.
| | 00:47 | I just want to move this
worksheet behind OrderDetails.
| | 00:51 | That means I want to put it
before the TimeCard sheet.
| | 00:55 | I want to create a copy and when I'm
ready, click OK and I have a new worksheet
| | 01:00 | called OrderDetails (2).
| | 01:03 | I'll change the name to Consolidated,
press Return and now I can click in the
| | 01:09 | table and rename the table.
| | 01:11 | Right now, it's OrderDetails6 but
instead I will change it to InvoiceData.
| | 01:18 | Now that I've renamed the worksheet
and the table, I can start adding new
| | 01:22 | columns to the table.
| | 01:23 | The columns I want to add are the
Order Customer ID, OrderProject, OrderDate,
| | 01:34 | ProductName, and ProductType.
| | 01:38 | These are all fields that I want to use to
analyze the data once I create my PivotTable.
| | 01:43 | Now that I've created the columns,
I can create my formulas that will look up
| | 01:46 | the data in the other tables.
| | 01:48 | So for example, in this case,
I want to have the OrderCustID.
| | 01:52 | I can find that information by
looking back in the Orders table.
| | 01:56 | The second column of which has the OrderCustID.
| | 01:59 | So when I go back to the Consolidated,
I can create a VLOOKUP formula that takes
| | 02:03 | data from this table and
looks it up in the other table.
| | 02:08 | The data that I'm going to be using for
the lookup is the OrderID, which is here
| | 02:13 | in Column B. So the formula
for the OrderCustID is VLOOKUP.
| | 02:20 | I'm drawing my data, the
lookup value, from cell B4.
| | 02:24 | The table from which I'm going to
draw the data is Orders and the data I'm
| | 02:29 | bringing in is in the second
column of that table. So Column 2.
| | 02:33 | I want an exact match, so I'll use FALSE,
right parenthesis, and Excel displays
| | 02:39 | the Customer ID for each of these Order items.
| | 02:43 | I can do the same thing
for the OrderProject lookup.
| | 02:45 | So just =VLOOKUP, the value.
| | 02:49 | Once again, this is in cell B4.
| | 02:51 | It's the Orders table.
| | 02:53 | And the data that I want is
in column 3 of that table.
| | 02:57 | I want an exact match, so I'll select FALSE.
| | 03:00 | Close out the formula and there we go.
| | 03:02 | Anytime we have a zero that
means that there is no project.
| | 03:05 | Do the same thing for the date.
| | 03:06 | =VLOOKUP, lookup_value B4,
the Orders table, Column 4.
| | 03:15 | In this case, Excel
brought in the dates as numbers.
| | 03:18 | This is how Excel manages
dates internally, as a number.
| | 03:21 | So to make it into a human readable
number, I can just select the entire column
| | 03:25 | in the table and select
the date format that I want.
| | 03:30 | In this case, I'll go for
Short Date and the dates appear.
| | 03:33 | Now I have to look up in the Products
table what the names of the products are
| | 03:36 | and then also the ProductType.
| | 03:40 | So to lookup the ProductName, I created
another VLOOKUP formula, VLOOKUP, and
| | 03:45 | I want the ProductID, which is here
in cell C4, the OrderDetailProduct.
| | 03:51 | These numbers represent
the product that was ordered.
| | 03:53 | So I type in cell C4, looking in the
Products table, comma,and then I want the values
| | 04:01 | from the second column in that table.
| | 04:04 | I want an exact match,
press Tab, and there it is.
| | 04:08 | And finally, I can do the
same things for the ProductType.
| | 04:11 | =VLOOKUP. The value that we're looking up.
| | 04:14 | Once again, that's in C4,
the ProductID. The table is Products.
| | 04:18 | The column that we're looking for
in the Products table is column 3.
| | 04:22 | That's the one that has the ProductType.
| | 04:23 | I want an exact match and there we have it.
| | 04:27 | Now that I have all of my data into this
table, I can use it to create a PivotTable.
| | 04:31 | To do that, I click any cell in the
table, which I've done and then on the
| | 04:35 | Insert tab, click PivotTable.
| | 04:37 | I'll verify in the dialog box that
the InvoiceData table, this one here, is
| | 04:41 | selected and that I want to create
the PivotTable on a new worksheet.
| | 04:45 | I prefer to create a PivotTable on its
own worksheet so it doesn't crowd any
| | 04:49 | other data that might be around.
| | 04:51 | So with that information in place, I can
click OK and Excel creates my PivotTable.
| | 04:56 | Now that I've created the PivotTable,
I can start adding fields to it.
| | 05:00 | In this case, for my Invoices, I want
to have the Customer ID, the name of the
| | 05:04 | product and the product's price.
| | 05:06 | The PivotTable will do the math for
me and add up the totals for all of the
| | 05:10 | products in the Orders.
| | 05:11 | So for this, I will go to the OrderCustID,
add it to the Rows, get the Products,
| | 05:17 | add it to the Row Labels and then for
the data field, I will add the ItemPrice.
| | 05:23 | Now that I've created this PivotTable,
I can filter the data to create a
| | 05:26 | separate worksheet for each customer.
| | Collapse this transcript |
| Dividing data by customer using report filters| 00:00 | In Excel 2007, you can create separate
worksheets, which contain PivotTables for
| | 00:05 | each of your customers.
| | 00:06 | To do that, you need to use
what's called a Report Filter.
| | 00:09 | The Report Filter field is
here in the PivotTable field list.
| | 00:13 | If you want to add a field to the
Report Filter area, you can drag it either
| | 00:16 | from the list up here or from one of the
existing area such as Column Labels or Row Labels.
| | 00:22 | In this case, I want to use the
Customer ID, so I will drag it from the Row
| | 00:26 | Labels area to the Report Filter area.
| | 00:29 | Now that it's there, it no longer affects the
organization here in the body of the PivotTable.
| | 00:34 | But instead, if I wanted to, I could
filter the values within the PivotTable.
| | 00:38 | So if I only wanted to see the items for
CustomerID #1824, I could filter and click OK.
| | 00:44 | To remove the filter, just go up
here click All and they come back.
| | 00:49 | Now that I have a field, in this case
the Customer ID field, in the Report
| | 00:53 | Filter area, I can use it to
create my separate PivotTables.
| | 00:56 | To do that, on the Options contextual tab
of the Ribbon click the Options button
| | 01:01 | and then click Show Report Filter Pages.
| | 01:03 | When I do, Excel displays a list of
the fields that are available in the
| | 01:07 | Report Filter area.
| | 01:08 | In this case, there is only one.
| | 01:10 | So make sure it's selected and
click OK and Excel creates my
| | 01:14 | separate worksheets.
| | 01:15 | In this case, I have a PivotTable that
is already filtered to only display those
| | 01:19 | items ordered by Customer ID #1824.
| | 01:23 | It's here on this worksheet, also called 1824.
| | 01:26 | The same thing occurred for
CustomerID #1825, which is here with the
| | 01:30 | separate PivotTable.
| | 01:32 | So if I were to click this arrow, you'd
see that I would be able to filter it,
| | 01:35 | but it is preset to a filter for this customer.
| | 01:38 | If there had already been a
worksheet named 1825 or 1824, you would see a
| | 01:44 | worksheet with the name 1824
followed by parenthesis and then the number 2.
| | 01:49 | So in other words, it would be 1824
and then 2 surrounded by parenthesis.
| | 01:53 | Now that you've created separate
worksheets for each of your customers, you can
| | 01:57 | add those customers information.
| | 01:59 | I'll show you how to do that in the next movie.
| | Collapse this transcript |
| Adding customer information to invoices | 00:00 | Now that you've created the invoices,
which are these two worksheets, you can
| | 00:04 | add customer information once
again by using VLOOKUP formulas.
| | 00:07 | You can add the data to all of the
worksheets at the same time by clicking
| | 00:11 | one of the worksheets tabs, holding down
the Ctrl key and clicking the other worksheet.
| | 00:15 | If there had been multiple worksheets,
say for example that if there had been
| | 00:19 | four or five, you can click the first
one in line, in this case 1824, hold down
| | 00:23 | the Shift key and then select the
last sheet tab that you want to include.
| | 00:27 | So in other words, if there were
three or four worksheets between 1824 and
| | 00:30 | 1825, holding down the Shift key and
clicking 1825 would have included all of
| | 00:35 | those worksheets as well.
| | 00:37 | Now that those two worksheets are
selected and Excel indicates that's true
| | 00:41 | with the term 'Group' on the title
bar after the file name, you can start
| | 00:45 | adding the information.
| | 00:46 | So let's say that I have the Customer Name.
| | 00:51 | I can create a formula that will add their name.
| | 00:53 | In this case I do VLOOKUP and the cell that
contains the value I am looking up is the cell B1.
| | 00:59 | That's the one that has the Customer ID of 1824.
| | 01:02 | The table_array is Customers, column
index is 3, and I want an exact match.
| | 01:09 | I will press Return just so
you can see what it looks like.
| | 01:13 | It will return only the first name.
| | 01:15 | If I want the entire customer name,
I need to add something to that formula.
| | 01:18 | Actually, I need to add several somethings.
| | 01:19 | So to do that, I will type an
ampersand, which in Excel is the
| | 01:24 | concatenation character.
| | 01:26 | It will add whatever I add to the formula here.
| | 01:29 | So to do that, I will add a space,
which must be in double quotes because it is
| | 01:34 | a literal string, so it's
just quote, spacebar, end quote.
| | 01:37 | I type the & symbol again and now
I look up the customer's middle name.
| | 01:44 | Again, I am working off of
cell B1, the Customers table.
| | 01:48 | This time, it's column 4
and I want an exact match.
| | 01:51 | Add another space using the same
technique, & " " &, and this will be the
| | 02:00 | last bit where I look up
the customer's last name.
| | 02:03 | Again, lookup_value in B1, Customers
table, table column is 5, I want an exact
| | 02:10 | match and when I type right parenthesis
and press Return, I get the customer's
| | 02:14 | entire name and now I can add
the customer's company, if any.
| | 02:17 | So I will type the company label,
then =VLOOKUP, cell B1 again, looking in the
| | 02:23 | Customers table, column 6, I want an
exact match and there I have the result.
| | 02:28 | In this case, there is no company for
customer name, John Q. Smith, but I don't
| | 02:33 | want this zero appearing in the invoice.
| | 02:35 | I can get rid of that by
changing an Excel option.
| | 02:38 | So I click the Office button, go to
Excel Options and then on the Advanced tab,
| | 02:44 | I can scroll down to the Display
options for this worksheet group, and
| | 02:49 | clear the Show a zero in
cell that have zero value.
| | 02:52 | I clear that option, I click OK,
and Excel hides the formula result.
| | 02:57 | Now I can add the address.
| | 03:00 | Again, VLOOKUP, B1, Customers, we are
looking in column 7, exact match, Return,
| | 03:08 | and then rather than putting in
Address 2 and Address 3, I would just make it
| | 03:11 | like an address block, such as you
would see on a mailing such as a catalog.
| | 03:16 | So I can type in =VLOOKUP, B1, table of
Customers, column 8, exact match, Return.
| | 03:24 | Do the same thing for row 9,
which is my third address field.
| | 03:28 | VLOOKUP, B1, Customers,
column 9, exact match, Return.
| | 03:35 | Now I can do the city.
| | 03:37 | This is in column 10.
| | 03:44 | State, which is in column 11, and
the postal code, and there we have it.
| | 03:58 | Now that I have made my changes,
I can click any sheet tab other than the
| | 04:01 | ones that are selected.
| | 04:02 | So for example if I click Orders,
it releases the selection down here.
| | 04:07 | So I can see that I have all of my data
for customer 1824 on that worksheet and
| | 04:12 | I have the data for
customer 1825 on this worksheet.
| | 04:15 | Now that I have that information in
place, I can add the invoice number.
| | 04:19 | So for 1824, which is the first invoice,
I will put an invoice number
| | 04:24 | and this will be Invoice #5 and I knew that
because that is the next invoice in the sequence
| | 04:29 | in my invoice's table.
| | 04:30 | I press Return and I can go to 1825 and
this will be Invoice #6. I don't want to
| | 04:40 | keep these two worksheets in this workbook.
| | 04:42 | These two worksheets contain
information that is going to be mailed out to a
| | 04:45 | particular customer for a particular time.
| | 04:47 | This could be a monthly invoice.
| | 04:48 | Perhaps a weekly invoice depending
upon the way you bill your customers.
| | 04:52 | So what I want to do is move these
two worksheets to a new workbook.
| | 04:56 | To do that, I select the worksheets again
using either the Ctrl or the Shift method.
| | 05:01 | I Ctrl+clicked.
| | 05:02 | Now I can right-click either of the
grouped sheets tabs, I click Move or Copy
| | 05:08 | and select a new book.
| | 05:09 | I do not want to create a copy.
| | 05:12 | I want to move them.
| | 05:13 | So I when I click OK,
Excel creates a new workbook.
| | 05:16 | You can see that these two worksheets
are the only worksheets in that workbook.
| | 05:19 | Now that I have these two worksheets
in the new workbook, I need to make one
| | 05:23 | final change to remove the link from
these workbooks to the previous workbook.
| | 05:28 | In other words, when I copy these
worksheets over, I also copied over the formulas.
| | 05:33 | So what I need to do is remove that
link. Otherwise I will get errors.
| | 05:37 | To do that, I select all of the
worksheets again by Ctrl+clicking or
| | 05:41 | Shift+clicking the tabs and then to
select every cell within each worksheet I
| | 05:46 | click the Select All button.
| | 05:47 | It's here at the top left
corner of the worksheet.
| | 05:50 | Now on the ribbon, I can click Copy.
Click the Paste button's down arrow
| | 05:56 | and click Paste Values.
| | 05:57 | When I do, I am going to
press Escape to lose the marquee.
| | 06:00 | I have all the data, but I have
no links to the previous workbook.
| | 06:04 | Now I can save it as December
Invoices and work with the data normally.
| | 06:11 | Now you are ready to include this data
in invoices you create either in Excel
| | 06:15 | or in Microsoft Word.
| | Collapse this transcript |
|
|
3. Creating Invoices Using Mail MergeCreating invoices with mail merge| 00:00 | If you prefer to manage your
invoices in Word, you can do so using the
| | 00:04 | Mail Merge feature.
| | 00:05 | After you create your base document,
you can add your customer's information.
| | 00:09 | However, because you are analyzing
your data in Excel, you will need to copy
| | 00:13 | your invoices from the
PivotTables from Excel into Microsoft Word.
| | 00:17 | When you create the invoices using
Microsoft Word, it's most helpful to use the
| | 00:20 | Mail Merge feature, which allows you to
create individual letters, envelopes, or
| | 00:25 | postcards for individual customers,
based on entries in a separate list.
| | 00:30 | To begin a Mail Merge, you go to the
Mailings tab of the ribbon and then
| | 00:34 | click Start Mail Merge.
| | 00:35 | We are going to be doing Letters,
so that's what I will select.
| | 00:38 | Now, we can select the recipients.
| | 00:41 | In this case we will use an
existing list, which is available in the
| | 00:46 | Exercise_files folder on my Desktop.
| | 00:49 | The workbook I want to use is named
CustomerAddresses, so I will click it and
| | 00:53 | click Open and I do want to use the
Customers$ table, which is the first one and
| | 00:58 | it is highlighted, so I can click OK.
| | 01:00 | And now I am able to start adding the fields.
| | 01:03 | Now, in many cases, you would have
to add individual fields, such as the
| | 01:07 | various address lines and so on, but
instead in Excel 2007, you can add what's
| | 01:11 | called an Address Block.
| | 01:13 | To do that, you just click the button of
the same name and you start identifying
| | 01:18 | which fields should be included in the block.
| | 01:20 | To do that, down to Match Fields.
| | 01:23 | So here are the items that you can include.
| | 01:25 | We have the First Name, which is CustFName.
| | 01:29 | Last Name, CustLName.
| | 01:31 | We don't have a Suffix, at least not
entered in the data fields we are using.
| | 01:34 | Then we have the Company,
which is CustCompany. Address 1.
| | 01:39 | Same thing and then Address 2.
| | 01:41 | Now remember, in our table we have three
address fields, and even though we only
| | 01:45 | go up to Address 2 in the required
group, in the Optional information group,
| | 01:50 | there is an Address 3 field.
| | 01:52 | So, if we need it, it will appear.
| | 01:54 | Just scroll down to the bottom, select
customer Address 3, and then scroll back up
| | 01:58 | and we can add our remaining fields.
| | 02:00 | In this case, that's the City,
the State and the Postal Code.
| | 02:05 | All of our mailings in this case are
going out within the United States, so we
| | 02:09 | don't need to add Country or Region.
| | 02:11 | Everything looks good. We can click OK.
| | 02:13 | And Word displays a preview of
what the Address Block will look like.
| | 02:17 | For number 1, it's John
Smith from Ventura, California.
| | 02:20 | And then for number 2, we have
Jane Smith from Portland, Oregon.
| | 02:23 | Again, everything looks
fine. So I can click OK.
| | 02:27 | And here, instead of the individual
address elements, it just gives us the
| | 02:31 | Address Block, which is defined
elsewhere within the document.
| | 02:35 | If I want, I can preview what my
documents will look like when they are printed.
| | 02:38 | So I can preview the results and then
I am currently on Recipient 2. We only
| | 02:42 | have two in this particular example, and
if I want to look at 1, I can click the
| | 02:48 | Back button and display 1
again. Same thing with 2.
| | 02:52 | Everything looks good, so
I can click Finish & Merge.
| | 02:56 | Now, I don't have the billing
information for each customer.
| | 02:59 | In other words, what I am going to
charge them for, within the documents.
| | 03:02 | So I will need to edit the individual
documents as opposed to printing them or
| | 03:06 | sending them as email messages.
| | 03:08 | So I will click Edit Individual Documents.
I will merge all of the records and click OK.
| | 03:15 | So now instead of previewing, I
have an actual document called Letters1
| | 03:19 | that contains a page with a letter
for John Smith, Customer 1, and a page
| | 03:25 | for Jane Smith, Customer 2.
| | 03:26 | Now, I can go back into
Excel and copy over their data.
| | 03:30 | I will just select the actual invoicing
data, because the customer address and
| | 03:39 | everything else are already there as
part of the address block, Copy, go back
| | 03:45 | over to Word, Paste and there it is.
| | 03:47 | I can go down to the second invoice for
my second customer. Click where I want it
| | 03:53 | to go, go to sheet 1825,
Copy, switch back and Paste.
| | 04:03 | Now that I have created the invoices,
I can save the file and I will call it
| | 04:08 | Invoice Merge Complete.
| | 04:13 | Mail Merging is one of Word's
most powerful tools for businesses.
| | 04:16 | After you've learned how to use it,
you will be able to create invoices
| | 04:19 | quickly and efficiently.
| | 04:21 | If you are able to do any programming
in Visual Basic for applications,
| | 04:24 | you can perform these actions even more
quickly by automating the Copy and Paste
| | 04:28 | from Excel to Word.
| | Collapse this transcript |
| Filtering the invoice table to find current items| 00:00 | When you generate an invoice for a
customer, you don't want to run an invoice
| | 00:04 | for every order they've ever made.
| | 00:05 | Instead, you want to create a new invoice for
just the new orders within a given time period.
| | 00:10 | In this movie, I will show you how to
filter Excel tables and you can use those
| | 00:13 | skills to filter PivotTables as well.
| | 00:16 | In this case, I have my Invoice table
and I have all of the invoices that I have
| | 00:21 | created and also any dates
that they might have been paid.
| | 00:25 | Now, let's say that I wanted to
filter my table so that I only see invoices
| | 00:29 | that were due in the first half of 2009,
that is from January 1, 2009, to June 30, 2009.
| | 00:36 | To do that, I can click the
InvoiceDueDate column's down arrow, point to Date
| | 00:41 | Filters and click Between.
| | 00:45 | Now, I can create my dates between which
I want to see the data, so for the Date
| | 00:50 | Picker I will go back to 1/1/2009 and
for here, I will just type in 6/30/2009.
| | 01:02 | Everything looks good. Click OK.
| | 01:04 | And I see those two invoices.
| | 01:06 | To remove the filter, I can click the
Filter down arrow and click Clear Filter.
| | 01:12 | If I wanted to see a specific invoice or
invoices for a specific customer, I can
| | 01:16 | do something similar.
| | 01:18 | I can create what's called a Selection Filter.
| | 01:20 | To do that, I can click the InvoiceCustomer
column's filter arrow, clear Select All,
| | 01:26 | and then select the
customer that I want to see.
| | 01:28 | In this case, it could be 1824,
click OK, and the table is filtered.
| | 01:33 | Again, to remove the filter, click
the Filter arrow and click Clear Filter.
| | 01:38 | That type of filter also works if you
have a column that contains any blank values.
| | 01:42 | If you want to do a Selection Filter,
such as for InvoicePaid, so you can see
| | 01:46 | any invoices that have gone unpaid, you
can click the down arrow, clear Select All,
| | 01:51 | and click Blanks.
| | 01:54 | When you do, only the unpaid items appear.
| | 01:57 | Limiting the data that appears in
your Excel table helps you target your
| | 02:00 | invoicing to just the current
period and the customers you want.
| | Collapse this transcript |
|
|
4. Advanced Invoice HandlingCalculating interest charges on unpaid items| 00:00 | Anyone who has been in business for
any length of time can tell you that your
| | 00:03 | customers don't always pay on time.
| | 00:05 | So it's a good idea to keep a measure of
how long a particular invoice is overdue.
| | 00:10 | I like to have those fields right in
the Invoices table where I can see them.
| | 00:14 | In this case, I will create an
InvoiceOverdue field and when I type the
| | 00:21 | heading next to the table headings and
press Enter, Excel 2007 adds that new
| | 00:26 | column to my table.
| | 00:27 | Now, I can add a formula to cell F4
to calculate the amount of time that
| | 00:32 | this invoice is overdue.
| | 00:33 | That formula is =IF, and then I will
create the Logical Test, so for this it
| | 00:39 | is IF E4 equals blank, in other
words, if there is no value in cell E4,
| | 00:45 | indicating that the invoice is unpaid, then I
want to find the integer value of today's date.
| | 00:54 | And then subtract the date
that the invoice was due.
| | 00:57 | So, again I am subtracting the
invoice due date from today's date.
| | 01:01 | If the invoice has been paid, then I
want Excel to display the value 0, which
| | 01:06 | indicates that no interest will be owed.
| | 01:08 | Right parenthesis to close the formula,
press Enter, and we can see that the
| | 01:12 | first invoice is 299 days
overdue and the second is 244.
| | 01:18 | Now, I am ready to calculate interest.
| | 01:20 | First, I will create a named
formula that has my interest rate.
| | 01:25 | That way I don't have to go into each and
every formula within a table and change it.
| | 01:29 | To create that named formula, I will
go to Formulas, and click Define Name.
| | 01:34 | The name that I will create is InterestRate
and say that it refers to the value of 12%.
| | 01:43 | When I am done, click OK.
| | 01:44 | And I have defined the value.
| | 01:45 | Now, I can create a new column
to calculate the interest due.
| | 01:50 | For that, I will type in the name,
press Return, Excel adds the column to my table,
| | 01:55 | and now I can type in the
formula to calculate the interest and
| | 02:00 | that formula is equal C4, which is the
amount of original invoice, multiplied by the
| | 02:06 | compounded interest rate, which is
calculate using the formula 1 plus interest rate,
| | 02:11 | divided by 365, which means it
will be compounded daily, raised to the
| | 02:17 | power of the value in F4.
| | 02:20 | In other words, we have a daily
interest rate, which we got by dividing the
| | 02:23 | normal interest rate by 365 and then
we are calculating interest based on the
| | 02:28 | number of days overdue, which is found in F4.
| | 02:31 | When I am done typing in the formula,
I can press Enter and Excel adds the values.
| | 02:36 | I want them to be displayed as currency values.
| | 02:39 | So I click Home and change
it to the accounting format.
| | 02:44 | After you calculate how many days
overdue the payment is, you can calculate
| | 02:48 | the interest due on that payment and include
the interest in a message to your customer.
| | 02:52 | It's difficult to manage a system where
you add unpaid items to current invoices.
| | 02:57 | So you should consider moving outside
the invoicing system and use letters to
| | 03:00 | communicate with customers who
are behind in their payments.
| | Collapse this transcript |
| Voiding invoices| 00:00 | One of the most basic accounting
principles is that you never delete a transaction.
| | 00:05 | Instead of removing invoices from the
table when a customer cancels an order,
| | 00:09 | you can make them void and then use the
contents of the void field to filter the
| | 00:12 | table to show only active invoices.
| | 00:15 | In this case, I will need to create a
new field that keeps track of whether an
| | 00:19 | invoice is void or not.
| | 00:20 | So, to do that, I will click in the
cell beside the right-most table column and
| | 00:27 | name it InvoiceVoid.
| | 00:30 | When I press Enter, Excel
adds that field to the table.
| | 00:34 | The best way to indicate that an invoice
has been voided is to add the word Void
| | 00:38 | to the appropriate table column.
| | 00:40 | So in this case, if customer 1825
decided to cancel this order, I can change it
| | 00:45 | so that the invoice is marked as void.
| | 00:48 | To do that, I will
just type in the word Void.
| | 00:51 | Now I can filter my table to only
show those invoices that are active.
| | 00:55 | To do that, I will click the InvoiceVoid
column's filter arrow, clear Select All,
| | 01:00 | and Display only those rows
where this field is blank.
| | 01:04 | When I am ready to bring it back, I can
click the filter arrow and click Clear Filter.
| | 01:09 | By contrast, the best way to indicate
an invoice is active is to do nothing.
| | 01:14 | It can be difficult to discern between
two words in a spreadsheet, especially
| | 01:18 | in a crowded data table, so a
blank cell is the best option.
| | 01:21 | Finally, note that the presence or
absence of a date in the InvoicePaid field
| | 01:25 | indicates whether an
invoice has been paid or not.
| | 01:27 | You don't need to use anything in the
InvoiceVoid field to indicate whether
| | 01:31 | payment has been received or not.
| | 01:33 | Voiding invoices maintains a
record of your customer interactions.
| | 01:36 | If a customer consistently changes
their mind and you end up voiding a lot of
| | 01:40 | their invoices, you might
want to fire them as a customer.
| | Collapse this transcript |
|
|
5. Printing InvoicesPrinting invoice worksheets| 00:00 | After you've created an invoice workbook, you
should print it and mail it out immediately.
| | 00:04 | Also, when you print an invoice
created on an Excel worksheet, you should
| | 00:08 | strongly consider hiding the
worksheet's gridlines when you print.
| | 00:11 | If you want to separate worksheet
elements from each other, you can use Color
| | 00:14 | and Cell Borders to do so.
| | 00:16 | First thing is to hide the gridlines.
| | 00:18 | To do that, you go to the View
tab on the Ribbon and clear the
| | 00:21 | Gridlines checkbox.
| | 00:23 | Now you can print your selected worksheets.
| | 00:25 | If you wanted to print just the first
worksheet, in this case 1824, you can
| | 00:29 | click at the Sheet tab and then press
Ctrl+P to display the Print dialog box.
| | 00:35 | If you want to print both of your
worksheets or multiple worksheets, you can
| | 00:38 | select those Sheet tabs using either
the Ctrl key which allows you to select
| | 00:43 | worksheets individually or you can use the
Shift key which selects a range of worksheets.
| | 00:48 | If you want to print them both, you can again
press Ctrl+P and display the Print dialog box.
| | 00:55 | If your workbook contains multiple
worksheets and you want to print all of them,
| | 00:58 | you can press Ctrl+P and then in
the Print dialog box instead of saying
| | 01:03 | Active Sheets, you can select the Entire
workbook button, click OK and you are ready to go.
| | 01:09 | Printing workbooks is a great
solution for small businesses.
| | 01:12 | If your customer demands invoices
as electronic documents, however,
| | 01:15 | you should look into PDF printing
solutions that make each workbook page a
| | 01:19 | separate document.
| | Collapse this transcript |
| Printing Word invoices| 00:00 | After you've created your Word invoices,
you can print the documents and mail
| | 00:03 | them when convenient.
| | 00:05 | Basically, all the controls that you will
need are found within the Print dialog box.
| | 00:09 | To display that, press Ctrl+P. Inside the
Print dialog box, you can make several changes.
| | 00:15 | The first is if you want to print the
entire document, in other words every invoice.
| | 00:20 | In this case, it's set up to do that
automatically, with the All option button selected.
| | 00:25 | If you only want to print the
current page, you can select that button.
| | 00:29 | But let's say that I only wanted to
print a selected part of the document.
| | 00:33 | To do that, you just select the
elements that you want to print.
| | 00:38 | Let's say that I wanted to print only
this bit here, the actual invoicing.
| | 00:44 | With that element selected, I can
press Ctrl+P and you will see that the
| | 00:48 | Selection option is now active.
| | 00:50 | If I click Selection and then click
OK, Microsoft Word prints only the
| | 00:55 | elements that I selected.
| | 00:57 | As with printing Excel worksheet
invoices, you should check with your customers
| | 01:01 | to see if they require
invoices in electronic format.
| | 01:03 | If they do, you can either scan your
letters or use PDF or similar software to
| | 01:08 | print directly to an electronic file format.
| | Collapse this transcript |
|
|
ConclusionGoodbye| 00:00 | Thanks so much for working through
Excel 2007: Creating and Managing Invoices.
| | 00:05 | I hope you've learned a lot of
techniques that you can use to help your small
| | 00:07 | business process your invoices efficiently.
| | 00:09 | But don't just stay with what I've shown you.
| | 00:11 | Build on it, change it if you like,
and also please be sure to provide
| | 00:15 | feedback on this course.
| | 00:16 | I'd love suggestions on how you can
extend the invoicing features that I shown
| | 00:19 | you to make everything even more efficient.
| | Collapse this transcript |
|
|
