Transcript Excel 2007 Workshop - Washington University in St. Louis

EMBA 35 Excel workshop
Oct 9, 23, Nov 6, 20, 2009
Olin Business School
Charles J. Cuny
Contact information: (314) 935-4527, [email protected]
Pre-course assignment
• This workshop will spend virtually all its available time
building spreadsheets (in Excel). Therefore, to set yourself
up before the workshop starts you really should:
• 1. Bring a laptop computer with Excel installed, including
both the Solver and the Analysis ToolPak add-ins (the
details on the next slides depend on your version of Excel).
• 2. Download (ahead of time) the following Excel file, for
easy access during class.
www.olin.wustl.edu/faculty/cuny/emba35/Data.xls
Pre-course assignment (Excel 2007)
• Make sure the Solver and Analysis ToolPak add-ins are
installed. For Excel 2007:
• Open Excel 2007. Go to the “Data” tab (on top), followed
by “Analysis” box (upper right). You want the box to exist,
and include both “Data Analysis” and “Solver.”
• If not, click on the upper-left (4-color) button. Click “Excel
options.” Click “Add-ins.”
• Under “Active Application Add-ins” (on top), you need
“Analysis ToolPak” and “Solver Add-in.”
• If either is in the Inactive list instead, click on “Go…” near
“Manage: Excel add-ins.” Then, click both “Analysis
ToolPak” and “Solver Add-in,” and “OK.” <Thanks!>
Pre-course assignment (Excel 2003)
• Make sure the Solver and Analysis ToolPak add-ins are
installed. For Excel 2003:
• Open Excel 2003. Go to Tools>Data Analysis and to
Tools>Solver. You need both to exist. (If, when you click
on “Tools,” you get a double-down-arrow at the menu
bottom, click it to show the full menu choice.
• If Solver and Data Analysis are not both available, click
Tools>Add-ins.
• Under “Add-ins,” click both “Analysis ToolPak” and
“Solver Add-in,” then “OK.”
• Check to make sure Solver and Data Analysis are now
available. <Thanks!>
Pre-course assignment (Mac Excel 2008)
• Mac Excel 2008 is an inferior product. (Solver and
Analysis ToolPak, which were available in Mac Excel
2004, are unavailable in 2008.) Solver allows complex
optimization; Analysis ToolPak allows statistical analysis.
(Shame on you, Microsoft!)
• The basics can be accomplished on Mac Excel 2008.
However, some useful tools will be unavailable.
• Although Solver can now be downloaded as an external
application (http://www.solver.com/mac), Analysis
ToolPak is still unavailable.
• (See next slide...)
Pre-course assignment (Mac Excel 2008)
•
•
•
•
Mac users have the following choices:
Use Excel 2007 (or Excel 2003), running Windows on the
Mac.
Use Mac Excel 2004 (which looks much like Excel 2003).
Deal with the crippled, inferior, Mac Excel 2008.
If you decide to use Excel 2007, Excel 2003, or Mac Excel
2004, please prepare according to the earlier slides. If you
decide to use Mac Excel 2008, just make sure it is
installed. <Thanks!>
Topics
• 1: Variable relationships, Pro formas, Sensitivity analysis,
Charts, Printing
• 2. Break-even analysis, Arrays and array referencing,
Chart varieties and presentation
• 3. Descriptive and logical functions, Sorting, Histograms
• 4. Circular references, Solver, Filtering, Pivot tables,
Data tables
Spreadsheets: what are they good for?
• Organization
– Putting data into readable, accessible form
• Analysis
– Assumptions and data
– Calculations
– Sensitivity to assumptions
– Conclusions
• Presentation
– Charts/graphs
– Tables
The workshop
• Building basic spreadsheet skills
– Organizing, analyzing, presenting data
– Functions
– Charts/graphs, Tables
• We will focus on actually using Excel to build
spreadsheets, and to present readable results
• There are usually multiple ways of doing anything in Excel
Variable relationships
• Consider five various possible relationships between the
quantity of goods sold (Q) and the price charged (P):
Q = 20000 – P
Q = 22000 – P2/5000
Q = 20000 – 150√P
Q = (15000 – P)2/104
Q = (8 · 107) P –1.2
• Calculate how the Q’s vary as the price P varies over the
range 1000 to 10,000 using 250 increments.
• Relative references
• Order of operations:
Parentheses, Exponent, Multiply/divide, Add/subtract
Charts
• To open a chart, highlight the interesting data and
Insert>Charts>Column (or Bar, or Scatter, etc.)
[If you click in the general area, Excel assumes what data
you want; you may be able to adjust this afterward.]
• In Excel 2007, put the chart on its own sheet with
Chart Tools>Design>Location
• In Excel 2003, step through the Chart Wizard.
• You can put the chart on its own sheet with Step 4 of Chart
Wizard. After the chart has been made, you can right-click
on the chart and choose “Location.”
Pro forma calculation
• Build a simplified pro forma for Bubblicious Drinks:
• At year-end 2009, Bubblicious has a cash balance of $1000.
• They expect to sell 70,000 units in 2010Q1, at price of $1.00/unit.
• They have fixed costs of 30,000 (labor) and 20,000 (headquarters), per
calendar quarter.
• They have variable costs for ingredients (5% of revenue), packaging
(10% of revenue), royalties (13% of revenue).
• Production requires machinery. Each machine owned has capacity of
20,000 units (per quarter). Machines cost 30,000.
• Track profits (define as revenue less all costs, as incurred) and cash
balance over future quarters, through 2012Q4. Assume all business is
done on a cash basis. Ignore taxes. Assume cash earns 0% interest.
Pro forma calculation
Will Bubblicious need to raise money by 2012Q4?
Consider these scenarios:
•
•
•
•
•
•
•
A. Assume constant production.
B. Units sold increase by 3%/quarter.
C. Units sold increase by 5%/quarter.
D. Units sold increase by 10%/quarter.
E. Units sold increase by 3%/quarter; unit price increases by 1%/quarter
F. Units sold increase by 5%/quarter; unit price increases by 1%/quarter
G. Units sold increase by 5%/quarter; unit price increases by
1%/quarter; fixed costs increase by 2%/quarter.
• Relative and absolute references
(Lock cell references with $ or rotate through F4 key.)
• Sensitivity analysis
• Excel 2007: Freeze panes by View>Window>Freeze panes
• Name cells in Name Box (upper left), or
Formulas>Defined Names
• Excel 2003: Freeze panes by Window>Freeze panes
• Name cells in Name Box (upper left), or
Insert>Name>Define
Printing
• In Excel 2007, printing adjustments are available at either
Page layout>Page setup or Four-color button>Print>Print
preview.
• In Excel 2003, printing adjustments are available under the
File heading.
• Particularly useful are:
– Set print area
– Landscape vs. portrait
– Centering on page
– Fitting to page
Variable relationships (redux)
• Suppose that the quantity of goods we sell (Q) depends
upon the price charged (P) and advertising (A) as
Q = (107) P –1.2 A.30
• Calculate how Q depends upon P and A,
for values of P ranging from 1000 to 10,000
and values of A ranging from 4000 to 14,000.
• Build a surface chart to illustrate the relationship.
(This will look much better in Excel 2007.)
Break-even analysis
• We sell our goods for price 30/unit.
Fixed production cost is 3500. Variable costs are:
Raw material (10/unit, with a 4/unit volume discount
beyond production of 250 units), Labor (12/unit, plus
6/unit overtime beyond production of 500 units).
Let’s do a break-even analysis.
• Goal seek
(In Excel 2007, Data>Data tools>What-if analysis)
(In Excel 2003, Tools>Goal seek)
• Graph how profit varies with production
(Insert>Charts>Scatter chart)
Tables
• Let’s build a cross-rate table to show exchange rates (to
convert) between various currencies. Start with the
currency rate data (see data file).
• If the TRANSPOSE( ) function is used on the array, then
rates will automatically update when the inputs change.
In using TRANSPOSE, the formula must be entered with
CTRL-SHIFT-ENTER.
Some array reference functions
• Let’s make an interface near the cross-rate currency table
to show the appropriate exchange rate conversion between
any pair of currencies on the table.
• The INDEX(array, row#, column#) function will give us
the appropriate cell of the cross-rate table (the array) if we
know the appropriate row# and column#.
• How can we translate from the currency name to the
appropriate row or column #?
We can use the exact MATCH(value, range, 0) function,
using the row and column titles as the respective ranges.
Variation and optimization
• Let’s see how the optimization goal affects the process.
• The quantity Q of units your company sells depends upon
the selling price P as follows: Q = 100 – P, for P ≥ 0.
(For prices P > 100, no units are sold.
Production cost is C = 40Q (that is, 40 per unit produced).
• Let’s build a spreadsheet to see how quantity, revenue,
cost and profit depend upon price P.
Let P vary from 100 to 0, using steps of 2.
• Name the columns. In Excel 2007, use Formulas>Defined
names>Create from selection.
In Excel 2003, use Insert>Name>Create.
Optimization goal
• Since you can increase sales by reducing price, the
question is how low to reduce price.
Consider how to pick a price to:
• Maximize market share [Q].
• Maximize revenue [PQ].
• Minimize cost [C = 40Q].
• Maximize profit [PQ – C(Q)].
• Use MATCH to find where each optimum occurs.
Use INDEX to find the corresponding price.
• Graph how (sales, cost, revenue, profit) vary with price.
• By the way: what do you really want to optimize?
Charts: varieties and presentation
• For Mock Turtle School enrollment (see data file):
• What is the total yearly enrollment by each study type
(regular, arithmetic, art, classics)?
• There are various ways we may wish to display this
information: Column chart (clustered or stacked), Bar chart
(clustered or stacked), Line chart.
(Let’s do both a clustered Column and Line.)
• We may also wish to display the relative enrollment mix
across study types (e.g., Column 100% stacked).
Descriptive functions
•
•
•
•
Consider the miscellaneous data (see data file).
What are the largest and smallest values?
How about the average(s)?
How many data points are there?
• MAX( ), MIN( )
AVERAGE( ), MEDIAN( ), MODE( )
COUNT( )
Descriptive functions (cont.)
• Consider the cereal data (see data file).
• The sales data is conveniently in column form.
• To find the largest and smallest values, the average(s), and
other interesting statistical summary measures, use
Descriptive Statistics under:
Data>Analysis>Data Analysis (Excel 2007)
Tools>Data Analysis (Excel 2003)
• Choose the Input Range (where is the data?), tick whether
a label is on top, choose the Output Range (where should
Excel put the results?), tick Summary Statistics
Logical functions
• The IF function has three parameters, as follows:
IF(statement, value if statement is true, value if statement
is false)
• IF statements can be nested, or put inside each other.
• IF can be used in combination with
AND( ), which is true (= 1) if all its statements are true;
OR( ), which is true (=1) if any of its statements are true.
• Copy the data from the blue area to the yellow area,
but only for the positive values (discard the rest).
Logical functions
• Copy the data from the blue area to the yellow area,
but only for the positive values (discard the rest).
•
•
•
•
For each of the yellow rows, check to see whether:
The maximum value is > 90
The minimum value is ≤ 10
Both the maximum > 90 and the minimum ≤ 10
Either the maximum > 90 or the minimum ≤ 10
• Some combined functions are available:
How often does the most common data value occur?
COUNTIF( )
Data sort
• Sort the Cellphone data (see data file) using Data Sort.
• In Excel 2007, this can be found at
Data>Sort & Filter
(or, mostly, at Home>Editing>Sort & Filter)
• In Excel 2003, this is at Data>Sort
• Sort first on date, and second on peak/off-peak
• After sorting the cellphone data, calculate the cost of each
entry, depending on call length, whether weekend rate
applies, and whether peak/off-peak. Sum for the month.
Histograms
• Return to the miscellaneous data worksheet.
• Copy the blue data to a new worksheet.
Build a histogram using cutoffs of 0, 20, 40, 60, 80.
• Data>Analysis>Data analysis>Histogram can be used to
summarize and graph the outcome distribution.
(In Excel 2003, Tools>Data Analysis>Histogram)
• This requires a listing of “bins” to sort the outcomes into.
You need to specify the cutoffs between the bins.
There is a “more” bin for the very highest outcomes.
• The histogram output does not update if the data changes.
Histograms
• FREQUENCY( ) also gives the distribution of values.
• This, too, requires a listing of the sorting bin cutoffs.
Don’t forget to include room for the “more” bin for the
very highest outcomes in the FREQUENCY output cells.
FREQUENCY is entered via CTRL-SHIFT-ENTER.
• Cumulative distribution can be calculated.
A chart of the histogram can be made.
• The frequency output does update if data changes.
Resolving circular references
• Let’s calculate the following:
• We give 10% of after-tax profit to charity. Revenue less
costs are $10,000. Tax is 35%; charity is tax-deductible.
• To know charity, you need to know after-tax profit.
• To know after-tax profit, you need to know tax.
• To know tax, you need to know charity.
• Fix via: (Excel 2007) Four-color button[upper left]>Excel
options>Formulas> Calculation>Enable iterative calculation
(Excel 2003) Tools>Options>Calculations>Iteration (tick)
Solver
• Solver is more powerful than Goal seek.
(Excel 2007: Data>Analysis>Solver)
(Excel 2003: Tools>Solver)
• Solver can handle numerous constraints.
• Solver is an add-in.
Examples (see data file):
• Hot tubs
• Furniture
Solver
• Solver can handle messy problems.
• SUMPRODUCT( ), perhaps with TRANSPOSE( ), can be
useful in summarizing the relevant linear constraints when
they are messy.
• TRANSPOSE( ) is entered with CTL-SHIFT-ENTER
• Solver can handle numerous (equality, inequality, integer
or binary) constraints.
Solver
• Here’s another type of problem Solver can handle.
• D and E represent a firm’s debt and equity ownership mix.
Suppose debt’s return is 4%, equity’s return is 12%, and
the weighted average return is 10%. Then:
D+E=1
4D + 12E = 10
(Ownership fractions add to 1)
(Weighted return is 10%)
Solve for D and E simultaneously.
Data sort
• Reminder: Data Sort can be found at
• In Excel 2007: Data>Sort & Filter
(or, mostly, at Home>Editing>Sort & Filter)
• In Excel 2003: Data>Sort
Filtering
• Start with the cereal sales data.
• Filter the data.
• In Excel 2007, Home>Editing>Sort&Filter>Filter or
Data>Sort&Filter>Filter
• In Excel 2003, Data>Filter (this is an inferior filter)
Midwest region only
Cereal: only Krunchies, Sugar Bombs, Vanilla Yum
State: all [qualifying]
Quarter: only 2007 year
• Copy and paste data elsewhere
Summarizing databases: pivot tables
•
•
•
•
Start with the cereal sales data.
Build a pivot table:
In Excel 2007, Insert>Tables>Pivot table
In Excel 2003, Data>Pivot table and chart>Pivot table
Constrain the data as follows:
• Report filter: Midwest region only
• Rows:
Cereal: only Krunchies, Sugar Bombs, Vanilla Yum
State: all [of the allowed Midwest states]
• Columns: only 2007 year
Summarizing databases: pivot tables
• Display total sales (in values).
• Display number of orders.
• Display both total sales and number of orders.
• Switch order of “cereal” and “state”
• Copy and paste output elsewhere.
• Switch order of “cereal” and “state” back again
(“cereal” then “state” sort)
Summarizing databases: pivot tables
•
•
•
•
•
Additional sort exercises:
Also display sales data for Fish-i-O’s and Kelp Krumbles
Hide the by-state data for those cereals
Display for AZ, CA, FL, GA only
Right-click on quarters; rearrange backward
chronologically
• Display all cereals (AZ, CA, FL, GA), totals only
• Remove quarter filter; switch State to columns
• Show average order size
Summarizing databases: pivot chart
• Start over with the cereal sales data.
• In Excel 2007, Insert>Tables>Pivot table>Pivot chart
• In Excel 2003, Data>Pivot table and chart>Pivot chart
• Rows: AZ, CA, NV
• Columns: Fish-i-o’s, Frooties, Kelp Krumbles, Krunchies
• Values: Sum of sales
• Rows: cereal; Columns: state
• Rows: cereal, state; Columns: -• Adjust the chart (remove legend, change font, etc.)
Two-way data tables
• Complete the copy center profit analysis: how does Profit
depend on Price/copy and Copies/machine?
• In Excel 2007,
Data>Data tools>What-if analysis>Data table
• In Excel 2003,
Data>Data table
• The formula belongs in the upper-left corner of the table.