1 / 32

Optional Business Computer Skills Review Paula Ecklund

Optional Business Computer Skills Review Paula Ecklund. Introduction. For what skill level is this workshop designed?. What software is used? What is the workshop focus? Excel Data Table Excel Pivot Table Non-sequential PowerPoint The handout.

kevork
Download Presentation

Optional Business Computer Skills Review Paula Ecklund

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Optional Business Computer Skills Review Paula Ecklund

  2. Introduction • For what skill level is this workshop designed? • What software is used? • What is the workshop focus? • Excel Data Table • Excel Pivot Table • Non-sequential PowerPoint • The handout

  3. The Pivot Table skills I picked up are absolutely invaluable in my summer internship. I’m analyzing a sales database using Excel and I would be beating myself in the head with simple math functions by now without Pivot Tables. They really saved me! Tera Ferguson, MBA 2003 I learned enough Excel at Fuqua to take a number of finance courses that required advanced Excel spreadsheet modeling. I put many of my newly acquired Excel skills to work during my summer internship in media strategy. Dhanusha Sivajee, MBA 2004 I had a background in programming prior to business school but I still learned quite a few new things in Excel that I hadn't used before. Marla McClure, MBA 2004

  4. Your experience?

  5. SPECIFICALLY http://faculty.fuqua.duke.edu/~pecklund/MEPI The website address is in your paper handout. The supporting website has many sample and practice files you can download.

  6. The paper handout you have in hand. Demonstration and practice files for the Excel Data Table. Demonstration files for the Excel Solver add-in. A Microsoft sample file to illustrate the Excel Pivot Table. PowerPoint files to illustrate techniques for non-sequential presentations.

  7. TOPICS An Introduction to the Excel Data Table An Introduction to the Excel Data Table An Introduction to the Excel Pivot Table Non-Sequential Presentation Techniques in PowerPoint

  8. Key Excel Forecasting Tools • Series • Edit, Fill, Series: Predict trends linearly or exponentially • Functions specifically for forecasting • FORECAST, TREND, GROWTH, LINEST, LOGEST • Regression analysis • Data Tables • Scenarios • Solver

  9. Strengths In a single operation (i.e., running the Data Table), substitute any number of different values for a particular model variable, tracking how the change affects some other critical model value. Display all the critical model value results together, in a compact format. The Excel Data Table • For forecasting, sensitivity analysis • How it works: Summarizes how a range of changes in one or more model variables affects critical values in the model.

  10. You might want to know how changing certain model values affects Operating Income, the bottom line. For example: Price per Unit Units Sold Units Produced Material Cost per Unit Advertising Etc. See the file ForecastingTools.xls An example for which a Data Table might be useful for forecasting

  11. It would be very inefficient… …to attempt to enter directly into the model the many possible values you want to test and keep track of the bottom line for each case. The Data Table is the perfect tool to use instead.

  12. Let’s review How does changing Price per Unit affect Operating Income, the bottom line? Specifically, what is our break-even point for Price per Unit? SEE

  13. In a compact tabular format, a Data Table analysis shows the effect of multiplePrice per Unit values on Operating Income and the break-even point. What’s important here? Using a Data Table, one can test a range of possible Price/Unit values on the bottom line in a single operation… …instead of having to test each Price/Unit value by entering each value in the model one by one.

  14. Other examples: How differentmodel values affect Operating Income • Units Sold: 900, 950, …1400 • Mat. Cost/Unit: $20, $21, …$30 • Both Units Sold and Material Cost/Unit2-Input Data Table

  15. Summary of using a data table • Plan • Identify your model’s objective. • Decide what key model value(s) (decision variables) you want to vary. They become data table inputs. • Set up • Establish a range of values for the decision variable(s). • Identify the model formula(s) that directly or indirectly use those values and that show the effect of the varying decision variables. • Follow Excel’s data table setup requirements. • Execute • Run the Data Table. Skip execution details

  16. Summary of Data Table Execution • Select the table range. • From the Data Menu • Data, Table • Complete the Table dialog. If data table inputs are arranged down the left column, identify the Column input cell. This is the cell that holds the model value to be varied (the decision variable).

  17. Then… • Excel executes Excel executes the formula as many times as there are input values in the data table. • For each value For each data table input value Excel substitutes that value into the data table formula (which comes from the model or references the model formula cell). • For each iteration For each iteration, Excel records the result of the formula (the objective) in the data table results matrix.

  18. For more information on theExcel Data Table

  19. TOPICS An Introduction to the Excel Data Table An Introduction to the Excel Pivot Table Non-Sequential Presentation Techniques in PowerPoint An Introduction to the Excel Pivot Table

  20. What is a Pivot Table? An interactive table • That can quickly summarize large amounts of numeric Excel list-format data. Use to analyze related totals, choosing from a variety of summary operations. • Each column or field in the list of data becomes a PivotTable field that summarizes multiple rows. Rotate (Pivot) • Rotate a Pivot Table’s rows and columns to see different summaries of the source data. Display details • Display details for areas of interest.

  21. Duke MBA students’ viewof the Pivot Table The opinion of students taking the Information Management elective that covers the Excel Pivot table… The Pivot Table is consistently rated  The most useful topic covered in the course. and The Excel data management tool students think they will use most often on the job.

  22. Your workshop handout.

  23. Source data used for Pivot Table examples in the Microsoft article.

  24. The sample file we’ll use DemoPivotTable.xls

  25. Sales person Product name Product category My sample data in that file. Worksheet: Sample Data The data is from Microsoft’s NorthWind sample business, which imports and exports exotic foods.

  26. Questions • Why use a Pivot Table to analyze this data? • There are 8,620 records in the list. • Other Excel tools (filter, subtotal) might be useful but less powerful. • Using a Pivot Table, what kinds of questions can we answer about the data? Examples: • What products are in the “Condiments” category? • What quantity of each product in this category has sold? • What is the sum of all quantities sold in this category? • Which 3 sales people sold the most in the “Produce” category? • What are product sales in particular categories by quarter?

  27. For sample questions and answers, see:

  28. TOPICS An Introduction to the Excel Data Table An Introduction to the Excel Pivot Table Non-Sequential Presentation Techniques in PowerPoint

  29. We turn to another presentation

  30. E N D

More Related