discussion

BusinessandFinanceorder22.docx

Home >Business & Finance homework help >discussion

RUNNING HEAD: DISCUSSION 1

DISCUSSION 4

To be able to apply the procedures and techniques that you will learn in this book, it is nec-essary for you to be relatively proficient in using Excel. We assume that you are familiar with the most elementary spreadsheet concepts and procedures, such as ■ opening, saving, and printing files; ■ using workbooks and worksheets; ■ moving around a spreadsheet; ■ selecting cells and ranges; ■ inserting/deleting rows and columns; ■ entering and editing text, numerical data, and formulas in cells; ■ formatting data (number, currency, decimal places, etc.); ■ working with text strings; ■ formatting data and text; and ■ modifying the appearance of the spreadsheet using borders, shading, and so on. Menus and commands in Excel reside in the “ribbon” shown in Figure A1.1. All Excel discussions in this book will be based on Excel 2016 for Windows; if you use Excel 2016 for Mac, some differences may exist, and we may point these out as appropriate. Menus and commands are arranged in logical groups under different tabs (Home, Insert, Formu-las, and so on); small triangles pointing downward indicate menus of additional choices. We often refer to certain commands or options and where they may be found in the ribbon. For instance, in the Mac version, groups are not specified. Excel provides an add-in called the Analysis Toolpak, which contains a variety of tools for statistical computation, and Solver, which is used for optimization. They will be found in the Data tab ribbon; you should ensure that these are activated. To activate them in Windows, click the File tab and then Options in the left column. Choose Add-Ins from the left column. At the bottom of the dialog, make sure Excel Add-ins is selected in the Manage: box and click Go. In the Add-Ins dialog, if Analysis Toolpak, Analysis Toolpak VBA, and Solver Add-in are not checked, simply check the boxes and click OK. You will not have to repeat this procedure every time you run Excel in the future. On Excel 2016 for Mac, go to Tools 7 Excel Add-ins and select both Analysis Toolpak and Solver.

Formulas in Excel use common mathematical operators:

■ addition (+) ■ subtraction (-) ■ multiplication (*) ■ division (/)

Exponentiation uses the ^ symbol; for example, 25 is written as 2^5 in an Excel formula. Cell references in formulas can be written either with relative addresses or absolute

addresses. A relative address uses just the row and column label in the cell reference (for example, A4 or C21); an absolute address uses a dollar sign ($ sign) before either the row or column label or both (for example, $A2, C$21, or $B$15). Which one we choose makes a critical difference if you copy the cell formulas. If only relative addressing is used, then copying a formula to another cell changes the cell references by the number of rows or columns in the direction that the formula is copied. So, for instance, if we would use a formula in cell B8, =B4-B5*A8, and copy it to cell C9 (one column to the right and one row down), all the cell references are increased by one and the formula would be changed to =C5-C6*B9.

Using a $ sign before a row label (for example, B$4) keeps the reference fixed to

row 4 but allows the column reference to change if the formula is copied to another cell. Similarly, using a $ sign before a column label (for example, $B4) keeps the reference to column B fixed but allows the row reference to change. Finally, using a $ sign before both the row and column labels (for example, $B$4) keeps the reference to cell B4 fixed no

EXAMPLE A1.1 Implementing Price-Demand Models in Excel

In Chapter 1, we described two models for predicting demand as a function of price: D = a - bP

and D = cP - d

Figure A1.2 shows a spreadsheet (Excel file Demand Prediction Models) for calculating demand for differ-ent prices using each of these models. For example, to

▶ Figure A1.2 Excel Models for Demand Prediction

calculate the demand in cell B8 for the linear model, we use the formula

=$B$4 - $B$5*A8

To calculate the demand in cell E8 for the nonlinear model, we use the formula

=$E$4*D8^ - $E$5

Note how the absolute addresses are used so that as these formulas are copied down, the demand is computed correctly.

matter where the formula is copied. You should be very careful to use relative and absolute addressing appropriately in your models, especially when copying formulas.

Copying Formulas

Excel provides several ways of copying formulas to different cells. This is extremely useful in building decision models, because many models require replication of formulas for different periods of time, similar products, and so on. The easiest way is to select the cell with the formula to be copied, and then press Ctrl-C on your Windows keyboard or Command-C on a Mac, click on the cell you wish to copy to, and then press Ctrl-V in Windows or Command-V on a Mac. You may also enter a formula directly in a range of cells without copying and pasting by selecting the range, typing in the formula, and press-ing Ctrl-Enter in Windows or Command-Enter on a Mac. To copy a formula from a single cell or range of cells down a column or across a row,

