Excel技巧英文培训材料.ppt

Excel Training for Consultants;Expectation;Intro and 6 Factors to Excel Excellence;Six factors that make a great model in Excel;Six factors that make a great model in Excel;Six factors that make a great model in Excel;Six factors that make a great model in Excel;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;Staging: Combine the 3 data sources into one table based on consultant name Keep in mind absolute vs. relative references Analytics: Use VLOOKUP, INDEX, and MATCH to identify the consultant with the most sales Keep in mind your basic Excel 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;Make sure all columns have unique headings;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