WEMBA Excel Review

April 2003

This page intentionally left blank.

Contents Page

Introduction

Suggested Excel Resources.................................................................................. 1

Working Smarter Demonstration of Selected Excel Efficiency Techniques..................................... 3

Key Tools for Decision Models Model Building Bad and Good Formula Technique......................................................................3 Formula Review...................................................................................................4 Logic Practice ......................................................................................................6 More Logic Practice.............................................................................................14 Charting Basics....................................................................................................17 Addressing...........................................................................................................17 Excel Auditing.....................................................................................................18 Using Online Help...............................................................................................18 Answer Key Answer Key for the Paper-And-Pencil Exercises ....................................19

Handouts 3.5” disk with Excel Review Files Also see: http://faculty.fuqua.duke.edu/~pecklund/ExcelReview/WEMBAXLReview.htm § Excel Spreadsheets: Getting Started § Efficiency Techniques in Excel § Excel Design & Audit Tips § Notes on Excel Calculations § Practice with Excel Modeling: Oak Products § The Svelte Glove Problem: Step-by-Step § Padgett’s Widgets § Gila Shoes Exercise: IF, MAX, and MIN Step-by-Step § Creating an XY Scatter Plot § Excel Proficiency Exercises with Suggested Solutions

This page intentionally left blank.

1

Suggested Excel Resources Spring 2003

Tutorial and Reference Guides (softcover books)

EXCEL 2002: THE COMPLETE REFERENCE Ivens and Carlberg, Osborne, reference text. ISBN 0-07213-2450, paperback, 759 pages

Excel 2002 Formulas John Walkenbach, Wiley, formula reference. ISBN 0-76454-800-X, paperback, 825 pages, CD ROM

Special Edition Using Microsoft Excel 2002 Blattner, Que, reference text. ISBN 0-78972-5118, paperback, 953 pages Microsoft Excel Version 2002 Inside Out Dodge, Microsoft Press, reference text. ISBN 0-7356-1281-1, paperback, 1104 pages, CD ROM Microsoft Excel 2002 Step by Step Frye, Microsoft Press, beg/int tutorial guide. ISBN 0-7356-1296-X, paperback, 352 pages, CD ROM Microsoft Excel XP/2002: Illustrated Complete Cram, Course Technology, beg/int tutorial guide. ISBN 0-61904-5051, paperback

MBA’s Guide to Microsoft Excel 2002 Nelson and Maguiness, Redmond Technology Press. ISBN 1-931150-01-X, paperback

Effective Executive’s Guide to Excel 2002 Nelson and Maguiness, Redmond Technology Press. ISBN 1-931150-08-7, paperback

Microsoft Excel XP/2002: Comprehensive Ageloff, Course Technology, intensive tutorial guide ISBN 0-61902-092-X, paperback, 808 pages This text offers a case-based, problem-solving approach especially recommended for a good MBA pre-program review. It's not necessary to go through the entire book. Instead, we recommend you focus on the discussions and complete the primary exercises from the following list of sections. The data files used in the exercises may be downloaded from the publisher's website. See the textbook for complete instructions. Tutorial 1: Using Excel to Manage Financial Data Tutorial 2: Working with Formulas and Functions Tutorial 3: Developing a Professional-Looking Worksheet Tutorial 4: Working with Charts Tutorial 6: Working with Multiple Worksheets and Workbooks Tutorial 7: Developing an Excel Application Tutorial 9: Data Tables and Scenario Management Tutorial 10:Using Solver for Complex Problems

2

Sources for Video/CD-ROM Excel Courses on Excel

§ KeyStone Learning Systems, Video or CD-ROM formats. Beginning and intermediate training. Order online or 1-800-581-9732.

§ LearnKey Training, Video or CD-ROM formats. Order online or 1-800-865-0165.

Excel Resource Websites Personal websites with Excel tips

www.beyondtechnology.com www.cpearson.com www.j-walk.com www.accountingadvisors.com http://geocities.com/aaronblood/ For more like this, see: http://www.mvps.org/links.html#Excel

