Excel技巧英文PPT

• • •
Goals
To review/teach you some of the more common functions used in data analysis To show you how analysis should be presented To teach you to understand how Excel formulas are designed
Six factors that make a great model in Excel
1) Formatting and Layout
• •
Hard coded cells are in blue, green are linked to another page Each firm will have a different code so always ask!
4) Common Sense and Traceability
•
Chances are your clients are going to use your models. Make sure they are easy to use and understand for those with lesser skills (i.e. just because you can use array formulas doesn‟t make them better than sumif formulas) If a formula is too long, add a column or row and split up the formula. Your first tab should be either a summary or instructions and be sure to include tabs for reference or source data. Label every line clearly. Use comments if necessary to explain a line or a cell.
Six factors that make a great model in Excel
3) Formulas, Formulas, Formulas
•
The more you know the better – more on this latter!
Six factors that make a great model in Excel
Exercise: Conditional Formulas

Review: Use basic SUM and COUNT formulas to analyze a bidder‟s pricing Remember to compare “apples to apples”

Advanced: Compare pricing between multiple vendors incorporating multiple criteria into the basic SUM and COUNT formulas Remember to compare “apples to apples” Remember array formulas must be entered by pressing CTRL+SHIFT+ENTER Kick it up a notch: Expand the analysis and compare pricing between multiple vendors based on subcategories of the items up for bid Remember to compare “apples to apples” Remember array formulas must be entered by pressing CTRL+SHIFT+ENTER Oftentimes, a bidder has to be able to fulfill all items in a subcategory to be considered in the analysis
Six factors that make a great model in Excel
2) Flexibility
• •
Hard coded cells as little as possible Anticipate the scenarios you may be asked to analyze (10 yrs instead of 5 yrs)

Analytics: PivotTables – Prep the Data
Make sure all columns have unique headings
Exercise: Pivot Table




Create a PivotTable to analyze: Revenue, COGS and Freight by Customer Type Add another dimension to the PivotTable: Discover how Revenue, COGS and Freight are distributed across Products within each Customer Type by adding Products to the column heading Add another dimension to the PivotTable: Discover how California residents differ from Arizona residents by adding „State‟ to the Columns Label Refresh your PivotTable after changing the source data Include a 10% discount to Freight across all customers Data must have different headings
Six factors that make a great model in Excel
6) Versioning
• • •
Save your work every 15 minutes. Do not use Ctrl S Develop a file naming convention (e.g. name – date_time of day)
Table of Contents Tab
If you are planning on turning the model over to the client or anyone else and there
are multiple tabs, create a table of contents briefly describing each ta
Agenda
Intro and 6 Factors to Excel Excellence
Exercise: Linking Data Exercise: Conditional Formulas
Exercise: Pivot Tables
Other Useful Formulas and Shortcuts
Exercise: Linking Data

Staging: Combine the 3 data sources into one table based on consultant name Keep in minrences

Analytics: Use VLOOKUP, INDEX, and MATCH to identify the consultant with the most sales Keep in mind your basic Excel formulas
Excel Training for Consultants
May 16, 2013
Purpose of this Training
Expectation
You have a basic understanding of Excel navigation, but lack experience formatting, using functions, and conducting data analysis
Formatting Examples: Client-Ready Models

Summary Tab Models should have a tab that summarizes the analysis (using linked formulas, not hard-coded numbers, of course) The data should be summarized in a way such that it can be easily copy/pasted (as a picture or PowerPoint table, not excel table) into a PowerPoint document/deliverable Include any key assumptions to be copy/pasted into a deliverable The summary tab may also contain the graphs that are to be copy/pasted into a PowerPoint document/deliverable
• •
•
Six factors that make a great model in Excel