first select the cell or range, click and hold the mouse on the small square in the lower right-hand corner of the cell (the “fill handle”), and drag the formula to the “target” cells to which you wish to copy.

Useful Excel Tips

■ Split Screen. You may split the worksheet horizontally and/or vertically to view different parts of the worksheet at the same time. The vertical splitter bar is just to the right of the bottom scroll bar, and the horizontal splitter bar is just above the right-hand scroll bar. Position your cursor over one of these until it changes shape, click, and drag the splitter bar to the left or down.

■ Column and Row Widths. Many times a cell contains a number that is too large to display properly because the column width is too small. You may change the column width to fit the largest value or text string anywhere in the column by positioning the cursor to the right of the column label so that it changes to a cross with horizontal arrows and then double-clicking. You may also move the arrow to the left or right to manually change the column width. You may change the row heights in a similar fashion by moving the cursor below the row number label. This can be especially useful if you have a very long formula to display. To break a formula within a cell, position the cursor at the break point in the formula bar and press Alt-Enter.

■ Displaying Formulas in Worksheets. Choose Show Formulas in the Formulas tab. You may also press Ctrl ~ in either Windows or Mac to toggle formulas on and off. You often need to change the column width to display the formulas properly.

■ Displaying Grid Lines and Row and Column Headers for Printing. Check the Print boxes for gridlines and headings in the Sheet Options group under the Page Layout tab. Note that the Print command can be found by clicking on the Office button in Windows or under the File menu in Mac.

■ Filling a Range with a Series of Numbers. Suppose you want to build a work-sheet for entering 100 data values. It would be tedious to have to enter the num-bers from 1 to 100 one at a time. Simply fill in the first few values in the series and highlight them. Then click and drag the small square (fill handle) in the lower right-hand corner down (Excel will show a small pop-up window that tells you the last value in the range) until you have filled in the column to 100; then release the mouse.

Functions are used to perform special calculations in cells and are used extensively in busi-ness analytics applications. All Excel functions require an equal sign and a function name followed by parentheses, in which you specify arguments for the function.

Basic Excel Functions Some of the more common functions that we will use in applications include the following:

MIN(range)—finds the smallest value in a range of cells MAX(range)—finds the largest value in a range of cells SUM(range)—finds the sum of values in a range of cells AVERAGE(range)—finds the average of the values in a range of cells COUNT(range)—finds the number of cells in a range that contain numbers COUNTIF(range, criteria)—finds the number of cells within a range that meet a

specified criterion

Logical functions, such as IF, AND, OR, and VLOOKUP will be discussed in Chapter 2. The COUNTIF function counts the number of cells within a range that meet a crite-rion you specify. For example, you can count all the cells that start with a certain letter, or you can count all the cells that contain a number that is larger or smaller than a number you specify. Examples of criteria are 100, “7100”, a cell reference such as A4, and a text string such as “Facebook.” Note that text and logical formulas must be enclosed in quotes. See Excel Help for other examples. Excel has other useful COUNT-type functions: COUNTA counts the number of non-blank cells in a range, and COUNTBLANK counts the number of blank cells in a range. In addition, COUNTIFS(range1, criterion1, range2, criterion2, . . . , range_n, criterion_n) finds the number of cells within multiple ranges that meet specific criteria for each range. We illustrate these functions using the Purchase Orders data set in Example A1.2.

EXAMPLE A1.2 Using Basic Excel Functions In the Purchase Orders data set, we will find the following:

■ Smallest and largest quantity of any item ordered ■ Total order costs ■ Average number of months per order for accounts payable ■ Number of purchase orders placed ■ Number of orders placed for O-rings ■ Number of orders with A/P (accounts payable) terms shorter than 30 months

■ Number of O-ring orders from Spacetime Technologies ■ Total cost of all airframe fasteners ■ Total cost of airframe fasteners purchased from Alum Sheeting

The results are shown in Figure A1.3. In this figure, we used the split-screen feature in Excel to reduce the num-ber of rows shown in the spreadsheet. To find the small-est and largest quantity of any item ordered, we use the MIN and MAX functions for the data in column F. Thus, the formula in cell B99 is =MIN(F4:F97) and the formula in cell B100 is =MAX(F4:F97). To find the total order costs,