Sample spreadsheets (on equity valuation, derivatives, and portfolio analysis) http://members.attcanada.ca/%7Ejohnjaz/spreadsheets.htm

Microsoft’s own web pages for Excel XP

General Information http://www.microsoft.com/office/excel/default.asp

The Support Center (Includes Highlights & Top Issues, Step-by-Step Instructions and How-To Articles, Newsgroups, and more) http://support.microsoft.com/default.aspx?scid=fh;EN-US;xl2002

The Knowledge Base (A search page; look here for articles on specific topics written by Microsoft professionals, bug reports, bug fixes) http://support.microsoft.com/default.aspx?scid=fh;EN-US;kbhowto&sd=GN&ln=EN-US&FR=0

A Fuqua Excel Review website (with links to the resources mentioned above)

http://faculty.fuqua.duke.edu/~pecklund/excelreview/ExcelReview.htm

3

Working Smarter Demonstration This demonstration reviews some of the most useful general techniques in Excel and techniques that can help you work more efficiently. If you’d like to follow along with the demonstrations by referencing the written explanations, use the printout named

Efficiency Techniques in Excel

If you have a good Excel reference guide in your library you may want to explore these topics in more depth.

Key Tools for Decision Models Model Building Dr. Laura Kornish, your Decision Models professor, has identified key Excel tools and techniques that you need to be successful with and enjoy Excel model building in her course. We use a series of short workbooks and exercises to review and practice those techniques. Bad and Good Formula Writing Techniques

File name: BadAndGoodFormulaTechnique.xls

4

Formulas Review

Logic Practice

File name: FormulaReview.xls

File name: LogicPractice.xls

5

Logic Practice on Paper – Using Logical Functions in Modeling

Exercise 1. Using Excel’s AND, OR, and NOT Functions File: LogicPractice.xls , Worksheet: “AND OR NOT”

A

1 The Data

2 15

3 9 4 8

A. Write an AND formula to determine if A2>A3 and A2<A4 is a true or false statement. B. Write an OR formula to determine if A2>A3 or A2<A4 is a true or false statement. C. Write a formula that expresses that A2+A3=24 is a false statement.

6

Logic Practice on Paper – Using Logical Functions in Modeling

Exercise 2. Using Excel’s IF Function File: LogicPractice.xls, Worksheet: “IF”

A

1 The Data

2 50

A. Write an IF statement so that if the number in Cell A2 is less than 100 the formula

displays the text “Within budget”, otherwise the formula displays the text “Over budget”.

B. Write an IF statement so that if the number in Cell A2 is 100 then the formula sums

the range B5:B15. Otherwise, the formula returns a blank (empty text).

7

Logic Practice on Paper – Using Logical Functions in Modeling

Exercise 3. More Practice with IF Functions File: LogicPractice.xls, Worksheet: “IF Scores”

A

1 Scores

2 45

3 90 4 78

A. Write an IF statement to assign a letter grade to the score in Cell A2. B. Write an IF statement to assign a letter grade to the score in Cell A3. C. Write an IF statement to assign a letter grade to the score in Cell A4.

If Score is Then return

Greater than 89 A

From 80 to 89 B

From 70 to 79 C

From 60 to 69 D

Less than 60 F

8

Logic Practice on Paper – Using Logical Functions in Modeling

Exercise 4. IF Function Practice (from the Proficiency Exercises) File: LogicPractice.xls, Worksheet: “Olive Oil Logic -1”

Olive oil can be purchased according to this price schedule:

A B 1 Cost/gallon for the first 500 gallons $23 2 Cost/gallon for gallons above 500 $20 3 Number of gallons: 4 10 5 483 6 500 7 1,600

