middle eastern women 2005 optional business computer skills review paula ecklund

Middle EasternWomen 2005

Optional Business Computer Skills Review

Paula Ecklund

Middle EasternWomen 2005Introduction

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


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

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

Middle EasternWomen 2005Your experience?

EXPERT COMFORTABLE NOVICE

Electronic

spreadsheet

Presentation Graphics


The supporting website has many sample and practice files you can download.

http://faculty.fuqua.duke.edu/~pecklund/MEPI The website address is in your paper handout.

SPECIFICALLY

Middle EasternWomen 2005The 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.


An Introduction to the Excel Data Table

An Introduction to the Excel Pivot Table

Non-Sequential Presentation Techniques

in PowerPoint

TOPICS


Middle EasternWomen 2005Key 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

Middle EasternWomen 2005The 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.

StrengthsIn 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.


An example for which a Data Table might be useful for forecasting

See the file ForecastingTools.xls

You might want to know how changing certain model values affects Operating Income, the bottom line.

For example:Price per Unit Units SoldUnits ProducedMaterial Cost per UnitAdvertisingEtc.

Middle EasternWomen 2005It 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.

Middle EasternWomen 2005Let’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

Middle EasternWomen 2005In a compact tabular format, a Data Table

analysis shows the effect of multiple Price 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.

Middle EasternWomen 2005Other examples: How different

model 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

Middle EasternWomen 2005Summary 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

Middle EasternWomen 2005Summary 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).

Middle EasternWomen 2005Then…

Excel executes Excel executes the formula as many times as there are input values in the data table.

For each valueFor 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 iterationFor each iteration, Excel records the result of the formula (the objective) in the data table results matrix.

Middle EasternWomen 2005For more information on the

Excel Data Table





in PowerPoint

TOPICS


Middle EasternWomen 2005What 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.

Middle EasternWomen 2005Duke MBA students’ view

of 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.


Your workshop handout.


Source data used for Pivot Table examples in the

Microsoft article.

Middle EasternWomen 2005The sample file we’ll use

DemoPivotTable.xls

Middle EasternWomen 2005Sales person

Product category

Product name

My sample data in that file.

Worksheet:Sample Data

The data is from Microsoft’s NorthWind sample business, which

imports and exports exotic foods.

Middle EasternWomen 2005Questions

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?


For sample questions and answers, see:




TOPICS


in PowerPoint

Middle EasternWomen 2005We turn to another presentation


E N D

middle eastern women 2005 optional business computer skills review paula ecklund

Documents