EMBA 37 Excel with Business Applications

1. EMBA 37 Excel with Business Applications Oct 8, 22, Nov 5, 19; 2010Olin Business SchoolCharles J. CunyContact information: (314) 935-4527, cuny@wustl.edu

2. Pre-course assignment • We will spend virtually all our available time building Excel spreadsheets. Therefore, you should bring a laptop computer with Excel installed.Also, by the second session, please: • 1. Install both the Solver and the Analysis ToolPak add-ins (see details on the next few slides, which depend on your version of Excel). • 2. Download the following Excel file, for easy access during class.apps.olin.wustl.edu/faculty/cuny/emba37/Data.xls

3. Pre-course assignment (Excel 2010) • Make sure the Solver and Analysis ToolPak add-ins are installed. For Excel 2010: • Open Excel 2010. 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 green “file” tab. Click “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!>

4. 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!>

5. 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!>

6. 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...)

7. Pre-course assignment (Mac Excel 2008) • Mac users have the following choices: • Use a Windows-based Excel (run 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 2010, 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!>

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

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

10. 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 • Note: Excel 2010 is almost identical to Excel 2007!

11. Session 1Variable relationships • Consider four 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 = (5 · 106) P –0.8 • Let’s calculate how the Q’s vary as the price P varies over the range 1000 to 10,000 using increments of 500. • Relative references • Order of operations: Parentheses, Exponent, Multiply/divide, Add/subtract

12. 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 withChart 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.”

13. Pro forma calculation • Build a simplified pro forma for Bubblicious Drinks: • At 2009Q4, Bubblicious has a cash balance of $3000. • Expect to sell 72,000 units in 2010Q1, priced at $1.05/unit  • Fixed costs of 30,000 (labor) and 20,000 (headquarters), per calendar quarter.  • Variable costs for ingredients (5% of revenue), packaging (10% of revenue), royalties (13% of revenue). • Production requires machinery. Each machine can produce 20,000 units (per quarter). Machines cost 30,000. • Track cash flow (revenue less costs, as incurred) and cash balance quarterly through 2011Q4. All business is done on a cash basis. Ignore taxes. Cash earns 0% interest.

14. Pro forma calculation • Will Bubblicious need to raise money by 2011Q4?Consider these scenarios: • A. Units sold are constant. • B. Units sold increase by 3%/quarter. • C. Units sold increase by 6%/quarter. • D. Units sold increase by 3%/quarter; fixed costs increase 2%/quarter. • E. Units sold increase by 6%/quarter; fixed costs increase 2%/quarter.(Leave on Scenario E.)

15. “Expanded presentation” Aggregate quarters to get annual data for 2010 and 2011.Expanded presentation: Calculate $ difference (2010 to 2011) Calculate % difference (2010 to 2011) Calculate numbers as % of revenue (2011 only)

16. 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), orFormulas>Defined Names • Excel 2003: Freeze panes by Window>Freeze panes • Name cells in Name Box (upper left), orInsert>Name>Define

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

18. Session 2Break-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)

19. 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 withCTRL-SHIFT-ENTER.

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

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

22. 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?

23. 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).

24. Session 3Descriptive functions Consider the miscellaneous data (see data file) in blue. What are the largest and smallest values? How about the average(s)? How many data points are there? MAX( ), MIN( )AVERAGE( ), MEDIAN( ), MODE( )COUNT( )

25. 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, useDescriptive 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

26. 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 withAND( ), which is true (= 1) if all its statements are true;OR( ), which is true (=1) if any of its statements are true.

27. 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( )

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

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

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

31. Session 4Resolving 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)

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

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

34. 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 (Ownership fractions add to 1) 4D + 12E = 10 (Weighted return is 10%)Solve for D and E simultaneously.

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

36. 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 onlyCereal: only Krunchies, Sugar Bombs, Vanilla YumState: all [qualifying]Quarter: only 2007 year Copy and paste data elsewhere

37. 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 tableConstrain the data as follows: Report filter: Midwest region only Rows: Cereal: only Krunchies, Sugar Bombs, Vanilla YumState: all [of the allowed Midwest states] Columns: only 2007 year

38. 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)

39. Summarizing databases: pivot tables Additional possible sorting 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

40. 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.)

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