Write IF statements to calculate the cost of the quantities of olive oil listed in Cells A5, A6, and A7 above. (See a sample formula to calculate the cost of 10 gallons (A4) below.) The syntax of Excel’s IF function is: =IF(condition-to-test, value-if-condition-true, value-if-condition-false) For example, a formula to find the cost for 10 gallons of olive oil is: =IF(A4<=500,B1* A4, 500*B1+(A4-500)*B2) See the footnote on this page.* A. Write a formula to find the cost of 483 gallons. B. Write a formula to find the cost of 500 gallons. C. Write a formula to find the cost of 1,600 gallons. Note: It’s always a good idea to use cell references instead of constant values in formulas. For the examples here, however, I’ve used some constants to make the formulas easier to read and understand.

9

Logic Practice on Paper – Using Logical Functions in Modeling

Exercise 5. Building & Using a Nested IF Statement File: LogicPractice.xls, Worksheet: “Olive Oil Logic -2”

We’ve modified the olive oil price schedule to give an additional price break for quantities over 1,000 gallons. The new pricing schedule is:

A B 1 Cost/gallon for the first 500 gallons $23 2 Cost/gallon for next 500 gallons $20 3 Cost/gallon for gallons > 1,000 $15 4 5 1600 6 483 7 2001

Write two formulas using nested IF statements to calculate the cost of the quantities of olive oil listed in Cell A6 (483 gallons) and Cell A7 (2,001 gallons) above. An Excel nested IF function can be written with this syntax: =IF(condition-to-test, IF(condition-to-test, value-if-condition-true, value-if-condition-false), value-if-condition-true, value-if-condition-false) For example, one formula to find the cost for 1,600 gallons is: =IF(A5<=500,A5*$B$1,IF(A5<=1000,(500*$B$1)+(A5-500)*$B$2,(500*$B$1)+(500*$B$2)+(A5-1000)*$B$3))

A. Write a formula to find the cost of 483 gallons. B. Write a formula to find the cost of 2,001 gallons.

10

Logic Practice on Paper – Using Logical Functions in Modeling

Exercise 6. The IF Function, the MIN Function and the SUMPRODUCT Function File: LogicPractice.xls, Worksheet: “Olive Oil Logic -3”

The price schedule for olive oil is the same but the data layout has changed, as illustrated below. In this view, the costs for each of the quantities (Cells G6 through I6) have already been calculated. The answers are in Cells G12:I12.

Write formulas that use the MIN function, the IF function (nested), and the SUMPRODUCT function to calculate the cost of the quantities of olive oil listed in Cells H6 (483 gallons) and I6 (2,001 gallons), above. The syntax of Excel’s MIN function is: =MIN(number1, number2, …) One way to write Excel’s nested IF function is: =IF(condition-to-test, IF(condition-to-test, value-if-condition-true, value-if-condition-false), value-if-condition-true, value-if-condition-false) Excel’s SUMPRODUCT function multiplies corresponding components in the given ranges and returns the sum of those products. One way to write Excel’s SUMPRODUCT function is: =SUMPRODUCT(range1, range2) where ranges 1 and 2 hold components you want to multiply and then add. (Both ranges must be the same length.)

11

Exercise 6, Continued

For example, formulas to calculate the cost of 1,600 gallons are located below in Cells G9, G10, G11, and G12.

A. Write the four formulas to calculate the cost of 483 gallons. B. Write the four formulas to calculate the cost of 2001 gallons.

12

More Logic Practice

More Logic Practice on Paper – Writing a Formula to Account for Multiple Conditions

Exercise 7. Calculate Employee Retirement & Health Plan File: MoreLogicPractice.xls, Worksheet: “Benefit Calculations”

A company contributes to each eligible employee’s retirement plan at the rate of 4% of the employee’s annual salary. However, to be eligible for this benefit, an employee must have full-time status with two or more years of employment. A calculation for the retirement contribution requires a test of two conditions: Full- or part-time status and number of years of employment. A graphical view of the conditions to test might look like this illustration:

File name: MoreLogicPractice.xls

13

There are three retirement contribution possibilities to account for:

• An employee works full time AND has been employed two or more years. The retirement benefit applies.

• An employee works full time but has NOT been employed two or more years. The retirement benefit does not apply.

• An employee does NOT work full time. The retirement benefit does not apply.

