ࡱ> q` JrbjbjqPqP .::il8ڎ< ΏΏ(ѐѐѐceeedɬl5l$hvů:ѐѐ::ůگ:c:cO p>..߫'<0 p( ѐ|d_ѐѐѐůůXѐѐѐ ::::DD>JdD>J  INTRODUCTION TO THE SALES ANALYSIS TUTORIAL In this tutorial you will learn how to use pivot tables to analyse your companys sales. You will also use Excels Import Wizard to load into Excel a data file that was exported from a third party accounts package. The accompanying data file SALANAL.TXT contains 4,188 sales invoice line detail records taken directly from an accounts package such as Sage, Pegasus, SunAccount, Navision, etc. The data has been exported from the accounts package as a Text file, which Excel can then import via the Import Wizard. Files in Text format are sometimes also called CDF (Comma Delimited Files), or CSV (Comma Separated Values) or ASCII files. Package suppliers now recognise that it is essential to include facilities to export data into Excel. Hopefully, you will be able to get your own sales data into Excel and analyse it with pivot tables as we do here. Pivot tables allow you to analyse the sales invoices in pretty well any way you want - sales by customer, sales by product group, sales by period, sales by sales rep, etc etc. This tutorial should take you about 30 minutes to work through. If possible, it is a good idea for two people to do it together, one reading the instructions, the other operating the keyboard. You will make extensive use of both left and right mouse buttons. When I say right click I mean click the right mouse button. When I say click on its own, I mean click the left mouse button. The tutorial is designed to be used with Excel 2003 and Excel 2000. There is an earlier version for use with Excel 5, Excel 95 and Excel 97, which can be found on AccountingWeb -News & Info Expert Guides Free Excel Tutorials. 1. IMPORTING THE TEXT FILE INTO EXCEL Start up Excel. Import the SALANAL.TXT file into Excel as follows: File - Open. Open the folder that contains SALANAL.TXT. You cant see SALANAL.TXT. Files of Type: (at the bottom) change from Microsoft Excel Workbook to Text Files SALANAL now appears. Open it. The Text Import Wizard, Step 1 of 3 appears. Excel should have determined that the Data Type is Delimited, not Fixed Width. If it has, click Next. The Text Import Wizard - Step 2 of 3 appears. There should be vertical lines between the columns (Excel identifies the tab marks as delimiters). In addition, you can see field names at the head of each column TRANSN, INVNO, DATE, etc. Click Next. Step 3 of 3 appears. The TRANSN column is highlighted. At the head of the column is the format which Excel assumes it will be in when it is imported the GENERAL format. Excel assumes GENERAL for all fields. At top right are the possible Column data formats: General, Text, Date, Skip (i.e. do not import) General format is OK for all your fields except DATE. You will need to change the date format. In the Column Data Format box at top right the Date option should have the default value DMY against it. This means that Excel is expecting that any Date fields it finds in the text file will be in Day/Month/ Year format, which is the UK standard. If DMY is not the default offered, select DMY from the list so that it is. Click on the General heading of the third column, DATE. The column is highlighted At the top right click on: Date. The heading of the DATE column should now read DMY. Click on Finish. Wait a moment. The file is imported into Excel. [Note: If your default date format was MDY rather than DMY, this means that your PC is set to express dates in month/day/yearUS format. If you are a UK user you need to change this now to UK day/month/year format. Cancel the Import wizard and come out of Excel. Close down all other programs, because you will have to reboot your machine. Then select: Start - Settings - Control Panel - Regional Settings. Change from English (United States) to English (United Kingdom) or English (British). Reboot the PC as instructed. Start the tutorial again. This time the Import wizard should offer DMY as default] 2. TIDYING UP THE FILE A Text file imported from another system will have lost most of its formatting. Therefore it first needs to be tidied up: Highlight the first row of the spreadsheet by clicking on the 1 to its left. Then click B on the toolbar to make the column headings Bold. Click on the top LH grey brick above the 1 and left of the A. The whole worksheet is highlighted. Double click between column headings A and B. All the columns are widened to their best fit. Highlight Row 2 by clicking on 2 at the left. Then select: Window - Freeze panes Now copy the file into a suitable directory and convert it to Excel format, as follows: File - Save As. Leave the file name as SALANAL but below the filename change as follows. Save as Type: Change from Text (Tab delimited) to Microsoft Excel Workbook. Save. 3. CONTENTS OF THE SALANAL DATABASE The database is a set of records derived from the sales invoice detail file within the accounts package. Each record contains the following fields: TRANSN = Transaction Type (i.e. Invoice, or Credit Note in this case) INVNO = Invoice or Credit Note number DATE = Invoice or Credit Note Date ACCNO = Customer Account Code CUSTNAME PARTNO DESCRIPTION MANUFR = Manufacturer (this is old data, and quite a few of these are now defunct) PRODGROUP = Product Group QTY = Quantity Invoiced S.P .= Item Selling Price NET = Line Net Value (i.e. Qty x S.P.) C.P. = Item Cost Price COST = Line Cost (i.e. Qty x CP) MRGN = Line Margin (i.e. NET COST) SALES EXEC = our Sales Representative So, taking the first two lines as an example, on 4th April 2004 we issued sales invoice number SIN001649 to customer no. R001, Rolls Royce Ltd, for two products HP51625A Colour Print Cartridge, quantity 2, at 18 each, and a US Robotics Sportster 28.8 External modem, quantity 1, at 228 each. 4. MOVING AROUND THE FILE Scroll down through the first 100 or so records to see the sort of data the file contains. Then: Go to the bottom of the file: (tip: hold down the right hand Ctrl key with your right thumb, then press Down arrow) There are 4,188 rows (i.e. 4,188 sales invoice detail records) Go to the bottom of column L, the NET column. Click onto the L at the top of column L. At the bottom of the screen it says Sum = 5442458 Make a note of this number. Click to remove the highlighting from column L. Return to the top of the file. (keyboard shortcut = hold down Ctrl, then press Home key.) 5. GENERATING A PIVOT TABLE The cell pointer should be on A2 (or anywhere within the data will do) Well build up the pivot table step by step. From the main menu at the top of the screen, select : Data - Pivot Table and Chart Report The Pivot Table Wizard, Step 1 of 3 screen appears. Click on Next. Step 2 of 3 appears The Range box should say: $A$1:$P$4188. [Earlier versions of Excel sometimes didnt pick up the range automatically. If the Range box is blank or says Database, type in $A$1:$P$4188 by hand]. Click on Next. Step 3 of 3: Click on Layout at bottom left. The COLUMN-ROW-DATA box appears. To the right are all the column headings of your spreadsheet. Drag and drop as follows: PRODGROUP into the ROW area and NET into the DATA area [When dropped into the DATA area, NET should now say Sum of NET. If it says Count of NET, double click on Count of NET. A box appears. Change Count to Sum] Now click on OK then Finish. A list of Product Groups appears with a total beside each one. At the bottom of the sheet is the total 5442458. From the 4,188 records in SALANAL, Excel has generated a pivot table summarising total Net Sales by Product Group. Look at the bottom left of your screen. To create the pivot table, Excel has generated a new worksheet to the left of SALANAL. This sheet is probably named Sheet1. Click back onto the SALANAL worksheet. The original sales invoice database is still there. Now right click back onto Sheet1 with the pivot table. Rename this worksheet PIVOT. 6. FORMATTING THE NUMBERS IN A PIVOT TABLE The sales figures would be easier to read if the thousands were separated by commas. Therefore: Click on any of the numbers in column B. Then right click the mouse. A menu appears, with Format Cells at the top. [If you are using Excel 2003, clear the screen with Hide Pivot Table Toolbar and Hide Field List]. Then select: Field Settings. The Pivot Table Field dialogue box appears. From the options on the right, select: Number. The Format Cells box appears. From the Category list, select: Number Then: Decimal Places = 0 - 1000 separator?, tick for YES - -1234 in red OK OK In the pivot table commas now separate the thousands. 7. SORTING THE PIVOT TABLE You would like to rank the product groups to show the best sellers first. To do this: Click anywhere in the Total column, eg on cell B10 Click on the ZA icon. The Product groups are sorted in Descending order, with PCs at the top [if you dont have the ZA icon, use the AZ icon instead and sort ascending.] Now move your mouse pointer around the top of cell A4 PRODGROUP until it turns into a thick black vertical down arrow. Then click the mouse. The column is highlighted. [If you cant get the black down arrow, it may be because selection is switched off. Right click for menu - Select - Enable Selection. Now try again.] Click on the AZ icon. The product groups are sorted back into alphabetical order, ascending. 8. ANALYSING SALES BY CUSTOMER Having seen sales by Product Group, you would now like to analyse them by customer. Right click anywhere within the pivot table. The menu appears. From the menu: Wizard [Excel 2003 = Pivot Table Wizard] - Layout The COLUMN-ROW-DATA box re-appears. Drag and drop PRODGROUP anywhere outside the ROW area. Drop ACCNO into the ROW area. Then drop CUSTNAME below ACCNO in the ROW area. Then: OK - Finish. The pivot table now displays sales by Customer account number and name. But the screen is not very clear as the ACCNO is totalling. These totals need to be removed:. Right click on the grey ACCNO field button in cell A4. The menu appears Select: Field Settings. In the Subtotals area on the left, change from Automatic to None. OK That looks better. Note that the total is again 5,442,458. This time the sales are broken down by customer. 8. SUMMARISE SALES BY MONTH The sales invoices were dated from April to September. We will summarise them by month: Right click within the pivot table. The menu appears. Select: P/T Wizard - Layout The COLUMN-ROW-DATA box re-appears. Drag and drop DATE into the COLUMN area. OK. Finish. The pivot table is recalculated. Across the top there is now a series of dates. They need to be grouped by month. To do this: Right click on the grey DATE field button in cell C3. The menu appears. Select: Group & Outline Group [In Excel 2003 Group and Show Detail - Group ]. The Grouping Dialogue Box should now appear. [If it doesnt, but you get the message Cannot Group That selection, see the paragraph below]. Select Months and Years (at the bottom of the list under Quarters). OK. The pivot table now displays the Net sales by month. [Cannot Group This Selection] You should not get this error message during the tutorial. However, if you ever do, the following notes may be helpful.The Group command is invaluable, but a bit temperamental. I get more trouble trying to group date fields than all other problems put together. And if it does fail, the only feedback you get is Cannot Group This Selection. There are two likely sources of error. First, your default date format in Windows is set to US month/day/year format, whereas the date fields in your imported data were expressed in UK day/month/year format. To check this, take a closer look at the way your dates are currently formatted in Excel. If you are a UK user and they are US with the month first, you need to reset your Regional Settings in Control Panel, as described in section 1 above. Then restart the tutorial over again. The second possibility is that one or more date fields within your data are either blank or not formatted correctly as dates. First thing to do is to go into the SALANAL worksheet, click anywhere on the DATE column BUT NOT THE COLUMN HEADING ITSELF, then click the AZ icon. This sorts the database into date order. Look at the first and the last dates in the worksheet as this is where faulty dates will appear. Correct any errors, then Refresh Data. Second, check that the Range is not looking at any blank rows, which will contain blank date fields. Do this via Wizard Back, which should show you the Range $A$1:$P$4188. If it says $A$1:$P$4189, then you need to change it to $P$4188. Even when youve corrected all the errors, when you Refresh Data you may still get the Cannot Group This Selection message. In this case delete the entire pivot table and build it again from scratch: this time it should work. Finally, sometimes a Date field will not Group when put in the ROW area, but will if put it in the COLUMN area In this case, Group them first in COLUM, then go into the wizard and move Years/Date out of COLUMN and into ROW. You will find that the Grouping stays OK.] 9. SHADE THE COLUMN HEADINGS We will tidy up the column headings with some shading. Move the mouse pointer over DATE in D3 until it turns into a thick down arrow, then click. The column headings Apr to Sep are highlighted. To add shading, click on the yellow Fill Color icon in the bottom row of icons, second from right (the one that looks like its pouring a can of paint). Centre the months by clicking the Centre icon. Also click the B icon for Bold print. Now click anywhere to remove the highlighting. The monthly headings are all shaded yellow. 10. ANALYSING SALES BY MANUFACTURER So far we have analysed sales by product group and sales by customer. Now we will analyse them by manufacturer, then see which manufacturers products are selling the best. Right click within the pivot table for menu. Select: P/T Wizard - Layout The COLUMN-ROW-DATA box re-appears. Remove ACCNO and CUSTNAME by dropping them anywhere outside the table. Drag and drop MANUFR into the ROW area. In addition, drop PRODGROUP onto the PAGE area. OK. Finish The pivot table is recalculated, showing sales by manufacturer. Again, the total is 5,442,458. [The list of manufacturers should be in alphabetical order, starting with 3COM. But sometimes DEC appears first Ive no idea why. If it does, sort the manufacturers alphabetically as follows: Move the mouse pointer over MANUFR in A5 until it turns into a thick down arrow, then click. The column is highlighted. Click on the AZ icon. The column is sorted and DEC goes to its proper position. 11. ADDING A FURTHER DIMENSION WITH PAGE FIELDS So far the data is presented in two dimensions, by rows and by columns. However, you can add a third dimension of analysis by using the PAGE field. For example, we might wish to see which brand of PC is selling the best. Therefore: PRODGROUP is the Page field in cell A1. Click on the down arrow in the right of cell B1. You see a list of the product groups - (All), COMMS, INTERNET, MEMORY, MONITORS etc. Highlight PCs OK. The pivot table now shows only those manufacturers who sell PCs. Total PC sales were 2,813,885. The best seller was DEC, with sales of 1,026,424. Click on the B1 down arrow again and now look at the sales for PRINTERS. The total value of sales by manufacturer is shown (243,132). But which models sold best? To find out, right click for menu - P/T Wizard Layout. Drag PARTNO and drop it under MANUFR in the ROW area. Drag DESCRIPTION and drop it under PARTNO in the ROW area In the ROW area, double click on PARTNO. The Pivot Table Field box appears Change Subtotals from Automatic to None. OK. OK Finish The pivot table is redisplayed, but this time shows sales of the individual models of printer. 12. HIDING THE DETAIL The Hide Detail and Show Detail commands will expand and collapse the pivot table. So: Right click on A4, MANUFR. From the menu select: Group and Outline - Hide Detail The pivot table is summarised back to top level with one line per manufacturer. To see more detail for EPSON: Double left click on EPSON in cell A6 Excel expands EPSON to show the individual products. Double left click anywhere in the gap below EPSON, say cell A8. You go back to one summary line. You can see the detail of any manufacturer, just by double clicking on them. Try IBM. IBM is expanded. Now double click in the gap below IBM. You go back to summary level. 13. SHADING SUBTOTAL LINES We want to make the sub-totals for Canon, Epson etc stand out by shading them. Move the mouse pointer to the grey 15 brick left of cell A15, CANON Total. Move it gradually to the right until it turns into a thick horizontal black arrow. Then left click the mouse. All the sub-total lines are highlighted. [Note: if the black arrow stays within the 15 box, only Row 15 is highlighted. But if the arrow is partly over cell A15 as well, ALL the sub-total rows are highlighted]. Well use the yellow Fill Color icon again (the one that looks like its pouring a can of paint). However, this time click on the down arrow to the right of the icon, and select a colour from the palette. Personally, I like light blue or light green in the bottom row. 14. SALES AND GROSS MARGIN BY SALES EXECUTIVE Having analysed the companys sales by product group, by customer, by manufacturer and by product, we will now analyse sales by salesperson. Right click for menu P/T Wizard Layout - COLUMN-ROW-DATA box re-appears. Drop MANUFR, PARTNO and DESCRIPTION out of the ROW area. And drop PRODGROUP out of the PAGE area. Now drop SALES EXEC into the ROW area. OK Finish The pivot table is recalculated to show sales by sales executive. Total sales are still 5,442,458. Top salesmen over the 6 months were Eddie Mars (1,643,489) and Bernie Ohls (1,116,160). But suppose you pay your sales force on the basis of gross margin earned, not turnover: Right click for menu P/T Wizard - Layout - COLUMN-ROW-DATA box re-appears. Drop MRGN below Sum of NET in the DATA area, so you get Sum of MRGN OK Finish Now the pivot table shows the Margin each salesman has earned, underneath their Net sales. Well tidy the MRGN lines up a bit. Firstly, put commas between the thousands as follows: Move the pointer to the left of Sum of MRGN until you get a thick black right arrow, then click Right click for menu. Field Settings. From the right hand list of options, select: Number Then: Number - Decimal Places = 0 - 1000 separator, YES - -1234 in red OK OK The MRGN lines now include commas separating the thousands. The Sum of MRGN lines should still be highlighted. Now: Click the down arrow on the Fill Color icon (the yellow paint can). A palette of colours appears. From the palette, select Tan (bottom row, second from left). Click anywhere to remove the highlighting. The Margin lines are all shaded light brown. 15. PRINTING THE SALES REPORT. We will print out this pivot table showing monthly sales and margin by sales executive. (You dont have to print this report. If you wish, you can skip this and go on to section 16). First, click the Print Preview icon to see the report on screen This report would look better if printed in Landscape. Therefore select:: Setup - Page - Landscape - OK Then, select: Setup - Margins - at bottom, tick the Horizontally and Vertically boxes - OK. Then, select: Setup - Sheet - check the Gridlines box - OK. Then select: Setup - Header/Footer then click on: Custom Header. Type in the following: Left Section: Type: Printed: then click on the 8/7 icon. &[Date] appears Right section: Type: Page: then click the # icon - type: of - click on the ++ icon. Center section: Type in capitals SALES AND MARGINS BY SALES EXECUTIVE Highlight this text with the mouse . Click on the A icon. The Font box appears Select Bold and 10 OK OK OK Then Print to print the report. 16. DISPLAYING NET AND MRGN IN COLUMNS. The sales by Sales Exec screen displays the NET and MRGN values underneath each other. You would prefer to see them side by side: Right click for menu P/T Wizard - Layout. Move Years and DATE into the PAGE area. OK Finish. The row of months disappears.. Wave the mouse over Sum of Margin for thick black right arrow. Click to highlight all rows. Well remove the colouring. Click on the down arrow in the Fill Color icon. The colour palette appears. Click the No Fill box. The brown colouring disappears. Now click the LH mouse button on the grey DATA field button in cell B4 and hold it down. Now drag it down a few millimetres until an icon with a slab of blue to the left appears. Keeping your eye on the slab of blue, slowly drag the brick over to the Total box in cell C4. When the slab of blue changes to horizontal, release the mouse key. The pivot table is reformatted, with Sum of NET and Sum of MRGN side by side. 17. USING FORMULAS TO CALCULATE GROSS MARGIN PERCENTAGE Margin is obviously important, but you suspect some of your salesmen have been buying business by discounting the list price. So you want to see the gross margin percentage your salesmen are making on sales. We will use the Formulas option to calculate gross margin percentage (i.e. Margin divided by Net Sales value). Right click on the pivot table for menu. Select: Formulas Calculated Field [In Excel 2003 the Formulas option is rather hidden away. From the menu, select Pivot Table Toolbar click on Pivot Table at the left click the down arrows to display the complete menu. Select: Formulas Calculated field] The Insert Calculated Field box appears. In the Name box, replace Field1 with MGN% In the Formula box click to the left of the 0. Delete the 0 Then: In the list of Fields, find MRGN and highlight it. Then click on Insert Field. MRGN appears in the Formula box To the right of MRGN, add a right slash (/) , meaning divide by. Go back into the list of fields. Find NET and highlight it. Insert Field The Formula box should now display the formula =MRGN/NET Click on Add, then OK A fourth column appears in the pivot table, entitled Sum of MGN% It needs to be reformatted as a percentage. Also, the Sum of MGN% title is making it too wide. Right click anywhere in the new column. From the menu, select: Field Settings In the Name box at the top, click to the right of the f in Sum of. Then use the big Left Arrow key to delete everything to the left . (leave one blank space before MGN% , otherwise Excel gets confused) Also select: Number - Percentage - Decimal places = 0 OK OK Interesting. While Eddie Mars and Bernie Ohls have generated the biggest margin in absolute terms, Vivian Regan and Jane Seymour are earning the highest percentage return on sales. And what on earth is Harry Jones doing at only 25%? We need to slice and dice to find out whats happening. Maybe the answer lies in the different mix of products they are selling. So: right click for menu P/T Wizard Layout. Move SALESEXEC into the PAGE area. Put PRODGROUP in the ROW area. OK Finish The pivot table is recalculated, showing the margin earned by Product Group. Now, next to SALES EXEC, click on the down arrow in column B. Select: Harry Jones. Most of Harrys sales were PCs. Thats hardware, with a margin percentage of only 33%. Now from the list choose Vivian Regan. Over half of her sales (136,559) were TRAINING, which is services. They generated 97% margin! 18. DRILLING DOWN ON A VALUE TO SEE THE UNDERLYING TRANSACTIONS An invaluable feature of pivot tables is the ability, if you are doubtful about a value, to drill-down on that value and see the individual transactions that make it up. So in this case, the margin of 136,559 on TRAINING needs to be checked. Double click on the: 136,559. A new worksheet is generated, listing all the transactions.. Click anywhere in the data to remove the highlighting. Scroll to the right to see the NET and COST values for each line in columns L and N. In many cases the COST is zero, resulting in 100% gross margin. This explains the 97% average. Finally, right click back onto the PIVOT worksheet. OK. Its time to take break. Save your work via: File Save . Then relax. Final food for thought: This is your first hint of the fact that, when data is exported from an accounts package, it will invariably contain errors in the form of wrong values, miscodings, blank records, etc. Before anything, you will first have to tidy the data up. Cost prices are a particular problem. Sales Admin departments, who process the sales orders and invoices, are usually not bothered about them. Selling Prices are obviously important, since customers will complain if they are wrong; but who cares about accurate Cost prices? In addition, when services are supplied as opposed to products, it is often difficult to work out what exactly is the true cost? While Sales Admin or Marketing personnel can be trained to use pivot tables to report sales by quantity or value, they should NOT be allowed to report on margins and profitability. This should only be done by a trained accountant, who is able to appreciate the potential pitfalls DAVID CARTER, October 2004     Page  PAGE 14 of  NUMPAGES 12 TUTORIAL SALES ANALYSIS WITH EXCEL PIVOT TABLES  dh #Bq24;'AmnoKLij6d+OPEF!!!!! h<>* h<H* h<56 h<6h< h<5X;<= c d e f g + ,   9r @]  @] @]qIr UVBCpqv34  9r @] @]BCnopq  ef  9r @] @]~5o"FwW  @] @]W ! !q!s!!!5"6"h"i"""##u#w####  9r @]  @] @]!5"Y"\"""W#]#g#o#w##y$$$$$%%%%%%%%%%&s&&&&&&&&&''''''(:)A)B)C)q)))))))))-** +@+Y+^+m++++++,B,X,Y,_,,,,,,,-------. . h<56 h<CJ h<>* h<5h< h<6X##M$N$$$%%%%%%&&r&s&&&''''((B)C)))) @]))).*/***** + +p+q+++++Y,Z,,,,,,--v-  9r @] @]v-w---.`.a.////00008090000011B1C1{1|11  9r @] @] ..//////07000001=1B1C1Q1Z111111111112222222 33?3H3L3P3T3W3Y3Z333r44444444444455 5555555G6v666666677 7Z7b7}77??J@~@AAGAAAAB C/C3C h<5 h<6h<`1111222233Y3Z333333J4K4444465758555  9r @] @]555F6G6v6w666%7&7\7]7^7_7`77::==???????  9r @] @]?J@K@@@AAzA{AAAA B BBB C C3C4CCCCCCCTDUD  9r @] @]3C4CCTD|EEEEE"FG GGGGGG/H#I)IJ0JHJQJUJYJvJJJJKKKKKKK3L8LFLzL|L}LLLLL* h<5h< h<6\UDEE{E|EEEEEE#F$FFFGGuGvGGG0H1HHHHHH5I  9r @] @]5I6IqIrIIIII1J2JvJwJJJJJKKLKKKKKFLGL|L}LLL 9r  @]L;MTTTTTXUYUUUVVXVYVVVWWAWBWW @]  9r @]WWWWXX{X|XXX Y YMYPYYYYY]Z^ZZZ+[,[[[[[ @]  9r @])X{X|XXX1Y?YyYYYYYYYY,Z-Z]Z^ZqZ{ZZZZZZZZZZ [ [+[=[w[y[[[[[[[[[\\\\\4\b\\\\\[]z]]]1^K^n^^^^/_:_B_C______7`8`9`v`a(a]aeaab*b2bc,cIcOcUc h<>* h<56h< h<5 h<6Z[[[[[4\b\c\\\]]|]}]]]0^1^^^D_E___9`:`;`  9r @] @];`x`y``LaMaaabbbcc-c.cZc[ccc d#dhdidddddde 9r  @]UcYccdd#dddeeeee'f,fffffg gg7g;gIgBiHiIiijk>l{lllymmmmmmnnnpppqqqqqqqqqqqqqqqqrrrrrrrFr˺˯h<0JmHnHuhH20JmHnHu h<0Jjh<0JUjh<Uh<56>* h<56 h<>* h<5h< h<6EdeeeeefffffJgKgLg9h:hhhIiJiiiiiKjLjjjjk @] 9r kkkkllzl{lll m mnmommmmmnnnn oo$pppq  9r @] @]qqqqqqqqqqqqrFrGrHrIrJr$a$ @]FrIrJr h<6h<,&P . A!n"S#n$S% 8@8 Normal_HmH sH tH 8@8 Heading 1$@&58@8 Heading 2$@&6@@@ Heading 3$ & F@&5DA@D Default Paragraph FontVi@V  Table Normal :V 44 la (k@(No List 4@4 Header  9r 4 @4 Footer  9r .)@. Page Number0U@!0 Hyperlink>*B*4B@24 Body Text$a$L^@BL Normal (Web) ddB*CJOJQJBP@RB Body Text 2 @]Jj;<= cdefg+, UVBCpq  v   3 4 B C nopq  ef~5o"FwW  qs56hiuwMNrs B!C!!!!!!."/""""" # #p#q#####Y$Z$$$$$$%%v%w%%%&`&a&''''((((8(9((((())B)C){)|)))))****++Y+Z++++++J,K,,,,,6-7-8-----F.G.v.w...%/&/\/]/^/_/`//22557777777J8K88899z9{9999 : ::: ; ;3;4;;;;;;;T<U<=={=|======#>$>>>??u?v???0@1@@@@@@5A6AqArAAAAA1B2BvBwBBBBBKCLCCCCCFDGD|D}DDD;ELLLLLXMYMMMNNXNYNNNOOAOBOOOOOPP{P|PPP Q QMQPQQQQQ]R^RRR+S,SSSSSSSSS4TbTcTTTUU|U}UUU0V1VVVDWEWWW9X:X;XxXyXXLYMYYYZZZ[[-[.[Z[[[[[ \#\h\i\\\\\d]e]]]^^^^^J_K_L_9`:```IaJaaaaaKbLbbbbccccddzd{ddd e eneoeeeeeffff gg$hhhiiiiiiiijFjGjKj0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000I0I0I0I0@0@0@0I0;<= cdefg+, UVBCpq  v   3 4 B C nopq  ef~5o"FwW  qs56hiuwMNrs B!C!!!!!!."/""""" # #p#q#####Y$Z$$$$$$%%v%w%%%&`&a&''''((((8(9((((())B)C){)|)))))****++Y+Z++++++J,K,,,,,6-7-8-----F.G.v.w...%/&/\/]/^/_/`//22557777777J8K88899z9{9999 : ::: ; ;3;4;;;;;;;T<U<=={=|======#>$>>>??u?v???0@1@@@@@@5A6AqArAAAAA1B2BvBwBBBBBKCLCCCCCFDGD|D}DDD;ELLLLLXMYMMMNNXNYNNNOOAOBOOOOOPP{P|PPP Q QMQPQQQQQ]R^RRR+S,SSSSSSSSS4TbTcTTTUU|U}UUU0V1VVVDWEWWW9X:X;XxXyXXLYMYYYZZZ[[-[.[Z[[[[[ \#\h\i\\\\\d]e]]]^^^^^J_K_L_9`:```IaJaaaaaKbLbbbbccccddzd{ddd e eneoeeeeeffff gg$hhhiiiiiiiijFjGjHjKjI0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0I0K0K0K0K0K0I0I0I0 0 kkkkkn! .3C)XUcFrJr:AEIORVW#)v-15?UD5ILRW[;`dekqJr;=>?@BCDFGHJKLMNPQSTUIr<"'25n!8@0(  B S  ? $ <i"{4N" ?11122Kj    A111!2!2Kj B *urn:schemas-microsoft-com:office:smarttagscountry-region9 *urn:schemas-microsoft-com:office:smarttagsplace   ";D `c{+, UVdh #)AKpq  v   2 4 ; <  :nqrv  LMjkdfv+/~OP8EF    qs56\]hgiuMNdIJ $Iefip , A!R!!!!!!!!!-"/"\"x""""""""#### $$2$A$Z$^$c$~$$$$$$$$$$$ %%%6%l%v%w%%%%%%%&& &&C&t&&&'''''(((((7(9((((((((-)=)K)Z)[)d)t)x)|)))))))***********+,+-+H+I+P+U+W+Z+++++),C,H,K,],^,,,,,,,-- --5-B----------^.v.x.........../!/%/&/Z///// 0002255777899G9H9z9{999: ::::;/;4;;;;;;;T<V<<<========">$>>>?? ?!?u????????/@1@@@@@@@)A*A5A=AOA}AAAAAAA0B2BQBRBYBuBvBwBBEEFFFFGGGHHHHTIhIxIyIIIII4J5JaJvJJJ K"K(KBKyKKKKKKKKLKLLLLM#M$MRMdMqMMMMMNNNNNWNYNjNmNNNNOAOBOOOOPPPPP Q Q1Q?QMQPQiQQQQQQQQQQ,R.R]R^RqRsRRRRRRRRRRRRRSS+S,S=S>SwSSSSSSSSSSSSSSSSTT+TJTbTlTTTTTTTT&UEUMUSU\UzUU0V1VKVQVVVVVVVVVWW6WUWkWWWWWWWW3XG_I_w_{_Maa+cCcDcccccc ddd.dBdzd{ddd e e?@ABCDEFGHIJKLMNOPQRSTUVWYZ[\]^_abcdefghijklmnopqrstuvwxyz{|}~Root Entry Fr.Data X1Table`BWordDocument.SummaryInformation(DocumentSummaryInformation8CompObjq  FMicrosoft Office Word Document MSWordDocWord.Document.89qRoot Entry FgR/@Data X1Table`BWordDocument. $, SummaryInformation(DocumentSummaryInformation8CompObjq  FMicrosoft Office Word Document MSWordDocWord.Document.89q՜.+,D՜.+,t0 px  David Carter6i .EXCEL COURSE PART ONE - FORMATTING A DATABASE Title4