we sum the data in column G using the SUM function: =SUM(G4:G97); this is the formula in cell B101. To find the average number of A/P months, we use the AVERAGE func-tion for the data in column H. The formula in cell B102 is =AVERAGE(H4:H97). To find the number of purchase orders placed, use the COUNT function. Note that the COUNT function counts only the number of cells in a range that con-tain numbers, so we could not use it in columns A, B, or D; however, any other column would be acceptable. Using the item numbers in column C, the formula in cell B103 is =COUNT(C4:C97). To find the number of orders placed for O-rings, we use the COUNTIF function. For this example, the formula used in cell B104 is =COUNTIF(D4:D97, “O@Ring”). We could have also used the cell reference for any cell con-taining the text O-Ring, such as =COUNTIF(D4:D97, D12). To find the number of orders with A/P terms less than 30 months, we use the formula =COUNTIF(H4:H97, “630”) in cell B105. Finally, to count the number of O-ring orders for Spacetime Technologies, we use =COUNTIFS(D4:D97, “O-Ring”, A4:A97, “Spacetime Technologies” IF-type functions are also available for other calculations. For example, the functions

SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS can be used to embed IF logic within mathematical functions. For instance, the syntax of SUMIF is SUMIF(range, criterion, [sum range]); sum range is an optional argument that allows you to add cells in a different range. Thus, in the Purchase Orders database, to find the total cost of all airframe fasteners, we would use

=SUMIF(D4:D97, “Airframe fasteners”, G4:G97)

This function looks for airframe fasteners in the range D4:D97, but then sums the associ-ated values in column G (cost per order). The arguments for SUMIFS and AVERAGEIFS are (sumrange, range1, criterion1, range2, criterion2, . . . ,rangeN, criterionN). For exam-ple, the function

=SUMIFS(F4:F97,A4:A97,“Alum Sheeting”, D4:D97, “Airframe fasteners”)

will find the total quantity (from the sumrange in column F) of all airframe fasteners pur-chased from Alum Sheeting.

Functions for Specific Applications

Excel has a wide variety of other functions for statistical, financial, and other applications, many of which we introduce and use throughout the text. For instance, some financial models that we develop require the calculation of net present value (NPV). Net present value (also called discounted cash flow) measures the worth of a stream of cash flows, taking into account the time value of money. That is, a cash flow of F dollars t time periods in the future is worth F> 11 + i2t dollars today, where i is the discount rate. The discount rate reflects the opportunity costs of spending funds now versus achieving a return through another investment, as well as the risks associated with not receiving returns until a later time. The sum of the present values of all cash flows over a stated time horizon is the net present value:

NPV = a n

Ft t = 011 + i2t (A1.1)

where Ft = cash flow in period t. A positive NPV means that the investment will provide added value because the projected return exceeds the discount rate. The Excel function NPV(rate, value1, value2, . . .) calculates the net present value of

an investment by using a discount rate and a series of future payments (negative values) and income (positive values). Rate is the value of the discount rate i over the length of one period, and value1, value2, . . . are 1 to 29 arguments representing the payments and income for each period. The values must be equally spaced in time and are assumed to occur at the end of each period. The NPV investment begins one period before the date of the value1 cash flow and ends with the last cash flow in the list. The NPV calculation is based on future cash flows. If the first cash flow (such as an initial investment or fixed cost) occurs at the beginning of the first period, then it must be added to the NPV result and not included in the function arguments.

Using the NPV Function

A company is introducing a new product. The fixed cost for marketing and distribution is $25,000 and is incurred just prior to launch. The forecasted net sales revenues for the first six months are shown in Figure A1.4. The formula in cell B8 computes the net present value of these cash flows as =NPV(B6, C4:H4)-B5. Note that the fixed cost is not a future cash flow and is not included in the NPV function arguments.

Insert Function

The easiest way to locate a particular function is to select a cell and click on the Insert Function button 3fx4, which can be found under the ribbon next to the formula bar and also in the Function Library group in the Formulas tab. You may either type in a description in the search field, such as “net present value,” or select a category, such as “financial,” from the drop-down box.

This feature is particularly useful if you know what function to use but are not sure of

what arguments to enter because it will guide you in entering the appropriate data for the function arguments. Figure A1.5 shows the dialog from which you may select the func-tion you wish to use. For example, if we would choose the COUNTIF function, the dialog in Figure A1.6 appears. When you click in an input cell, a description of the argument is shown. Thus, if you are not sure what to enter for the range, the explanation in Figure A1.6 will help you. For further information, you could click on the Help button in the lower left-hand corner.

Date and Time Functions

In many analytics applications, a database might contain dates, such as when an order is placed or when an employee was hired. Excel can display a date in a variety of formats, such as 2/14/17 or 14-Feb-17. You may choose the standard date format (for example, 2/14/17) by selecting Date in the Number formatting box or select a custom format by selecting Custom in the Number box. Excel stores dates in a serial format. January 1, 1900 is day 1, and each sub-sequent day is numbered sequentially. Both the current date and January 1, 1900 are included in the count. So 2/14/17 is 42,780 when expressed in this format. This means there are 42,780 days between January 1, 1900 and February 14, 2017 (including both days). Therefore, to determine the number of days between two dates, you can simply subtract them. Another useful date function is DATEDIF (which surprisingly doesn’t appear in the

Insert Function list!), which can compute the number of whole years, months, or days between two dates. The syntax of DATEDIF is

DATEDIF(startdate, enddate, time unit)

The time unit can be “y,” “m,” or “d.” For instance, DATEDIF(4/26/89, 2/14/17, “y”) will return 27 (years), while DATEDIF(4/26/89, 2/14/17, “m”) will return 333 (months).

Computing Lead Times

In the Purchase Orders database, we will compute the lead time for each order, that is, the number of days between the order date and arrival date. Figure A1.7 shows the use of

the DATEDIF function. Alternatively, we could have simply subtracted the values: for example, in cell K4, use = J4-I4.

Other useful date functions are

■ YEAR(date) ■ MONTH(date) ■ DAY(date)

These functions simply extract the year, month, and day from a date or cell reference that contains a date. The function TODAY( ) displays the current date. Similar to date functions, times can be formatted in a variety of ways, such as 12:26 PM, Hours:Minutes:Seconds, or in military time. The function NOW( ) displays the cur-rent time and date.

Miscellaneous Excel Functions and Tools

In this section, we will illustrate a few miscellaneous Excel functions and tools that sup-port analytics applications. Range Names

A range name is a descriptive label assigned to a cell or range of cells. Range names can help to facilitate building models on spreadsheets and understanding the formulas on which models are based. There are several ways to create range names in Excel.

. Use the Name box. The name box is at the upper left of a spreadsheet between the ribbon and the column headers. Usually, it displays the cell reference that is selected. To name a range, first select a cell or range and enter the range name in the Name box. Range names cannot contain any spaces, so it is common to use an underscore between words.

2. Use Create from Selection. This option is particularly useful when the names you want to use are listed in the column immediately to the right or left of the cell, or in the row immediately above or below the cell or range. The next example illustrates this procedure.

EXAMPLE A1.6 Using Create from Selection to Define Range Names

In the Total Cost Model spreadsheet, we will use the text labels to the left of the numerical inputs as the range names. First, highlight the range A3:B5. Then, on the Formulas tab, choose Create from Selection. The box for the left column

will automatically be checked. Click OK. If you select any numerical cell, you will see the range name in the Name box as shown in Figure A1.9.

Use Define Name. This allows you to enter the name but also provides an option to restrict the name to the current worksheet or allow it to be used in any work-sheet of the workbook.

EXAMPLE A1.7 Using Define Name to Create a Range Name

In the Total Cost Model spreadsheet, select cell B3. Click Define Name on the Formulas tab. This will bring up a

dialog that allows you to enter a range name. Figure A1.10 illustrates this. Click OK.

After range names have been defined, we can display a summary by clicking the Name

Manager in the Formulas tab. Figure A1.11 shows this. (Note: The Name Manager button in the Formulas tab is only available in Windows. On a Mac, you can click Define Name and see a list of your range names, and then modify, add, or delete them.) This allows you to easily add, edit, or delete range names. Finally, you can apply range names to the formulas in the spreadsheet. This replaces the cell references by the names, making it easy to understand the formulas. To do this, click on the drop-down arrow next to Define Name and select Apply Names. . . . In the dia-log box, select all the names you wish to use and click OK. Figure A1.12 shows the result for the total cost cell, B7. We see that the original formula =B3+B4*B5 now displays the names.

After range names have been defined, we can display a summary by clicking the Name