You can account for these three possibilities in a single formula. Write your formula using logical functions. There’s more than one way to write this formula. For example, you might use both the IF and AND statements or you could express the same thing with a nested IF statement.

A. Write the formula to calculate the Retirement Contribution for Gopnik. You should be

able to copy this formula down the column to get valid values for employees Mahfouz through Heller.

The company supplies two health plan options: § Up to $10K of annual coverage for employees who choose the family plan. § Up to $8K of annual coverage for employees who choose the individual plan. These benefits do not apply if the employee or employee-and-family is already covered by some other health plan. A calculation for health insurance requires a test of three conditions: Individual, Family, Already Covered. A graphical view of the conditions to test might look like this illustration that follows.

14

B. Write the formula to calculate the Health Plan Cost for Gopnik. You should be able to

copy this formula down the column to get valid values for employees Mahfouz through Heller.

15

Charting Basics Addressing

File name: Charting Basics.xls

File name: Addressing.xls

16

Excel Auditing Using Online Help

File name: UsingOnlineHelp.xls

File name: ExcelAuditing.xls

17

Answer Key for the Paper-And-Pencil Exercises Exercise 1 - Page 7 Using Excel’s AND, OR, and NOT Functions File: LogicPractice.xls Worksheet: “AND OR NOT”

A. =AND(A2>A3, A2<A4) B. =OR(A2>A3, A2<A4) C. =NOT(A2+A3=24)

Exercise 2 – Page 8 Using Excel’s IF function File: LogicPractice.xls Worksheet: “IF”

A. =IF(A2<=100,"Within budget","Over budget") B. =IF(A2=100,SUM(B5:B15),"")

Exercise 3 – Page 9 Using IF Functions to Determine Grades File: LogicPractice.xls Worksheet: “IF Scores”

A. =IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F")))) B. =IF(A3>89,"A",IF(A3>79,"B", IF(A3>69,"C",IF(A3>59,"D","F")))) C. =IF(A4>89,"A",IF(A4>79,"B", IF(A4>69,"C",IF(A4>59,"D","F"))))

Exercise 4 – Page 10 IF Function Practice (From the Proficiency Exercises) File: LogicPractice.xls Worksheet: Olive Oil Logic - 1

A. =IF(A5<=500,B1* A5, 500*B1+(A5-500)*B2)B. B. =IF(A6<=500,B1* A6, 500*B1+(A6-500)*B2) C. =IF(A7<=500,B1* A7, 500*B1+(A7-500)*B2)

Exercise 5 – Page 11 Building and Using a Nested IF Statement (from the Proficiency Exercises) File: LogicPractice.xls Worksheet: Olive Oil Logic - 2

A. =IF(A6<=500,A6*$B$1,IF(A6<=1000,(500*$B$1)+(A6-500)*$B$2,(500*$B$1)+(500*$B$2)+(A6-1000)*$B$3))

B. =IF(A7<=500,A7*$B$1,IF(A7<=1000,(500*$B$1)+(A7-500)*$B$2,(500*$B$1)+(500*$B$2)+(A7-1000)*$B$3))

18

Exercise 6 – Pages 12 and 13 IF, MIN, MAX, and SUMPRODUCT File: LogicPractice.xls Worksheet: Olive Oil Logic -3

A. =MIN(H$1, $C$4) =MAX(IF(H$1<$C$4+$C$5, H$1-$C$4, $C$5), 0) =IF(H$1>1000, H$1-1000, 0) =SUMPRODUCT($E$4:$E$6, H4:H6) B. =MIN(I$1, $C$4) =MAX(IF(I$1<$C$4+$C$5, I$1-$C$4, $C$5), 0) =IF(I$1>1000, I$1-1000, 0) =SUMPRODUCT($E$4:$E$6, I4:I6)

Exercise 7 – Pages 14-16 Retirement & Health Plan Calculations File: MoreLogicPractice.xls Worksheet: “Benefit Calculations

A. =IF(AND(C4="full time", G4>=2), E4*0.04, 0) B. =IF(D4="family", "10K", IF(D4="individual", "8K", None))