Entire Solutions Manual

Chapter 2 Review Exercises 2. 1

The four Excel objects are: the workbook; the worksheet; a range of cells and a cell itself.

2.2

An object hierarchy is an ordered list of objects. An object model is the entire system of objects. Understanding the hierarchy helps us quickly locate and manipulate objects.

2.3

The hierarchy of objects in this example is the following: The excel file “Inventory.xls” contains the sheet titled “Price List” which contains the column B entitled “Purchase Price” that contains the purchase price of a pack of pens.

2.4

Excel menu includes an extra option that other Microsoft Applications do not have. This is Data. Some of the choices available in the drop-down list of this option are: Sort; Filter; Subtotals; PivotTable and PivotChart Report; etc.

2.5

In order to construct a shape such as a triangle or an oval in Excel, we do the following: a. Add the Drawing Toolbar in the on the menu bar: View > Toolbars > Check Drawing. b. To place a triangle or oval on the screen, go to the Drawing toolbar, click on the item you want (triangular, oval). The cursor will change to a thin cross and you can click and drag to change the size of the figure.

2.6

Excel allows customizing your own toolbar. To customize a toolbar, access the list of toolbars from View > Toolbars > Select Customize. A dialog box will appear that allows you to edit toolbars by modifying their commands and display options. In the Toolbars tab, select the toolbar you wish to modify. You can create a new toolbar option by clicking on New and naming your toolbar. The Attach key attaches specific toolbars to a particular workbook.

2.7

The six categories of the formatting options available in the Format Cells dialog box are: Number; Alignment; Font; Border; Patterns and Protection.

2.8

The following is a custom format for the social security number: ###-##-####.

2.9

In order to lock particular cells in a worksheet you should first protect the worksheet doing the following: Tools > Protection > Protect Sheet. This will prevent anyone from entering or changing data in the locked cells. The cells are locked, for example, when creating forms that present information that should not be modified by the users.

2.10

In order to evenly distribute the widths of a set of columns do the following: Format > Column > Standard Width.

2.11

The benefit of using AutoFormat option is that it offers a number of table designs that can be applied in a spreadsheet.

2.12

The shortcut keys for cut, copy and paste are correspondingly: CTRL + X; CTRL + C; and CTRL + V.

2.13

The shortcut CTRL + 1 displays the Format Cells menu.

2.14

The option to merge cells and wrap text appears under the Alignment tab of the cell formatting menu.

2.15

The four options for the data type of a cell entry are: numbers; text; formulas, and Boolean values.

Hands-On Exercises 2. 1

To draw the following shape, do the following: a. Add the Drawing Toolbar in the on the menu bar: View > Toolbars > Check Drawing. b. On the Drawing toolbar select: AutoShapes > Basic Shapes > Regular Pentagon. The cursor changes to a thin cross. Click and drag to change the size of the figure. c. Select the pentagon shape. Go to: Format > AutoShape > Select the tab Colors and Lines. On the section Fill, choose the yellow color to fill the shape. On the section Line, choose the blue color and the dashed line for the border of the shape.

2. 2

To create the toolbar shown below, follow these steps: a. To customize the toolbar access the list of toolbars: View > Toolbars > Customize. b. Select Toolbars tab. Click on the command button New to create a new toolbar. Name the new toolbar: Exercise 5. c. Select the Commands tab. • From the File Category, select the New command and use drag-anddrop to add the command to your toolbar. • From the File Category, select the Exchange Folder command and use drag-and-drop to add the command to your toolbar. • From the Edit Category, select the Office Clipboard command and use drag-and-drop to add the command to your toolbar. • From the File Category, select the Find command and use drag-anddrop to add the command to your toolbar.

• • • • • •

2. 3

From the Charting Category, select the Column Chart command and use drag-and-drop to add the command to your toolbar. From the Drawing Category, select the Clip Art command and use drag-and-drop to add the command to your toolbar. From the AutoShapes Category, select the WordArt command and use drag-and-drop to add the command to your toolbar. From the Tools Category, select the Control Toolbox command and use drag-and-drop to add the command to your toolbar. From the Web Category, select the Start Page command and use drag-and-drop to add the command to your toolbar. From the AutoShapes Category, select the Smiley Face command and use drag-and-drop to add the command to your toolbar.

To create the Excel spreadsheet shown below, follow these steps: a. Type Student IDs and Roll calls in Tables A and B. b. Highlight the column “Roll call” from Figure B and then choose: Format > Conditional Formatting from the menu. c. Specify the condition for the cells as follows: • Condition 1: Cell Value Is: Equal to: (type) Y. Click on the command button: Format. Under the tab Pattern, select a color that will highlight the students that were not in class. • From the Conditional Formatting window click-on the command button Add to add a new condition. • Condition 2: Cell Value Is: Equal to: (type) excused. Click on the command button: Format. Under the tab Pattern, select a color that will highlight the students that were not in class.

2. 4

To create the Excel spreadsheet shown below, follow these steps: a. Type the data in an Excel spreadsheet.

b. Highlight the first column that presents the name of the products and select Format > Cells from the menu. From the Font tab select the Font style bold. c. Highlight the first row of the table that presents the names of the columns and select Format > Cells from the menu. From the Font tab select the Font style bold and Color to white. From the Pattern tab select the black Color. d. Highlight the column Sales. Select from the menu: Format > Cells. From the Number tab: select the currency category; set decimal places to 2; select the symbol $. e. Highlight the column Percent Return. Select from the menu: Format > Cells. From the Number tab: select the percentage category; set decimal places to 2. f. Highlight the table. Select from the menu: Format > Cells. From the Font tab: select the Time New Romans font option; set font style to regular; and size to 12pt. g. Highlight the table. Select from the menu: Format > Cells. From the Alignment tab: select the Central text alignment option.

2. 5

To create the Excel spreadsheet shown below, follow these steps: a. Type the data in an Excel spreadsheet. b. Highlight the Price column. c. Select: Format > Cells from the menu. In the Cells window select the Number tab; in the Number tab select the Fraction category and As sixteenths (8/16) type.

2. 6

The following table presents the temperatures at which the substances A, B and C dissolve. Scenario 1 corresponds to dissolving 5g of a particular substance in 100mL of water. Scenario 2 corresponds to dissolving 7g of a particular substance in 50mL of water.

In order to format the table, follow these steps: a. Type the data in an Excel spreadsheet. b. Highlight the cells of the table. c. Select Format > AutoFormat > Simple from the menu. In the AutoFormat window click-on the command button Options. The Formats to Apply window appears. From this window uncheck: Numbers and Fonts. d. Highlight the column Substance. Select from the menu: Format > Cells. From the Alignment tab: select from Text Alignment options: Horizontal > Center. From the Font tab: select from Font options: Font Style > Bold. e. Highlight the columns that correspond to the Scenarios. Select from the menu: Format > Cells. From the Alignment tab: select from Text Alignment options: Horizontal > Center. 2. 7

In order to enter the data presented in this exercise in an Excel spreadsheet using the click-and-drag method we do the following: a. To create the first column, we type 1 in the first cell and 2 in the next cell. We select these two cells and use the click-and-drag method to fill-in the rest of the cells. b. To create the first column, we type 10 in the first cell and 9 in the next cell. We select these two cells and use the click-and-drag method to fill-in the rest of the cells. c. To create the first column, we type 1 in the first cell and 3 in the next cell. We select these two cells and use the click-and-drag method to fill-in the rest of the cells. d. To create the first column, we type a in the first cell, b in the second cell and c in the third cell. We select these three cells and use the click-and-drag method to fill-in the rest of the cells. e. To create the first column, we type hello in the first cell. We select this cell and use the click-and-drag method to fill-in the rest of the cells.

2. 8

In order to create the table presented below follow these steps: a. Type the data in an Excel spreadsheet. b. Highlight the first column of the table and then choose: Format > Conditional Formatting from the menu. c. Specify the condition for the cells as follows: • Condition 1: Cell Value Is: Between: (type) 10/1/02 And (type) 10/31/02. Click on the command button: Format. Under the tab Pattern, select a color that will highlight the dates corresponding to the month of October.

• •

2. 9

From the Conditional Formatting window click-on the command button Add to add a new condition. Condition 2: Cell Value Is: Not Between: (type) 10/1/02 And (type) 10/31/02. Click on the command button: Format. Under the tab Font, select Effects > Strikeout.

In order to create the table presented below follow these steps:

a. Type the data in an Excel spreadsheet. b. Highlight the column “Transaction No.” and then choose: Format > Cells from the menu. From the tab Number select Number category. Set Decimal Places to zero. c. Highlight the column “Transaction Date” and then choose: Format > Cells from the menu. From the tab Number select Custom category. From this category select: mm/dd/yy;@. d. Highlight the column “Account No.” and then choose: Format > Cells from the menu. From the tab Number select Custom category. From this category select: General and in the textbox Type, type ###-##-####. e. Highlight the columns “Withdrawal Amount”, “Deposit Amount”, “Interest Earned” and “Service Charge” and then choose: Format > Cells from the menu. From the tab Number select Currency category. From this category set Decimal Places to 2, Symbol to $ and Negative Numbers to ($1,234.10).

2. 10

In order to create the table presented below follow these steps:

a. Type the data in an Excel spreadsheet. b. Highlight the table and then choose: Format > Cells from the menu. From the tab Border set the borders of the table so in the same fashion as the table below.

c. Highlight the first row of the table (that presents column names) and then choose: Format > Cells from the menu. From the tab Alignment set the option Orientation to 45 degrees. d. Highlight the “Jan” and “Apr” columns and then choose: Format > Cells from the menu. From the tab Pattern set the option Color to blue. e. Highlight the “Feb” and “May” columns and then choose: Format > Cells from the menu. From the tab Pattern set the option Color to yellow. f. Highlight the “Mar” and “June” columns and then choose: Format > Cells from the menu. From the tab Pattern set the option Color to orange.

2. 11

2. 12

Type the data in an Excel spreadsheet.

a. In order to copy the entire table, and then paste only the values in the cells A13:D18, do the following: • Highlight the table and then chose Edit > Copy from the menu. • Set the cursor to cell A13. Chose Edit > Paste Special > check Values. b. In order to copy the names from the original table and paste them in a row to cells A20:E20, do the following: • Highlight the names from the original table and then chose Edit > Copy from the menu. • Set the cursor to cell A20. Chose Edit > Paste Special > check Transpose. c. In order to copy the original table and transpose it to cells A22:F22, do the following: • Highlight the original table and then chose Edit > Copy from the menu. • Set the cursor to cell A22. Chose Edit > Paste Special > check Transpose. 2. 13

In order to create the table presented below follow these steps: a. Type the data in an Excel spreadsheet. b. Type in the cell C4: =B4/100. Select cell C4 and use the click-and-drag method to copy the formula in the cells C5:C15. c. Highlight cells C5:C15 and then chose Edit > Copy from the menu. d. Set the cursor to cell B4. Chose Edit > Paste Special > check Values.

e. Highlight cells C4:C15 and then chose Edit > Clear Content from the menu. f. Highlight cells B4:B15 and then chose: Format > Cells. Select the tab Number. In the Number category set Decimal Places to 6.

2. 14 In order to create the Table B, starting with the information presented in Table A follow these steps: a. Type the data in an Excel spreadsheet. b. Highlight cells A1:E5 and then chose Edit > Copy from the menu. c. Set the cursor to cell A10. Chose Edit > Paste Special > check Transpose.

2. 15 In order to create the table presented below follow these steps:

a. Enter the data in an Excel spreadsheet. b. Highlight the first columns “Change in M” and “Change in N” and then choose: Format > Conditional Formatting from the menu. c. Specify the condition for the cells as follows: • Condition 1: Cell Value Is: Greater than: (type) 0.03. Click on the command button: Format. Under the tab Pattern, select the purple color to highlight the price changes greater than 3%. • From the Conditional Formatting window click-on the command button Add to add a new condition. • Condition 2: Cell Value Is: Less than: (type) -0.03. Click on the command button: Format. Under the tab Pattern, select the red color to highlight the price changes less than -3%.

2. 16 In order to create the table presented below follow these steps:

a. Enter the data in an Excel spreadsheet. b. Highlight the first column “Grade” and then choose: Format > Conditional Formatting from the menu. c. Specify the condition for the cells as follows: • Condition 1: Cell Value Is: Greater than or equal to: (type) 90. Click on the command button: Format. Under the tab Pattern, select the red color to highlight the A grades. • From the Conditional Formatting window click-on the command button Add to add a new condition. • Condition 2: Cell Value Is: Between: (type) 80 And (type) 89. Click on the command button: Format. Under the tab Pattern, select the yellow color to highlight the B grades. • Condition 2: Cell Value Is: Less than: (type) 60. Click on the command button: Format. Under the tab Pattern, select the blue color to highlight the F grades.

2. 17 In order to create the table presented below follow these steps:

a. Type the data in an Excel spreadsheet. b. Highlight the column “Date” and then choose: Format > Cells from the menu. From the tab Number select Date category. Select the Type 03/14/01. c. Highlight the column “Balance” and then choose: Format > Cells from the menu. From the tab Number select the Currency category. Set Decimal Places to 2, Symbol to $ and Negative Numbers to ($1,234.10).

2. 18 In order to create the table presented below follow these steps:

Type the data in an Excel spreadsheet. a. Highlight the column “Sales” and then choose: Format > Cells from the menu. From the tab Number select Currency category. Set Decimal places to 2; Symbol to $.

b. Highlight the column “Quarter” and then choose: Format > Conditional Formatting from the menu. Specify the condition for the cells as follows: Cell Value Is: Equal to: (type) 4. Click on the command button: Format. Under the tab Pattern, select the red color to highlight the 4th quarter’s sales. c. Highlight the column “Sales” and then choose: Format > Conditional Formatting from the menu. Specify the condition for the cells as follows: Cell Value Is: Greater than: (type) 2000. Click on the command button: Format. Under the tab Font, select Font style bold. 2. 19 In order to create the table presented below follow these steps:

a. Type the name of each column, the dates and the amounts. b. Type in cell A3, 1 and in cell A4 2. Highlight cells A3 and A4. Use the click-anddrag method to fill-in the cells A5:A22. c. Highlight cells B3:B22. Chose Format > Cells from the menu. From the tab Number select the Custom category. Set Type to General and type mm/dd. d. Highlight cells C3:C22. Chose Format > Cells from the menu. From the tab Number select the Currency category. Set Decimal Places to 2; set Symbol to $ and Negative numbers to -$1,234.10. 2. 20 In order to create the table presented below follow these steps: a. b. c. d.

Type the data in an Excel spreadsheet. Highlight the table and choose: Format > Style. Type in the Style name drop-down list: New and click the Add command button. Click-on the Modify command button. Format cell window appears.

e. In the Format cell window: • Select the Number tab. Set the Number category to Percentage and the Decimal places to 2. Click the OK button. • Select the Alignment tab. Set the Vertical and Horizontal Text Alignment to Center. Click the OK button. • Select the Font tab. Set the Font to Time New Roman; Set the Font size to 12pt. Click the OK button. • Select the Border tab. Set the Presets to Outline. Click the OK button. • Select the Pattern tab. Set the Color to Blue. Click the OK button.

Chapter 3 Review Exercises 3. 1

The four basic ways to refer top a cell are: relative referencing; absolute referencing; row absolute and column absolute referencing.

3.2

A relative reference can be changed to an absolute reference by adding the “$” mark in the address of the cell referenced. If the “$” mark is in front of the letter that presents the column it means that the column value will not change. If the “$” mark is in front of the row number it means that the row value will not change.

3.3

a. b. c. d.

3.4

R1C1 notation refers to a cell based on the cell’s relative position to the cell in which the formula is being typed.

3.5

To set R1C1 as the default notation in Excel one should do the following: Go to Tools and choose Options from the menu, then and clicking on the General tab. Check R1C1 reference style from the list of options on that tab.

3.6

=R[3]C[4]

3.7

In order to refer to a cell in a different worksheet from the one in which the formula is being used do the following: type the name of the sheet followed by an exclamation mark (!) before the cell or range name.

3.8

a. =’Sheet 2’!A2 b. = [Inventory.xls]’New York’!B5

3.9

To refer to a cell that is in a workbook not currently opened in Excel do the following: type the complete path of the workbook and worksheet surrounded by single quotation marks.

3.10

In order to name a cell/range in Excel one could: use the name window; define names; and create names.

3.11

In case that the name of a cell or range has been changed, the formula which referenced newly named cells or ranges should be updated. To do the update, choose Insert > Name > Apply from the Excel menu. The formula is automatically updated to reflect the new names of the referenced cells. In order to define a constant or name a cell we follow the same steps: choose Insert > Name > Define from the main menu. However, in case that we define a constant (different from naming a cell) we would type a number (instead of selecting a cell or range of cells) in the Refers to area in the bottom of the Define Name window.

3.12

The relative reference to cell G7 is: = G7 The absolute reference to cell A2 is: = $A$2 The row absolute reference to cell B6 is: =B$6 The column absolute reference to cell J12 is: = $J12

3.13

When defining a name we choose Insert > Name > Define from the main menu. In case that we create a name we choose Insert > Name > Create from the main menu. One can create cell/ranges name (instead of defining them) in case that the data labels are already typed in the table.

3.14

Circular referencing can occur when there is a loop of referencing between cells. A simple example would be if cell A1 had the value “=B1” and cell B1 had the value “=C1” and cell C1 had the value “=A1”.

3.15

Excel allows the users to convert column and row titles of data tables into corresponding range names. To do that one can go to Tools > Options > Calculations and select Accept Labels in Formulas. This will then allow the user to refer to a row or column of data from the table by the corresponding row or column title as if you had created a range name.

Hands-On Exercises 3. 1

We typed the data presented in this exercise in an excel spreadsheet and created column headings that describe the column’s content.

In order to give to the range of cells in each column the same name as the column headings we choose Insert > Name > Crate from the Excel menu. From the Create Names window we selected the Top Row option. In order to define a name for the whole table we choose Insert > Name > Define from the Excel menu. In the top of the Define Name window we typed WorkstationDesign. 3. 2

We typed the data presented in this exercise in an excel spreadsheet and created the following table.

In order to give to the range of cells in each column and each row a name we choose Insert > Name > Crate from the Excel menu. From the Create Names window we selected the Top row and Left column options. In order to define a name for the whole table we choose Insert > Name > Define from the Excel menu. In the top of the Define Name window we typed Results. 3. 3 a. We typed the data presented in this exercise in an excel spreadsheet. b. We typed the formula ‘=B2*B3’ in cell B5. c. We selected the range A2:B3 and chose Insert > Name > Create from the Excel menu. From the Create Names window we selected the Left column option. d. We chose Insert > Name > Apply from the Excel menu. The formula in cell B5 now shows: ‘=Length*Width’. 3. 4 a. To set the name of cell B2 to Radius follow these steps: choose Insert > Name > Define from the Excel menu. In the top of the Define Name window, in the Names in workbook area, type Radius. b. Choose Insert > Name > Define from the Excel menu. In the top of the Define Name window, in the Names in workbook area, type Pi and in the bottom of the same window, in the Refers to area, type 3.14159. c. To find the circumference of the circle type the following in cell B4: ‘=2*Pi*Radius’. d. To find the area of the circle type the following in cell B5: ‘=Pi*Radius*Radius’. e. Changing the value in cell B2 to 10 gives the following results:

3. 5

The response we get when the formula ‘=column’ is placed in cell: C1 is 1; D7 is 7; and E11 is #VALUE!.

3. 6

The following is the summary table.

We typed in worksheets ‘3.6-2000’, ‘3.6-2001’ and ‘3.6-2002’ the data from previous years. In the summary worksheet we referenced to the data given in these worksheets. The figure below presents how we performed the referencing.

3. 7

To create a name for each column and row of data, select the table (together with the labels for the rows and columns) and then choose Insert > Name > Crate from the Excel menu. From the Create Names window we selected the Top row and Left column options. To create a name for the formulas located in the ‘From Sum’ column, select the corresponding column (together with its label) and then choose Insert > Name > Crate from the Excel menu. From the Create Names window we selected the Top row option. To create a name for the formulas located in the ‘To Sum’ row, select the corresponding row (together with its label) and then choose Insert > Name > Crate from the Excel menu. From the Create Names window we selected the Left column option.

3. 8

The following table presents the Interest Earned from the bank accounts.

The following table presents how cell A2 is referenced while calculating the amount of interest earned.

3. 9

The following table presents the Interest Earned from the bank accounts for different interest rate.

The following table presents how cells A2:A4 are referenced while calculating the amount of interest earned.

3. 10 a. The name of the columns that present the Time, Distance and Velocity are defined following these steps: select the data and the corresponding labels; choose Insert > Name > Crate from the Excel menu. From the Create Names window we selected the Top row option. b. In the column that presents the velocity type ‘=Distance/Time’. The following table presents the results.

c. The velocity is recomputed using R1C1 notation.

3. 11

The following table presents the location of the cells containing the amount of transaction using R1C1 notation in reference to cell A1.

3. 12 a. To define the constant ‘Gravity’, follow these steps: choose Insert > Name > Define from the Excel menu. In the top of the Define Name window, in the Names in workbook area, type Gravity and in the bottom of the same window, in the Refers to area, type 9.81. b. Select the column labeled “Surface Area”. Choose Insert > Name > Define from the Excel menu. In the top of the Define Name window, in the Names in workbook area, type Surface_Area.

Select the column labeled “Mass”. Choose Insert > Name > Define from the Excel menu. In the top of the Define Name window, in the Names in workbook area, type Mass. Select the column labeled “Force”. Choose Insert > Name > Define from the Excel menu. In the top of the Define Name window, in the Names in workbook area, type Force. Select the column labeled “Force Per Area”. Choose Insert > Name > Define from the Excel menu. In the top of the Define Name window, in the Names in workbook area, type Force_Per_Area. c. d.

The following table presents the results from calculating the Force and the Force Per Area.

e.

The following are the formulas used to compute the Force and Force Per Area using R1C1 notation.

3. 13

The following table presents the results from calculating the corresponding degrees in Celsius.

The following table shows how relative referencing is used to calculate the values for the table of Degrees in Celsius.

The following table shows how to calculate the values for the table of Degrees in Celsius using R1C1 notation.

3. 14 a.

b.

c. 3. 15 a.

In order to name each cell in the range B2:B12 by its corresponding state abbreviation follow these steps: select all the states and the corresponding monthly sales, then choose Insert > Name > Crate from the Excel menu. From the Create Names window select the Left column option. In order to name the Monthly Sales column as ‘Monthly_Sales’ follow these steps: select the monthly sales column together with its label, then choose Insert > Name > Crate from the Excel menu. From the Create Names window select the Top row option. In cell A14 type ‘=sum(Monthly_Sales)’. The result is $8,299.02. In order to create a constant named “Multiplier” follow these steps: choose Insert > Name > Define from the Excel menu. In the top of the Define Name

b. c.

d.

3. 16

window, in the Names in workbook area, type Multiplier and in the bottom of the same window, in the Refers to area, type 10. The formula used to perform the first multiplications is: ‘=A2*Multiplier’. The following table presents the results from relative referencing of the formula:

In order to name the results from the multiplication select range B1:B7. choose Insert > Name > Define from the Excel menu. In the top of the Define Name window, in the Names in workbook area, type New_Values. In order to convert column and row titles of data into corresponding range names go to Tools > Options > Calculations and select Accept Labels in Formulas. The table below presents the total number of shipments made by each plant and the total number of shipments received by each city.

The following table presents the formulas used to calculate the sums.

3. 17

In order to convert column and row titles of data into corresponding range names go to Tools > Options > Calculations and select Accept Labels in Formulas. The table below presents the ending inventory.

The following table presents the formulas used to calculate the ending inventory.

3. 18

In order to convert column and row titles of data into corresponding range names go to Tools > Options > Calculations and select Accept Labels in Formulas. The table below presents the beginning and ending inventory for each month.

The following table presents the formulas used to calculate the beginning and ending inventory.

3. 19

Add solution to this problem in here……

3. 20

In May, Mary will have $609.50 in her savings account. The following table presents the amount of money in Mary’s account in the beginning and end of each month.

In order to calculate the balance in the beginning and end of each month we used the following formulas:

3. 21

In order to convert column and row titles of data into corresponding range names go to Tools > Options > Calculations and select Accept Labels in Formulas. The table below presents the beginning and end balance on John’s loan.

The following table presents the formulas used to calculate the beginning and end balance in John’s loan.

3. 22

The following table presents the Euclidean distances from each candidate DC location to each customer. From the results we can see that location A is the best as it minimizes the total distance traveled.

The following table presents the formulas used to calculate the distances.

3. 23 a. b. The following table presents the revenues for the months of January through December, together with the monthly revenues, annual revenues and the annual revenues per item.

c.

d.

In order to create names for the columns of the Revenues table follow these steps: select the table with the corresponding labels, then choose Insert > Name > Crate from the Excel menu. From the Create Names window select the Top row option. In order to create names for the rows of the Revenues table follow these steps: select the table with the corresponding labels, then choose Insert > Name > Crate from the Excel menu. From the Create Names window select the Left column option.

e. f.

3. 24

In order to define a name for the table follow these steps: select the table, then choose Insert > Name > Define from the Excel menu. In the top of the Define Name window, in the Names in workbook area, type Revenues. The following table presents the formulas used to calculate totals.

The following table presents the monthly and annual revenues, expenses and profits for the “Contemporary Furniture” store.

b. Select the cells that present the monthly revenues for the store. Set the cursor on cell E2. Choose: Edit > Paste Special from the Excel menu. In the Paste Special window select the Transpose option. c. Note that the solution to Hands-on Exercise 3.23 is in the same Excel file as the solution to this problem. The name of the spreadsheet with the solution to exercise 3.23 is ‘3.23’.

d. In order to create names for the columns of this table follow these steps: select the table with the corresponding labels, then choose Insert > Name > Crate from the Excel menu. From the Create Names window select the Top row option. e. In order to define a name for the table follow these steps: select the table, then choose Insert > Name > Define from the Excel menu. In the top of the Define Name window, in the Names in workbook area, type Profits. f. In order to calculate the monthly profits use the formula ‘=RevenuesExpenses’ as shown in the table above. g. In order to calculate the annual profits use the formula ‘=sum(Profits)’ as shown in the table above. 3. 25

The following table presents the total weighted scores for each location.

a. In order to create names for the columns of the table that presents the scores for each location follow these steps: select the table with the corresponding labels, then choose Insert > Name > Crate from the Excel menu. From the Create Names window select the Top row option. b. In order to define a name for the table follow these steps: select the table, then choose Insert > Name > Define from the Excel menu. In the top of the Define Name window, in the Names in workbook area, type Preference Matrix. c. The following table presents the formulas used to calculate the weighted score for each location.

d. In order to create a name for the columns of the table that presents the weighted scores follow these steps: select a column at a time, then choose

Insert > Name > Define from the Excel menu. In the top of the Define Name window, in the Names in workbook area, type JacksonvilleWS (or OrlandoWS, or MiamiWS). e. In order to define the best location, calculate the total weighted score for each location. The tables above show how to calculate the total weighted scores and provide the corresponding results. Jacksonville is the best location.

Chapter 4 Review Exercises 4. 1

There are ten categories of functions in Excel: Financial, Date and Time, Mathematics and Trigonometry, Statistical, Lookup and Reference, Database, Text, Logical, Information, and Engineering.

4.2

The six Logical functions in Excel are: TRUE, FALSE, IF, NOT, AND and OR.

4.3

The IF function takes three parameters: condition to be met, return value if true, and return value if false.

4.4

The three data types that can be determine with the TYPE function are: number, text, and logical values. The number 1 signifies a numerical data type; the number 2 signifies a text data type; and the number 4 signifies a logical value data type.

4.5

The match type codes used by the MATCH function return: the code 0 returns the location of the first value it finds that is equal to the value for which we are searching; the code 1 returns the location of the largest value that is less than or equal to our specified value (given that the data is in ascending order); and the code –1 returns the location of the smallest value that is greater than or equal to our value (given that the data is in descending order).

4.6

Excel uses the serial number to enumerate all dates and times. For dates, this number considers January 1, 1990 to be the initial starting point; for time, the initial starting point is at zero hours, zero minutes, and zero seconds counting toward the current time on a 24-hour scale.

4.7

The NETWORKDAYS function returns the total number of working days between two dates. The DAYS360 function returns the total number of days between two dates. The NETWORKDAYS counts 6 days between a Monday (say October 14, 2002) to the next Monday (October 21, 2002); and the DAYS360 function returns 7 days.

4.8

There are three different options for the second parameter of the WEEKDAY function. The parameter takes the value 1 (this is also the default value if we leave this parameter blank) if Sunday is enumerated as day 1 and Saturday as day 7 of the week; the parameter takes the value 2 if Monday is enumerated as day 1 and Sunday as day 7 of the week; the parameter takes the value 3 if Monday is enumerated as day 0 and Sunday as day 6 of the week.

4.9

The first way to generate a random number between 1 and 20 is by using the RANDBETWEEN() function in Excel (‘=RANDBETWEEN(1,20)’). The other alternative is to use the function RAND(). This function generates a number

between 0 and 1. However, we could use the following formula to get a random number between 1 and 20: ‘=RAND()*20’. 4.10

The NPER function in Excel is a financial function. It is used to calculate the number of payments (or periods) left. This function takes the rate, payment per period, and unpaid amount as parameters to calculate the number of payments that remain to pay off the unpaid amount.

4.11

To calculate the sum of year’s digits, one can use the SYD function in Excel. This function calculates the sum of the year’s digits for a given period in the depreciation calculation. The parameters needed in the SYD function are the initial cost, salvage at the last period, useful life of the asset, and the period in which we are interested.

4.12

The following are the four categories of formulas: simple values, basic operators, naming and referencing, and functions.

4.13

The icon fx leads to the function menu.

4.14

The function PI in Excel returns the value of pi.

4.15

When the CONCATENATE function in Excel is used, the cells to be concatenated do not need to be neighboring.

4.16

The COUNTBLANK function in Excel returns the number of empty cells in a range.

4.17

The YEARFRAC function would be useful in accounting, or in preparing taxes, etc. For example, a company buys equipment sometime in July. In the end of the year the amount depreciated for the first years will be calculated considering that the equipment has been used only for a fraction of the year (assuming the halfyear convention is not considered). In this case, YEARFRAC function would be used to calculate the amount of depreciation for the current year.

4.18

HLOOKUP function searches for data in a spreadsheet. HLOOKUP searches for a value in the top row of a table, marks the column that contains that value, and then returns a value from that column for a specified row.

4.19

The INDEX function searches for an entry in a given range (the name of the range where the function will be looking for the particular entry is given as a parameter) and the first column and row of a range is numbered 1. In the OFFSET function, we are given a reference cell instead, and the search is performed with respect to this cell. The reference cell in the OFFSET function is similar to the cell number 1 that corresponds to the first column and first row in the INDEX function.

4.20

The MMULT function in Excel is used to multiply matrixes. As a result, the number of columns of one matrix must equal the number of rows of the other matrix.

Hands-On Exercises 4. 1

The MATCH function searches the data and returns the location of a desired value. The match type code 0 returns the location of the first value it finds that is equal to the value for which we are searching. Therefore, since number 5 is not on our array, we get an error (#N/A). When the match type code is 1, the function returns 2. That means the largest value that is less than or equal to 5 (given that the data is in ascending order -- as it is in our case) is in the second row of our array. When the match type code is –1, we get an error (#N/A). The reason is that in order to use the code -1, our data should be sorted in a descending order.

4. 2

In order to find the sum, average, minimum and maximum of the numbers given, do the following: a. Typed the numbers in cells B2:B10. b. In cell B11 type: ‘= sum(B2:B10)’ in order to calculate the sum. The result is 70.2. c. In cell B12 type: ‘= average(B2:B10)’ in order to calculate the average. The result is 7.8. d. In cell B13 type: ‘= min(B2:B10)’ in order to find the minimum. The result is 1.9 e. In cell B14 type: ‘= max(B2:B10)’ in order to find the maximum. The result is 14.

4. 3

The following table demonstrates how to use the formula to calculate the height of the cylinder given the volume and the radius.

4. 4

In order to find the net present value of the given series of cash flow, do the following: a. Type the numbers in cells A2:A8. b. In cell A9 type the formula: ‘= NPV(0.08,A2:A8)’. The result is $2,841.62.

4. 5

The following table presents how the logical functions in Excel can be used to find the acceptable buildings.

The results show that buildings C and H are the only ones to be accepted. 4. 6 a. In order to generate the random numbers less than 10 based on the algorithm described on this problem, do the following: • In cell A2 type: ‘=RANDBETWEEN(0,10000)’. • In cell B2 type: ‘=IF(A2>11, SQRT(A2),A2)’. This function checks whether the value in cell A2 is less than or equal to 10. If this is not the case, return the square root of A2, otherwise return A2. • In cell C2 type: ‘=IF(B2>11, SQRT(B2),B2)’. This function checks whether the value in cell B2 is less than or equal to 10. If this is not the case, return the square root of B2, otherwise return B2. • Copy the formula in cell B2 to B3:B21, and the formula in cell C2 to C3:C21.

Note that in 2 iterations we will be getting numbers less than 10. The figure above presents the formulas used in this exercise. b. This method does not appear to be very good method to use to calculate random numbers between less than 10. The reason is because we are using a number of Excel functions, such as the RANDBETWEEN(), IF() and SQRT(), while we could find the randomly generated numbers simply using the formula: ‘=RANDBETWEEN(0,10)’. 4. 7 The following table presents the results for this problem.

The logical function we used to identify the type of meal the patient should receive, is given below: =IF(AND(IF(AND(D2="High", OR(B2>69,C2>201)),"Low Sodium Meal", "Regular Meal") = "Low Sodium Meal", IF(AND(E2="High", OR(B2>69,C2>201,D2="High")), "Low Cholesterol Meal", "Regular Meal") ="Low Cholesterol Meal"),"Low Sodium and Low Cholesterol", IF(IF(AND(D2="High", OR(B2>69,C2>201)),"Low Sodium Meal", "Regular Meal") = "Low Sodium Meal", IF(AND(D2="High", OR(B2>69,C2>201)), "Low Sodium Meal", "Regular Meal"), IF(AND(E2="High", OR(B2>69,C2>201, D2= "High")), "Low Cholesterol Meal", "Regular Meal"))) 4. 8 The following table presents the results for this problem.

The following table presents the formulas used to calculate average acceleration as well as the maximum velocity and maximum average acceleration.

4. 9 The following table presents the results for this problem.

The following table presents the logical functions used.

4. 10 The following table presents the results for this problem.

The following table presents the logical functions used.

4. 11 The future value is $579.49. Use the FV() function to calculate the future value of the cash flow.

4. 12 The following table presents the results for this problem.

The following table presents the formulas used.

4. 13 The following table presents the results for this problem.

The following table presents the formulas used.

4. 14 The following table presents the results for this problem.

The following table presents the formulas used.

4. 15 The following table presents the results for this problem.

The following table presents the formulas used.

4. 16 The following table presents the results for this problem.

The following formula is used to calculate the “Days Until Next Week” in cell C4: ‘=DAYS360($B$1,B4)’. This formula is copied to the range C5:C11. The following formula is used to calculate “Day of Week” in cell D4: =IF(WEEKDAY(B4,1) = 1, "Sunday", IF(WEEKDAY(B4,1) = 2, "Monday", IF

(WEEKDAY(B4,1) = 3, "Tusday", IF(WEEKDAY(B4,1) = 4, "Wednsday", IF(WEEKDAY(B4,1) = 5, "Thursday", IF(WEEKDAY(B4,1) = 6, "Friday", IF(WEEKDAY(B4,1) = 7, "Saturday"))))))). This formula is copied to the range D5:D11. 4. 17 The following table presents the results for this problem.

The following table presents the formulas used.

4. 18 The following table presents the results for this problem.

The following table presents the formulas used.

In order to return the flight number of all flights being cancelled in a single cell, we used the CONCATENATE and VLOOKUP functions as shown below: = CONCATENATE(IF(VLOOKUP(A2,$A$2:$F$8,3)="Chicago", A2," "), IF(VLOOKUP (A3,$A$2:$F$8,3) ="Chicago", A3," "), IF(VLOOKUP(A4,$A$2:$F$8,3)="Chicago", A4," "), IF(VLOOKUP(A5,$A$2:$F$8,3)="Chicago", A5," "), IF (VLOOKUP (A6,$A$2:$F$8, 3) = "Chicago", A6," "), IF(VLOOKUP(A7,$A$2:$F$8,3)="Chicago", A7," "), IF (VLOOKUP (A8,$A$2:$F$8,3)="Chicago", A8," "))

4. 19 The following table presents the results for this problem.

The following table presents the formulas used.

4. 20 The following are the formulas used in this problem.

4. 21 The following are the results for this problem.

The following table presents the formulas used.

4. 22 The following are the results for this problem.

The following are the formulas used.

4. 23 The following are the results for this problem.

The following are the formulas used.

4. 24 The following are the results for this problem. a. 3 b. France c. Canada The following are the formulas used: a. =COUNTIF(B3:B11,">400") b. =OFFSET(A2,MATCH(MAX(C3:C11),C3:C11,0),0) c. =CONCATENATE(IF(AND(D3<601,D3>499),A3,""), IF(AND (D4<601, D4 > 499), A4,""), IF(AND(D5<601,D5>499),A5,""), IF(AND(D6<601,D6>499),A6, ""), IF(AND(D7<601,D7>499),A7,""), IF(AND(D8<601,D8>499),A8,""), IF( AND(D9<601,D9>499),A9,""), IF(AND(D10<601,D3>499),A10,""), IF(AND (D11<601, D11>499), A11,"")) 4. 25 The following are the results for this problem.

The following are the formulas used:

4. 26 The following are the results for this problem.

The following are the formulas used:

4. 27 The following are the results for this problem.

The following are the formulas used:

4. 28 The following are the results for this problem.

The following are the formulas used:

4. 29 a. The mean of the sample is 29.2, the standard deviation is 8.11. To calculate the mean use the function ‘=AVERAGE(A1:D9)’. To calculate the standard deviation use the function ‘=STDEV(A1:D9)’. b. The results are:

The functions used are:

c. The results are:

The functions used are:

d. The result from the Chi-square test is 0.99990.9. The Excel function used is ‘=CHITEST(C15:C20,E15:E20)’. The critical value is equal to 11.07. We find the critical value using the following function ‘=CHIINV(0.05,5)’. Since the critical value is greater than the test statistics, we accept the hypothesis that the data is normally distributed with mean 29.2 and standard deviation 8.11. 4. 30 a. The upper control limit is 0.266. The lower control limit is 0. To find the upper control limit use the formula: =AVERAGE(B2:B7,D2:D7)+3*STDEV (B2:B7, D2:D7). To find the lower control limits use the formula: =MAX(AVERAGE (B2:B7, D2:D7) - 3*STDEV(B2:B7,D2:D7), 0). b. None of the observations is above or below the control limit. To count the number of observations above the upper control limit use the formula: =COUNTIF(B2:B7,">0.226") + COUNTIF(D2:D7,">0.266"). To count the

number of observations below the lower control limit use the formula: =COUNTIF (B3:B8,"<0") + COUNTIF(D3:D8,"<0").

Chapter 5 Review Exercises 5. 1

There are ten different chart types in Excel: Column; Bar; Line; Pie; XY (Scatter); Area; Doughnut; Radar; Surface; Bubble; and Stock.

5.2

Using Excel Charts is useful as it allow the user to illustrate the data graphically or display data patterns. This helps to perform better analyses.

5.3

A Data Range is the group of cells that selected to create a chart. A range is any group of cells that form a row, column, or area.

5.4

To begin using the Chart Wizard, one can either choose Insert > Chart from the menu or click the

icon at the top of the window.

5.5

The steps followed to create a chart using the Chart Wizard are: Step 1 is to select the Chart types to be used. Step 2 is to verify or change the Data Range and determine how to define the Series. (a) Data Range selection and row versus column option for Series, (b) Range selections and names for each Series. Step 3 defines the Chart Options. Step 4 is to select the location of our chart.

5.6

In order to create a stock chart one should have three series of data. These charts are used when plotting the price of a stock, bond, etc. The series of data should be ordered as follows: High-Low-Close. For example, when one plots the daily price of a stock, data about the highest, lowest and closing price are needed.

5.7

The six tabs found on the Chart Options form are: Titles; Axes; Gridlines; Legend; Data Labels and Data Table.

5.8

When creating a chart, in the Chart Source Data form, select the Series tab. In this window, one can give names to different series used in the chart.

5.9

The two main options to locate a chart are: As new sheet and As new object. If the first option is chosen, Excel will maximize the chart to fill an entire new worksheet. Choosing to have the chart as an object in a sheet will leave it as a separate smaller window in the current worksheet or another sheet of your selection.

5.10

After creating a chart using the Chart Wizard, you can return to any of the Wizard steps by simply right-clicking on the chart. You will see a long list of options including Source Data. Selecting this option will bring you back to the corresponding Wizard dialog box where you can modify any of the initial settings made while creating the chart.

5.11

The general patterns, font and properties of a chart can be modified using the Format Chart Area form. The Format Plot Area option enables formatting of the plot area. The Format Data Series option enables formatting of point shapes, line styles, colors and views of data series. The Format Data Point option enables formatting specific data points. The Format Axis option enables formatting pattern, font, scale, number type, and alignment of the x and y-axis. The Format Gridlines option enables formatting the gridlines in a chart. The Format Legend option enables formatting the legend of a chart.

5.12

Yes the scale of gridlines can be changed. To change the scale of gridlines double click on the gridlines of the chart. The Format Gridlines form appears. Select the Scale tab to change the scale of the gridlines.

5.13

A dynamic chart is a chart that is linked directly to the Data Range specified in the Source Data step of the Chart Wizard. If any points in this range of data are modified, the chart is automatically updated to reflect a new corresponding data point.

5.14

A dynamic range is such that every time a new data point is entered the range (the range could be either a column or row in the table) the definition of the range names is updated to include this new data.

5.15

In order to change the color of a data series one double clicks on the series of data on the chart. The Format Data Point form opens. Select the Pattern tab. One can then choose a color for the data series.

Hands-On Exercises 5. 1

The following is the ‘Velocity and Acceleration of a Moving Object’ chart.

To create the chart, follow these steps: a. Select the rows with the data on time, velocity and acceleration. b. Select Insert > Chart from the main menu. c. From the Chart Type form select XY (Scatter). d. From the Chart Source Data form select the Series tab. Name series 1 as Velocity and series 2 as Avg. Acceleration. e. In the Chart Option form type the title of the chart and the name of x-axis. f. In the Chart Option form select the Legend tab. Choose the Bottom option. 5. 2

The following presents the Scatter Plot, Line Plot with Nodes and Line Plot with no Nodes charts. To create the Scatter Plot chart follow these steps: a. Select the row with the data. b. Select Insert > Chart from the main menu. c. From the Chart Type form select XY (Scatter). d. From the Chart Source Data form select the Series tab. Name series as S. e. In the Chart Option form type the title of the chart and the name of x-axis. To create the Line Plot with Nodes chart follow steps a through e as shown above. Update step c as follows: c. From the Chart Type form select Line. Select a Chart Sub-type that presents a line with nodes on it. To create the Line Plot with no Nodes chart follow steps a through e as shown above. Update step c as follows: c. From the Chart Type form select Line. Select a Chart Sub-type that presents a line with no nodes on it.

5. 3

The following tables present the values for the functions Y = ex and Y = log(ex).

The following are the corresponding graphs.

a. The function Y = ex is convex up. b. The function Y = log(ex) is linear. 5. 4

The following presents the bar graph, scatter chart and pie chart for the data given in this problem.

5. 5

The following are the scatter charts created based on the data given in exercise 5.4. The chart on the right includes some formatting changes, such as: the color of the data series is different, the scale of Y axis has changed and the data values for each series are displayed. In order to change the color of the data series, one double clicks on the graph (on the line that presents the data series). Format Data Series window appears. In the window one can change the color of the data series. In order to change the scale of the Y-axis one can double click on the Y axis. The Format Axis window appears. Choose the Scale tab to make changes on the scale of the Y axis.

Double click on the data series. Format Data Series window appears. Select the Data Labels tab. Check the Label Contains Y Values option.

5. 6

The following graph presents the relationship between the height and radius of a cylinder.

5. 7

The following table presents the data for this problem.

In order to compare the different materials, it is better to use the doughnut chart. This chart helps understand the relative importance of each factor on each material. As the tensile strength is the most important factor, the engineer should

look at the second ring of the doughnut. From the chart one can see that materials C and F have the highest tensile strength.

5. 8

The following stock chart presents the highest, lowest and closing prices of the stock of company XYZ during 2002.

To create the chart, follow these steps: a. Select the rows with the data on price and date. b. Select Insert > Chart from the main menu. c. From the Chart Type form select Stock. d. In the Chart Option form select the Title tab. Type in the title of the chart and the name of x-axis. e. In the Chart Option form select the Legend tab. Uncheck the option Show Legend. 5. 9

The following presents the 3-D surface chart, line chart and stacked area chart.

5. 10

Looking at the charts created in 5.9, the best suited for our data is the stacked 3D area chart. In formatting the X axis there are scale limitations in the three charts. For example, the options for the minimum, maximum, major unit, minor unit do not exist for the Y axis of the charts we created. The following picture presents the charts when we plot by rows instead of plotting by column. The charts have plotted 7 different series.

5. 11

The following chart presents the temperatures for 1 week in February.

To create the chart, follow these steps: a. Select the rows with the data on price and date. b. Select Insert > Chart from the main menu.

c. From the Chart Type form select Bar. d. In the Chart Source Data form select the Series tab. Type in names for each series. e. In the Chart Option form select the Title tab. Type in the title of the chart. f. In the Chart Option form select the Data Table tab. Check the option Show Data Table. 5. 12

The following charts present the time spend in each activity during different days of the week as well as the total time spent in one week performing each activity.

To create the chart, follow these steps: g. Select the rows with the data on price and date. h. Select Insert > Chart from the main menu. i. From the Chart Type form select Pie. j. In the Chart Option form select the Title tab. Type in the title of the chart. k. In the Chart Option form select the Data Labels tab. Check the option Percentage. 5. 13

5. 14

Based on the information from the charts of exercise 5.12: a. Travel takes the largest amount of time on Friday. b. On Thursday the largest amount of time was spent on Direct Work (32% of the time). c. Direct work time was higher than indirect work time every day of the week. To create the doughnut chart follow these steps: a. Select the rows with the data. b. Select Insert > Chart from the main menu.

c. From the Chart Type form select Doughnut. d. In the Chart Source Data form select the Series tab. Type in names for each series. e. In the Chart Option form select the Title tab. Type in the title of the chart. f. In the Chart Option form select the Data Labels tab. Check the option Series Names.

5. 15

To create the bubble chart follow these steps: a. Select the rows with the data. b. Select Insert > Chart from the main menu. c. From the Chart Type form select Bubble. d. In the Chart Source Data form select the Series tab. Type in name for the series (Area). e. In the Chart Option form select the Title tab. Type in the title of the chart and the name for the X and Y axis.

5. 16

To create the column chart follow these steps:

a. b. c. d.

Select the rows with the data. Select Insert > Chart from the main menu. From the Chart Type form select Column. In the Chart Source Data form select the Series tab. Type in name for the series as Cash Flow Amount. e. In the Chart Option form select the Title tab. Type in the title for the chart ‘Cash Flow Summary’. f. In the Chart Option form select the Legend tab. Select the Left placement for the legend. g. In the Chart Option form select the Data Labels tab. Select the Value option.

5. 17

To create the area chart follow these steps: a. Select the rows with the data on months, demand units and consumption. b. Select Insert > Chart from the main menu. c. From the Chart Type form select Area. d. In the Chart Source Data form select the Series tab. Type in name for the series as Units and Energy Consumption.

e. In the Chart Option form select the Title tab. Type in the title for the chart as Demand-Based Predictions of Energy Consumption and label the Y axis as Consumption (kWh). f. In the Chart Option form select the Legend tab. Select the Top placement for the legend. g. In the Chart Option form select the Gridlines tab. In the Category (X) Axis select the Major Gridlines option. h. Double click on the area under the demand curve. The Format Data Series form appears. Select the Pattern tab. Select green to color the area under the curve. i. Double click on the area under the consumption curve. The Format Data Series form appears. Select the Pattern tab. Select red to color the area under the curve. j. Double click on the chart area. The Format Chart Area form appears. Select the Pattern tab. Select the white color for the chart area. 5. 18 a. b. c. d.

To create the area chart follow these steps: Select the whole table (the data together with the row and column headings). Select Insert > Chart from the main menu. From the Chart Type form select Line. Make sure the line you choose has nodes. In the Chart Source Data form select the Date Range tab. Check the Rows option. e. In the Chart Option form select the Title tab. Type in the title for the chart as Product Sales: Expected vs. Actual and label the Y axis as Sales ($). f. In the Chart Option form select the Legend tab. Select the Right placement for the legend. g. In the Chart Option form select the Data Labels tab. Select the Value option.

Based on the chart: a. The product with the highest Expected sales is B. b. The product with the lowest Actual sales is A. 5. 19

To create the line chart follow these steps:

a. b. c. d.

Select the data. Select Insert > Chart from the main menu. From the Chart Type form select Line. In the Chart Option form select the Title tab. Type in the title for the chart as Portfolio Value Analysis and label the Y axis as Avg. Portfolio Value.

Based on the graph: a. The portfolio value was the highest on the first quarter of 2001. b. The portfolio value was the lowest on the fourth quarter of 2000. c. The average portfolio value was quite stable during the fourth quarter of 1999 and the first two quarters of 2000. d. It seems like the value of the portfolio is going down. I would guess the value will drop to $1,900 next quarter. e. Not all the chart types work well with this data. For example, one cannot create a Surface chart as in order to do so you need two series of data. The Stock chart also does not work for this data. We think the better suited charts for this data are the line, scatter or column chart. 5. 20

In order to create the dynamic chart follow these steps: a. Create a XY Scatter chart. b. Select the range B3:B10. Go to Insert > Name > Define from the main menu. In the Define Name form type Month in the Names in the workbook window and =OFFSET ('5.20'!$B$3,0,0, COUNT('5.20'!$B:$B),1) in the Refers to window. c. Select the range C3:C10. Go to Insert > Name > Define from the main menu. In the Define Name form type Sales in the Names in the workbook window and =OFFSET ('5.20'!$C$3,0,0, COUNT('5.20'!$C:$C),1) in the Refers to window. d. Right click on the chart area. Select Source Data option. In the Source Data form select the Series tab. In the X Values window type ‘=Chapter5.xls! Month’. In the Y Values window type ‘=Chapter5.xls!Sales’.

One can see that as we type the sales for the months 9 to 11, the new sales points automatically appear on the graph.

5. 21

a. In order to build the y = ln(x) graph for 1 ≤ x ≤ 100, create a table with 2 rows. In one row enter the values of x and on the other calculate the y values using the Excel function LN(). Use XY scatter chart to build the corresponding graph.

b. In order to calculate the values of y = e^x use the Excel function EXP(). Use XY scatter chart to graph the relationship between x and y. c. In order to calculate the values of y = sin(x) use the Excel function SIN(). Use XY scatter chart to graph the relationship between x and y. d. In order to calculate the values of y = cos(x) use the Excel function COS(). Use XY scatter chart to graph the relationship between x and y.

5. 22

The following is the bubble chart for this problem.

5. 23

The following is the line chart for this problem.

a. The amount of interest paid for each payment decreases as the payment number increases. b. The principal amount paid increases as the payment number increases. This means in the beginning we pay more toward the interest and by the end we pay more toward the principal. In order to build the line chart follow these steps: a. Select the rows with the data on interest and principal payment. b. Select Insert > Chart from the main menu. c. From the Chart Type form select Line.

d. In the Chart Source Data form select the Series tab. Type in name for the series as Int. Paid and Principal Amt. e. In the Chart Option form select the Title tab. Type in the title for the chart and label the X axis as Payment Nr. f. In the Chart Option form select the Legend tab. Select the Top placement for the legend. 5. 24

In order to build the doughnut chart follow these steps: a. b. c. d.

Select the rows with the data on interest and principal payment. Select Insert > Chart from the main menu. From the Chart Type form select Doughnut. In the Chart Source Data form select the Data Range tab. Select the Series in Rows option. e. In the Chart Source Data form select the Series tab. Type in a name for each series. f. In the Chart Option form select the Title tab. Type in the title of the chart. g. In the Chart Option form select the Data Labels tab. Check the option Series Names.

Creating a doughnut chart is useful as it help understand that the part of the payment amount that goes to pay off interest decreases as we make more payments and the amount that goes to pay off the loan (the principal amount) increases.

5. 25

In order to build the radar chart follow these steps: a. Select the rows with the data. b. Select Insert > Chart from the main menu. c. From the Chart Type form select Radar.

h. In the Chart Source Data form select the Series tab. Type in a name for each series. i. In the Chart Option form select the Title tab. Type in the title of the chart.

Based on this graph we can see that the brand X covers most of the area of the graph, therefore we can say that this is the brand with the best content of vitamins C, A, E and B1.

Chapter 6 Review Exercises 6. 1

A pivot table is a table where the data, taken from a database, is re-organized to give meaningful representation. Pivot tables are used to transform large amounts of data from a table or database into an organized summary report.

6. 2

The parts of a pivot table into which the items from the original database can be placed are: row fields, column fields, data field and page field. The row field and column fields carry the headings of rows and columns of the original table. The data and page fields carry the data from the original table.

6. 3

The Grand Totals feature of a pivot table enables calculations applied to rows or columns of data in the Data Field. The Subtotals feature of a pivot table enables calculations applied to Row or Column Fields.

6. 4

In order to start creating a pivot table one should first select Data > Pivot Table from the main menu.

6.5

The process of creating a pivot table consists of the following three main steps: specify location, select data and create table layout.

6.6

In the pivot table toolbar one can choose the Refresh option to update the data shown in our pivot table if our source data is changed.

6.7

To add a calculated field or item to a pivot table, click on Pivot Table > Formulas > Calculated Field (or Calculated Item) from the toolbar drop-down options.

6.8

Four common pivot table fields available in Excel are: Row, Column, Data and Page field.

6.9

The drop-down arrow is a tool for filtering or searching for particular data. The drop-down arrow gives a list of all values in the corresponding field. Using the drop-down list one can select or deselect values to display in the pivot table.

6.10

One of the icons in the pivot table toolbar is

(Format Report), which enables

the user to change the format of the pivot table. If one clicks on the icon ,a list with different report styles appears. After the user chooses a particular style the pivot table automatically accommodates the new style. 6.11

Pivot Charts use pivot tables as their Source Data so that filtering options are transferred to the chart as well. However, one can create a chart that shows only the data you have selected in your pivot table.

6.12

One can create a Pivot Chart using the source data by highlighting the source data, choosing Data > Pivot Table and Pivot Chart Report from the menu and selecting Pivot Chart from the options in Step 1.

6.13

To group field values, select the field and choose Group and Show Detail from the Pivot Table drop-down options, or right-click on the table.

6.14

Subtotals are Field Settings applied to Row or Column Fields, not to Data Fields. However, totals for Column Fields can be calculated using Column Grand Totals, so we really need to use Subtotals for Row Fields only. To cerate Subtotals for Column Fields, in the pivot table option window we check Grand Totals for Columns.

6.15

The parameters of GETPIVOTDATA function are desired_field, range_of_desired _data, field1, item1. The desired_field is the field which contains the value you are searching for. The range_of_desired_data is the range in the pivot table which contains this field. The remaining field and item values allow you to refine your search if desired. The field and item values can be listed in any order, regardless of the pivot table layout.

Hands-On Exercises 6.1

The figure below presents the pivot tables created for this exercise: In order to create the 1st pivot table, follow these steps: a. Select the table together with the row and column titles. b. Choose Data > Pivot Table and Pivot Chart from the main menu. c. From the Pivot Table and Pivot Chart wizard window, Step 1 select the option Microsoft Excel list or database from the Where is the data you want to analyze? option list. Choose Pivot Table from the What kind of report you should create? option list.

d. In the 3rd step of the Pivot Table and Pivot Chart wizard window select the existing worksheet to put the Pivot Table Report. Specify the cell where you want to put the pivot table. Click-on the Layout command button. e. Identify ‘Dist Center Location’ and ‘Retailer Location’ as the Row Field. Identify ‘Sum of Quantity’ as the Data Field. f. Click on the Finish button of the Pivot Table and Pivot Chart wizard, Step 3 of 3 window. g. Right click on the ‘DC’ field of the pivot table created. From the option list that appears, select ‘Field Settings’. In the ‘PivotTable Field’ window that appears select ‘Max’ from the SubTotals window. In order to create the 2nd pivot table, follow steps a to f described above. Then, from PivotTable toolbar drop-down list select: PivotTable > Formulas > Calculated Fields. In the ‘Insert Calculated Field’ window type the name of the new field on the ‘Name’ window, and type ‘=Price*Quantity’ in the ‘Formula’ window. Update the pivot table so that the Row Field would still be ‘Dist Center Location’ and ‘Retailer Location’, but the Data Field would now be the new calculated field. In order to create the 3rd pivot table, follow steps a to f described above. Note that for this table however, the row fields are ‘Dist Center Location’ and ‘Product Name’, and the Data Field is ‘Price’.

6.2

The following figure presents the pivot tables created for this exercise:

6.3

In order to create the pivot tables for this exercise follow these steps: a. Select the table together with the row and column titles. b. Choose Data > Pivot Table and Pivot Chart from the main menu. c. From the Pivot Table and Pivot Chart wizard window, Step 1 select the option Microsoft Excel list or database from the Where is the data you want to analyze? option list. Choose Pivot Table from the What kind of report you should create? option list. d. In the 3rd step of the Pivot Table and Pivot Chart wizard window select the existing worksheet to put the Pivot Table Report. Specify the cell where you want to put the pivot table. Click-on the Layout command button.

e. Identify ‘Start City’ as the Row Field. Identify ‘Destination City’ as the Column Field. Identify ‘Min of Travel Time’ as the Data Field for the first table and ‘Min of Cost’ as the Data Field for the second pivot table. f. Click on the Finish button of the Pivot Table and Pivot Chart wizard, Step 3 of 3 window.

6.4

In order to create the following pivot table follow these steps: a. Select the table together with the row and column titles. b. Choose Data > Pivot Table and Pivot Chart from the main menu. c. From the Pivot Table and Pivot Chart wizard window, Step 1 select the option Microsoft Excel list or database from the Where is the data you want to analyze? option list. Choose Pivot Table from the What kind of report you should create? option list. d. In the 3rd step of the Pivot Table and Pivot Chart wizard window select the existing worksheet to put the Pivot Table Report. Specify the cell where you want to put the pivot table. Click-on the Layout command button. e. Identify ‘Drink Type’ and ‘Fluid Ounces’ as the Row Field. Identify ‘Material’ as the Column Field. Identify ‘Average of Cost’ as the Data Field. f. Click on the Finish button of the Pivot Table and Pivot Chart wizard, Step 3 of 3 window.

6.5

The following is the pivot chart created for this exercise:

The most expensive products to bottle are the 22 ounce bottles. Glass bottles are more expensive. 6.6

The following is the pivot table for this problem. In order to create the pivot table follow these steps:

a. Calculate the cost of shipping 10 ounce packages (=‘First Ounce Rate’ + ‘9*Rate After First’). Create a new column in the existing table titled ’10 Ounce Package’. b. Select the table together with the row and column titles. c. Choose Data > Pivot Table and Pivot Chart from the main menu. d. From the Pivot Table and Pivot Chart wizard window, Step 1 select the option Microsoft Excel list or database from the Where is the data you want to analyze? option list and choose Pivot Table from the What kind of report you should create? option list. e. In the 3rd step of the Pivot Table and Pivot Chart wizard window select the existing worksheet to put the Pivot Table Report. Click-on the Layout command button. f. Identify Shipment as the Row Field. Identify the column with the price of the 10 ounce packages as the Data Field. Click on the command button OK. Click on the Finish button of the 3rd step. g. Double-click on the ‘Sum 10 Ounce package’ title of the pivot table. The PivotTable Field window appears. Select Min in the Summarize by list box. h. In the Option window of the pivot table, make sure that the option button ‘Grand Totals for Column’ is checked. i. From the Pivot Table toolbar choose Format Report option. Select one of the report styles presented.

6.7

The following are the pivot tables created for this exercise:

6.8

Based on the information from the pivot tables of exercise 6.7, we see that the busiest hour of the day is hour 3. The following chart presents the queue length during this hour in a week.

6.9

In order to create the following pivot table follow these steps: a. Select the table together with the row and column titles. b. Choose Data > Pivot Table and Pivot Chart from the main menu. c. From the Pivot Table and Pivot Chart wizard window, Step 1 select the option Microsoft Excel list or database from the Where is the data you want to analyze? option list. Choose Pivot Table from the What kind of report you should create? option list.

d. In the 3rd step of the Pivot Table and Pivot Chart wizard window select the existing worksheet to put the Pivot Table Report. Specify the cell where you want to put the pivot table. Click-on the Layout command button. e. Identify ‘Region’ as the Row Field. Identify ‘Category’ as the Column Field. Identify ‘Sum of Demand’ as the Data Field. f. Click on the Finish button of the Pivot Table and Pivot Chart wizard, Step 3 of 3 window

6.10

The following table presents the pivot tables build for this exercise:

6.11

In order to create the following pivot table follow these steps: a. Select the table together with the row and column titles. b. Choose Data > Pivot Table and Pivot Chart from the main menu. c. From the Pivot Table and Pivot Chart wizard window, Step 1 select the option Microsoft Excel list or database from the Where is the data you want to analyze? option list and choose Pivot Table from the What kind of report you should create? option list.

d. In the 3rd step of the Pivot Table and Pivot Chart wizard window select the existing worksheet to put the Pivot Table Report. Specify the cell where you want to put the pivot table. Click-on the Layout command button. e. Identify ‘Income’ as the Row Field. Identify ‘Gender’ as the Column Field. Identify ‘Days per Week’ as the Data Field. Click on the command button OK. f. Click-on the Options command button in the Pivot Table and Pivot Chart wizard, Step 3 of 3 window. g. In the PivotTable Options window, make sure that the option button ‘Grand Totals for Columns’ and ‘Grand Totals for Rows’ are checked. h. Click on the Finish button of the Pivot Table and Pivot Chart wizard, Step 3 of 3 window.

The pivot table shows that man buy Sally’s lemonades more than women do. We do not see a clear relation between the income and frequency with which a customer buys her lemonade. 6.12

In order to create the following pivot table follow these steps: a. Select the table together with the row and column titles. b. Choose Data > Pivot Table and Pivot Chart from the main menu. c. From the Pivot Table and Pivot Chart wizard window, Step 1 select the option Microsoft Excel list or database from the Where is the data you want to analyze? option list. Choose Pivot Table from the What kind of report you should create? option list. d. In the 3rd step of the Pivot Table and Pivot Chart wizard window select the existing worksheet to put the Pivot Table Report. Specify the cell where you want to put the pivot table. Click-on the Layout command button. e. Identify ‘Gender’ as the Row Field. Identify ‘Major Desired’ as the Column Field. Identify ‘Count Major Desired’ as the Data Field. f. Click on the Finish button of the Pivot Table and Pivot Chart wizard, Step 3 of 3 window.

6.13

In order to create the following pivot table follow these steps: a. Fill the empty fields of the ‘Salesperson’ column. This will require typing the name of the salesperson for each transaction. b. Select the table of data together with the row and column titles. c. Choose Data > Pivot Table and Pivot Chart from the main menu. d. From the Pivot Table and Pivot Chart wizard window, Step 1 select the option Microsoft Excel list or database from the Where is the data you want to analyze? option list. Choose Pivot Table from the What kind of report you should create? option list. e. In the 3rd step of the Pivot Table and Pivot Chart wizard window select the existing worksheet to put the Pivot Table Report. Specify the cell where you want to put the pivot table. Click-on the Layout command button. f. Identify ‘Salesperson’ as the Row Field. Identify ‘Count Date of Sale’ as the Data Field. Do not specify the Column Field. g. Click on the Finish button of the Pivot Table and Pivot Chart wizard, Step 3 of 3 window.

6.14

In order to create the following pivot table follow these steps: a. Fill the empty fields of the ‘Salesperson’ column. This will require typing the name of the salesperson for each transaction. b. Select the table of data together with the row and column titles. c. Choose Data > Pivot Table and Pivot Chart from the main menu. d. From the Pivot Table and Pivot Chart wizard window, Step 1 select the option Microsoft Excel list or database from the Where is the data you want to analyze? option list. Choose Pivot Table from the What kind of report you should create? option list. e. In the 3rd step of the Pivot Table and Pivot Chart wizard window select the existing worksheet to put the Pivot Table Report. Specify the cell where you want to put the pivot table. Click-on the Layout command button.

f. Identify ‘Salesperson’ as the Row Field. Identify ‘Product Sold’ as the Column Field. Identify ‘Sum Transaction Revenue’ as the Data Field. g. Click on the Finish button of the Pivot Table and Pivot Chart wizard, Step 3 of 3 window.

6.15

In order to create the following pivot table follow these steps: a. Fill the empty fields of the ‘Salesperson’ column. This will require typing the name of the corresponding salesperson for each transaction. b. Select the table of data together with the row and column titles. c. Choose Data > Pivot Table and Pivot Chart from the main menu. d. From the Pivot Table and Pivot Chart wizard window, Step 1 select the option Microsoft Excel list or database from the Where is the data you want to analyze? option list. Choose Pivot Table from the What kind of report you should create? option list. e. In the 3rd step of the Pivot Table and Pivot Chart wizard window select the existing worksheet to put the Pivot Table Report. Specify the cell where you want to put the pivot table. Click-on the Layout command button. f. Identify ‘Salesperson’ as the Row Field. Identify ‘Date of Sale’ as the Column Field. Identify ‘Sum Transaction Revenue’ as the Data Field. g. Click on the Finish button of the Pivot Table and Pivot Chart wizard, Step 3 of 3 window. h. Right click on cell H3. From the option list that appears, select: Group and Show Details > Group. Grouping window appears. In this window set the: Group By option to 7, Start at option to 1 and End at option to 31.

6.16

The following figure presents the pivot tables build for this exercise:

6.17

The following is the pivot table created for this exercise:

6.18

The following presents the pivot table build for this exercise:

6.19

The following is the pivot table build for this exercise:

6.20

The following is the pivot table created for this exercise:

Chapter 7 Review Exercises

7.1

The function used to calculate the mean of a data set in Excel is AVERAGE(range or range_name).

7.2

The mean of a range of numbers is the average, the median is the middle number in a list of sorted data and mode is the most frequently occurring value. If the data values are fairly symmetric the mean and median of the data are the same.

7.3

Some useful tools from Analysis Toolpack are: Histogram, Moving Average, ANOVA, Random Number Generation, etc.

7.4

The Descriptive Statistics tools provide a list of statistical information about your data set including the mean, median, standard deviation, and variance.

7.5

Standard Deviation is the square root of the Sample Variance.

7.6

Any values in the data set that lie more than ±2standard deviations from the mean are called outliers. We can identify outliers by looking at a chart of the data, or we can actually find values in the data set which are greater than +2standard deviations and smaller than -2standard deviations using the following Excel function: ‘=IF(ABS(data_value – mean_value)>2*s, “outlier”,”” )’

7.7

The function ‘=IF(OR(data_value – mean_value>2*s, data_value – mean_ value < -2*s), “outlier”,”” )’ can be used to identify an outlier.

7.8

Skewness is a measure of how symmetric or asymmetric the data set is. A Skewness value between -1 and +1 implies symmetry. We would say that a good value of Skewness for a symmetric data set is 0.

7.9

The PERCENTILE function returns a value for which a desired percentile k of the specified data set falls below. The PERCENTRANK function performs the complimentary task; it returns the percentile of the data set which falls below a given value.

7.10

Bins are the intervals into which values can fall; they can be defined by the user or can be evenly distributed among the data by Excel.

7.11

A Pareto organization of a chart sorts the columns from the largest to the smallest.

7.12

The four basic shapes to a histogram are: symmetric, positively skewed, negatively skewed, and multiple peaks.

7.13

A negatively skewed histogram has a peak on the right and many lower points (stretching) on the left.

7.14

In the function y = a*x + b, x is the independent variable and y is the dependent variable.

7.15

A trendline can be fitted to any chart created in Excel.

7.16

There are five basic trend lines that Excel can model: Linear, Exponential, Power, Moving Average and Logarithmic.

7.17

The two values that measure the accuracy of a linear trendline are: R-square value and the standard error.

7.18

The parameters of a Binomial distribution function are: the number of trials and the probability of a success.

7.19

Weibull distribution has two parameters: alpha and beta. The Exponential distribution has a single parameter, lambda. If alpha in Weibull distribution is equal to 1, then the distribution becomes equivalent to the Exponential distribution with lambda equal to 1/beta.

7.20

In order to convert a Normal x value to a Standard Normal x value, we use the following Excel function: ‘=STANDARDIZE(x, mean, std_dev)’.

Hands-on Exercises

7.1

In order to find the typical salary we calculate the mean (using the function ‘=AVERAGE (data_set)’)) and median (using the function ‘=MEDIAN (data_set)’) ) of the data set. We find that mean = $ 25,279.45 and median = $26,152.50. Since the values found for the mean and median are close to each-other, we can say that the data set does not have outliers. We can finally say that a typical salary for the geography graduates is $ 25,279.45.

7.2

The following are the results for this exercise:

In order to display the results as percentages, we selected the range E2:E11. Choose Format > Cell from the main menu. In the Format Cell window select the Number tab. In the Category list box choose the Percentage option. In order to calculate the fraction Nonconforming and mean and standard deviation, we used the following formulas:

7.3

a.

The following are the statistics calculated for this data set:

Based on these results we can say that the average voltage held by the units increased after shipment. The standard deviation also increased slightly, what means that there is more variation of the voltage held by each unit from the mean. Comparing the mean with the median we see that prior shipment, less than 50% of the units held more voltage than the average, and after the shipment more than 50% of the units held more voltage than the average. b.

80% of the units are acceptable before and after shipment. The following are the formulas used.

c. d. e.

One way to improve the quality of Electro’s regulators is to remove the outliers (the items that hold voltage above 75 or below 25). 10% of NY regulators have a voltage exceeding 24.6. To find that used the following Excel function: ‘=PERCENTILE(B3:B12,0.1)’. 5% of NY regulators have a voltage less than or equal to 22.8. To find that use the following Excel function: ‘=PERCENTILE(B3:B12,0.05)’.

7.4

The following are the three histograms we created for this problem. The mean return for stock is 5.58%, T-bills is 3.42% and T-bonds is 5.37%.

7.5

The histograms created in the hands-on exercise 7.5 are positively skewed.

7.6

a. The slope of the least squares line of Dell’s stock is -0.000206 and S & P’s stock is -2.973E-05. b. The R-Squared value of the Dell stock is 0.0557 and S and P stock is 0.0183. c. Looking at the slopes of the two trend lines, seems that the return of Dell’s stock and S & P’s stocks are decreasing; however the rate of decrease is higher for Dell than S&P. Therefore, we would choose S & P as the better alternative.

7.7

The following graph plots the price versus the square footage of a house. We fitted a linear trend line to the data. The R2 for this trend line is 0.95. That means, the trend line found gives a good approximation of data. a. Based on the equation given for the trendline, we can estimate the addition in price if we plan to increase the square footage of the house by 400. The addition in price is: = 104.39*400 = $41,756. b. 95% of the variations in the home value are explained by variations in the house size. c. A typical price for a 2500 square foot house is $265,920.5 (calculated using the trend line equation). Therefore, the price $470,000 is not typical.

7.8

The following two graphs plot home prices versus the number of bathrooms and the number of bedrooms. For each relation we plot a trend line. The R-square for the trend line that presents the relationship between price and square footage (see hands-on exercise 7.8) is 0.95. The R-square for the trend line that presents the relationship between price and number of bathrooms is 0.67. The R-square for the trend line that presents the relationship between price and number of bedrooms is 0.46. These results suggest that the square footage has the highest impact on home prices.

7.9 a.

b. c.

The following chart presents the trendlines for the annual revenues of Staples, Wal-Mart and INTEL. The highest R-square is 0.9927. This belongs to Staples. Therefore, we can say that Staple’s revenues best fit an Exponential trend curve. The annual percentage growth rate varies by company. For Staples, it is 25.51%, for Wal-Mart it is 16.53% and for INTEL is 11.21%. The revenues for 2003 are expected to be: for Staples = $16,235; for WalMart = $310,522; for INTEL = $32,725.

7.10 a. b. c.

There is a negative relationship between price and demand. As price increases, demand decreases and as price decreases demand increases. This is the reason why the slope of the trend line is negative. The demand for a price of $69 can be calculated as follows: demand = 5.2657*(69) + 598.1 = 234.77 1% increase in price reduces demand by 5.2657%.

7.11

In order to answer the questions in this exercise, one should first identify a trend line for the data. Below we present the results from fitting two different trend lines to the data. The R-square is higher when the fitted line is logarithmic. a.

If 75,000 phone calls will be made next month, the expected number of subscriptions is: = 26472*Ln(75,000) = 66,289.

b.

If we want to sell 80,000 subscriptions, we have to make 6975.1* EXP(4E05* 80,000) = 171,117 phone calls.

7.12 a. b.

c.

The dependent variable is annual salary and the independent variable is years worked. In the following graph we plot a linear and a logarithmic trend line in the data.

Both graphs show that there is a positive relationship between the years worked and annual salary. Based on the equation of the linear trend line, one can say that the annual expected increase in salary is about $1,016. The linear trend line fits the data well as the R-square is 0.9453. However, the logarithmic line fits the data better as the corresponding R-square is 0.9622.

7.13 a. b.

c.

Town population is the independent variable and average household income is the dependent variable. The following graphs plot the relationship between the town population and the average household income. We have fitted two different trend lines to the data, a linear and a logarithmic trend line. There exists a positive relationship between the town population and the average household income. Based on the equation of the linear trend line, one can say that for every additional 1000 people, there is an increase of about $1,954 on the average household income of the town.

The linear trend line fits the data well as the R-square is 0.8724. However, the logarithmic trend line fits the data better as the corresponding R-square is higher.

7.14

We graph the relationship between the salary and age.

The R-square we get is 0.7508. That means 75% of the changes in salary are explained by work experience. 7.15 a. b. c.

The power trendline fits the data better as the corresponding R-square is the highest. The R-square for the linear trendline is 0.929, for the exponential 0.9185 and for the power trendline 0.9372. If production is 800 units, the costs are expected to be: 429.79*(800)^0.7148 = $51,096.

7.16 a. In order to identify the best curve that fits our data, we experimented with different trend lines. The following are the R-squares for each trend lines: linear – 0.906; exponential – 0.9425; logarithmic – 0.9765; power – 0.9563;

polynomial – 0.959. Based on the values of R-square, we can say that Logarithmic trendline fits our data best.

b. Assuming that our data follows a learning curve, the next batch is expected to be produced in: -16.462*Ln(21) + 125 = 75 units of time. 7.17 a. b.

There is 1% chance that the car sales next year will be 17,431. In order to find the number of cars expected to be sold, use the following Excel function: ‘=NORMINV(0.01, 50000, 14000)’. The probability that the company will sell less than or equal to 2.7 million cars during the next year is 1. In order to find this probability, we used the following Excel function: ‘=NORMDIST(2700000,50000,14000,TRUE)’.

7.18

6.68% of American males weight more than 225lbs. In order to find that use the following Excel function: ‘=1-NORMDIST(225,180,30,TRUE)’.

7.19

10.56% of the people make more than $60,000. In order to find that use the following Excel function: ‘=1-NORMDIST(60000,45000,12000,TRUE)’. The corresponding value to the $60,000, in case we standardize the Normal Distribution with mean 45,000 and standard deviation 12,000, is: 1.25. In order to find that use the following Excel function: ‘= STANDARDIZE(60000,45000,12000)’. The percentage of people that make more than $60,000 (when the standard normal distribution is used instead) is: 10.56%. In order to find that use the following Excel function: ‘=1-NORMSDIST(1.25)’. As expected, the same answer is found in both cases.

7.20

The probability that the sales will be over 600 in January is: 0.3425. To calculate that use the following Excel function: ‘=1-EXPONDIST(600,1/560,TRUE)’.

7.21

The probability of observing exactly 15 accidents at the plant is: 0.1024. To calculate that, use the following Excel function: ‘= POISSON(15,15,FALSE)’. The probability of observing less than 15 accidents at the plant is: 0.4657. To calculate that, use the following Excel function: ‘=POISSON(14,15,TRUE)’. We can be 99% sure that less than 25 accidents will occur in a year. In order to find the number of accidents one could use the inverse Poisson distribution function. However, there is no such a function in excel. Therefore, we used Goal Seek to find the number of accidents. We set the Excel function (POISSON) in the following way:

Then select Tools > Goal Seek from the main menu. The Goal Seek window appears. Fill in the information in this window as follows:

Click the OK command button. 7.22

In this problem the probability of a show up is 0.95. The plane holds 200 passengers. We need to find how many tickets to sell, so that the chances for overbooking are less than or equal to 5%, in other words one should find x that satisfies the following: P(x > 200) = 0.05 or P(x ≤ 200) = 0.95. In order to find the number of tickets, such that at most 5% of the time the company will overbook, one should use the inverse Binomial distribution function. However, there is no such a function in excel. Therefore, we used Goal Seek to find the number of accidents. We set the Excel function (BINOMDIST) the following way:

Then select Tools > Goal Seek from the main menu. The Goal Seek window appears. Fill in the information in this window as follows:

We find that 206 tickets should be sold. 7.23 a. We need to find P(5*x ≥ 60), or, P(x ≥ 12). Random variable x has a binomial distribution with probability of success equal to 0.5. This is equal to: 1- P(x ≤ 11). We find this using the following Excel function: ‘=1-BINOMDIST(11,20,0.5,TRUE)’ The result is 0.2517. b. Let consider the case that for each incorrect answer the student will loose 5 points. We assume that the student gives an answer to each question. Let x be the number of correct answers, than 20 – x will be the number of incorrect answers. We want to find P(5*x - 5*(20 - x) ≥ 60) = P(x ≥ 16) = 1 - P(x≤15). We find this using the following Excel function: ‘=1-BINOMDIST(15,20,0.5,TRUE)’ The result is 0.0059. 7.24

The probability that we will wait more than 1 minute before seeing the next arrival is 0.2636. To find that we used the following Excel function: ‘=1-EXPONDIST(60,1/45,TRUE)’ The probability that we have to wait for at least 2 minutes before seeing the next arrival is 0.071045. To find that we used the following Excel function: ‘=1-EXPONDIST(60,1/45,TRUE)’

7.25 a. The following graph plots a trendline to the data. The trendline that best fits our data is polynomial as it gives the highest R-square.

b. The R-square found for the linear trendline is 0.9261; for the power 0.8824; for the logarithmic 0.8588; for the exponential 0.911; for the polynomial 0.9353. c. The expected sales for week 13 are: -0.1831*13^2 + 7.9988*13 + 58.341 = 131 and the expected sales for week 14 are: -0.1831*14^2 + 7.9988*14 + 58.341=134. 7.26 a. The probability that a part is longer than 80.5 millimeters or shorter than 80 millimeters is 0.225. To find this probability use the following Excel function: ‘=(1NORMDIST(80.5,80.3,0.2,TRUE))+NORMDIST(80,80.3,0.2,TRUE)’. b. 99% of the time, the length of the case will be up to 80.77 millimeters. To find the length of the case, use the following Excel function: =NORMINV(0.99,80.3,0.2). 7.27 c. d.

e.

f.

The probability that the bottle weights more than 13 ounces is 0.02275. In order to find this probability use the following Excel function: ‘=1-NORMDIST(13,12,0.5,TRUE)’. The probability that the weight is no more than 13 ounces and no less than 11 ounces is 0.9545. In order to find this probability use the following Excel function: ‘=NORMDIST(13,12,0.5,TRUE) -NORMDIST(11,12,0.5,TRUE)’. The standard deviation of weight should be 0.33 for the company to state that 99.9% of the bottles weight less than 13 ounces. In order to find the standard deviation one should calculate ‘=NORMDIST(13,12, sigma, TRUE)’ for different values of sigma, until the value of this function is equal to 99.9%. The mean of weight should be 11.3 for the company to state that 99.9% of the bottles weight less than 13 ounces. In order to find the mean one should calculate ‘=NORMDIST(13,mean,0.5,TRUE)’ for different values of the mean, until the value of this function is equal to 99.9%.

7.28 a.

The probability that a page is viewed for more than 10 seconds is 0.357. To find this probability, use this formula: ‘=1-LOGNORMDIST(10,0.5,1)’.

b. c.

7.29 a. b.

c.

50% of the users view a page for 1.65 minutes. To find the number of users, use this formula: ‘=LOGINV(0.5,0.5,1)’ The following are the density functions for the Lognormal distribution with θ = 0.5 and 1 and ω2 = 1.

The probability that a semiconductor laser lasts at least 600 hours is 0.48. To find this probability use the following Excel function: ‘=1- WEIBULL(600,2,700,TRUE)’. The probability that a semiconductor laser lasts at most 400 hours is 0.279. TO find this probability use the following Excel function: ‘=WEIBULL(400,2,700,TRUE)’. The following is the density functions for the Weibull distribution with parameters α =2 and β = 700 hours.

Chapter 8 Review Exercises 8.1

The three components of a mathematical model are: objective function, constraints and decision variables.

8.2

The difference between integer programming and linear programming is that in an integer programming problem the decision variables take integer values.

8.3

In a nonlinear programming problem we find in the objective function or constraints variables of multiple powers such as x2, x3 or terms involving two variables such as x1x2.

8.4

The three main steps involved in using the Solver are: (a) reading and interpreting the problem; (b) preparing the spreadsheet; and (c) solving the model and reviewing the results.

8.5

The constraint equations are entered separately using formulas. The constraints are given in terms of the decision variables, therefore the formulas should be given in terms of the decision variable cells. For each constraint, the inequality signs and the right-hand sides should be in individual cells as well.

8.6

The Target Cell refers to the location of the formula for the objective function.

8.7

To insure that negative quantities are not produced in a Solver solution, one can follow these steps: choose Tools > Solver from the main menu. In the Solver Parameters window click on the click-on the Options command button. In the Solver Options window select the option Assume Non-negativity.

8.8

To change a model from Linear programming to an Integer programming, one should specify that the decision variables are integer. To do this, one should add a constraint in the Solver window. For this new constraint, highlight the range of the decision variables as the constraint formula and choose “int” from the list of inequalities. The RHS value will then automatically become “integer”.

8.9

To enforce binary decision variables one should add a constraint in the Solver window. For this new constraint, highlight the range of the decision variables as the constraint formula and choose “bin” from the list of inequalities. The RHS value will then automatically become “binary”.

8.10 The following is a linear programming problem that can be solved using the Solver: Min: 2x + 3y Subject to: x + y < 10 2x + y < 15 x, y integers

(objective function) (constraint 1) (constraint 2) (decision variables)

8.11 The Premium Solver is the latest version of Excel Solver. As such, it can handle larger problems (problems with a larger number of decision variables and constraints). It takes longer to solve an IP with the Standard Solver. The Standard Solver is less efficient in solving NLP-s. 8.12 The three Solver versions offered in the Premium Solver are: Standard GRG Nonlinear; Standard Simplex LP, and Standard Evolutionary. 8.13 Some of the options offered by the Standard Solver are similar to the ones offered by the Premium Solver, such as Max Time, Iteration, Precision, and Assume NonNegativity. However, the Premium Solver has additional options such as: Population Size, Mutation Rate that are parameters associated with the Genetic Algorithm. 8.14 The Standard Solver is limited by a slope-based algorithm for solving NLP problems. This algorithm works well for convex maximization or concave minimization problems, however when this is not the structure of the problem, there can be multiple optima which the Standard Solver will not recognize. Such examples would be problems with fixed charges or absolute values. Therefore, the Genetic Algorithm of the Premium Solver is much better for solving NLP problems. Standard solver can solve NLP-s with as many as 200 variables and 100 constraints. Therefore, for larger problems one should use the Premium solver. 8.15 The two main parameters of the Genetic Algorithm are the Population Size and Mutation Rate. Hands-on Exercises: 8.1

The following is the solution we get from the Excel Solver:

In order to solve the problem, set the Excel spreadsheet in the following way:

Check the following option of the Excel Solver: Non-negativity assumed. Set the solver the following way:

8.2

We define the following decision variables for this problem: X1 X2 X3

presents the number of clothing boxes shipped presents the number of jewelry boxes shipped presents the number of perfume boxes shipped

X4

presents the number of shoe boxes shipped

The objective is to minimize total shipping costs: Min: 500*(400 – X1) + 300*(400 – X2) + 275*(400 – X3) + 250*(400 – X4) -55*X1 - 70*X2 – 65*X3 – 50*X4 The constraints are: The total volume shipped should not be more than the volume of the truck. 5*X1 + 2*X2 + 2*X3 + 6*X4 ≤ 4*1,000 ft2 The total weight shipped should not be more than the weight limit of the trucks. 75*X1 + 20*X2 + 20*X3 + 45*X4 ≤ 4*50,000 lbs The number of boxes shipped is integer. We assume that we cannot ship parts of a box. X1, X2, X3, and X4 are integers The number of boxes shipped should be no more than 400 or less than 0. 0 ≤ X1, X2, X3, X4 ≤ 400 The following is the solution from Excel solver:

Set the Excel spreadsheet the following way:

Check the following option of the Excel Solver: Non-negativity assumed. Set the solver in the following way:

8.3

We define the following decision variables for this problem: X11, X12, X13, X14 1, 2, 3, and 4. X21, X22, X23, X24 1, 2, 3, and 4. X31, X32, X33, X34 1, 2, 3, and 4. X41, X42, X43, X44 2, 3, and 4.

present the number of clothing boxes shipped using truck presents the number of jewelry boxes shipped using truck presents the number of perfume boxes shipped using truck presents the number of shoe boxes shipped using truck 1,

The objective is to minimize total shipping costs: Min: 500*[(400 – X11 – X12 – X13 – X14)] + 300*[(400 – X21 – X22 – X23 – X24)] + 275*[(400 – X31 – X32 – X33 – X34)] + 250*[(400 – X41 – X42 – X43 – X44)] -55*( X11 + X12 + X13 + X14) - 70*(X21 + X22 + X23 + X24) – 65*( X31 + X32 + X33 + X34) – 50*( X41 + X42 + X43 + X44) The constraints are: The total volume shipped should not be more than the volume of each truck. 5*X11 + 2*X21 + 2*X31 + 6*X41 ≤ 1,000 ft2 5*X12 + 2*X22 + 2*X32 + 6*X42 ≤ 1,000 ft2 5*X13 + 2*X23 + 2*X33 + 6*X43 ≤ 1,000 ft2 5*X14 + 2*X24 + 2*X34 + 6*X44 ≤ 1,000 ft2 The total weight shipped should not be more than the weight limit of the trucks. 75*X11 + 20*X21 + 20*X31 + 45*X41 ≤ 4*50,000 lbs 75*X12 + 20*X22 + 20*X32 + 45*X42 ≤ 4*50,000 lbs 75*X13 + 20*X23 + 20*X32 + 45*X43 ≤ 4*50,000 lbs 75*X14 + 20*X24 + 20*X34 + 45*X44 ≤ 4*50,000 lbs

The number of boxes shipped is integer. We assume that we cannot ship parts of a box. X11, …, X44 are integers The number of boxes shipped should be no more than 400 or less than 0. 0 ≤ X11,…, X44 ≤ 400 The following is the solution from Excel solver:

In the Excel spreadsheet, we set the objective function and constraints the following way: Objective function =F2*(400-SUM(B9:E9)) +F3*(400-SUM(B10:E10)) + F4*(400-SUM(B11:E11)) + F5*(400-SUM(B12:E12)) -E2*SUM(B9:E9)-E3*SUM(B10:E10)E4*SUM(B11:E11)-E5*SUM(B12:E12) Constraints

Check the following option of the Excel Solver: Non-negativity assumed. Set the solver in the following way:

8.4

Define the following decision variables for this problem: X1 X2 X3 X4

total number of cars sold total number of trucks sold total number of trains sold total number of airplanes sold

The objective is to maximize profits: Max: (5.99 – 2.15 – 0.08*200 – 0.17*7.25)*X1 + (7.99 – 3.45 – 0.065*200 – 0.25*7.25)*X2 + (10.95 – 4.65 – 0.1*200 – 0.19*7.25)*X3 + (8.49 – 3.50 – 0.075*250 – 0.23*7.25)*X4 Constraints: 2.15*X1 + 3.45*X2 + 4.65*X3 + 3.50*X4 ≤ $10,000 – budget constraint 0.17* X1 + 0.25*X2 + 0.19*X3 + 0.23*X4 ≤ $1,000 – time constraint X1, X2, X3 ≥ 500, X4 ≥ 250 X1, X2, X3, X4 integers The following is the solution from Excel solver:

In the Excel spreadsheet, set the objective function and constraints the following way:

Check the following option of the Excel Solver: Non-negativity assumed. Set the solver in the following way:

8.5

The decision variables for this problem are: X percentage of ingredient A included in the mix Y percentage of ingredient B included in the mix Z percentage of ingredient C included in the mix The objective is to minimize the unit costs: 0.75*X + 0.63*Y + 0.20*Z The constraints are: X + Y + Z = 1 (A, B and C are the only ingredients to be used) 830*X + 600*Y + 750*Z ≤ 750 (calorie limits) 4*X + 6*Y + 3*Z ≤ 10 (fat limits) 0.35*X + 0.25*Y + 0.04*Z ≥ 0.25 (RDA for Vitamin A) 0.5*X + 0.41*Y + 0.15*Z ≥ 0.25 (RDA for Vitamin D) 0.35*X + 0.21*Y + 0.22*Z ≥ 0.25 (RDA for Protein) X, Y, Z ≥ 0. The following is the solution from Excel solver:

In the Excel spreadsheet, set the objective function and constraints the following way:

Check the following option of the Excel Solver: Non-negativity assumed. Set the solver in the following way:

8.6

a. The following figure traces the dependents of the decision variables.

To trace the dependents of the decision variables, follow these steps: - Select View > Toolbars > Formula Auditing from the main menu. Select cell B8 and click on the icon click on the icon on the icon

at the Formula Auditing toolbar. Select cell B9 and

at the Formula Auditing toolbar. Select cell B10 and click at the Formula Auditing toolbar.

b. The following figure traces the precedents of the target cell and constraints:

To trace the precedents of the target cell and constraints, follow these steps: - Select View > Toolbars > Formula Auditing from the main menu. Select cell F7 (the target cell) and click on the icon

at the Formula Auditing toolbar.

Select cells E8,…,E14 (one at a time) and click on the icon Auditing toolbar. 8.7

The decision variables for this problem are: X1 number of batches of nuts produced at workstation 1 X2 number of batches of nuts produced at workstation 2 X3 number of batches of nuts produced at workstation 3 X4 number of batches of nuts produced at workstation 4 Y1 Y2 Y3 Y4

number of batches of bolts produced at workstation 1 number of batches of bolts produced at workstation 2 number of batches of bolts produced at workstation 3 number of batches of bolts produced at workstation 4

at the Formula

The objective is to minimize costs: Min: 0.62*X1 + 0.61*X2 + 0.66*X3 + 0.61*X4 + 0.82*Y1 + 0.80*Y2 + 0.79*Y3 + 0.77*Y4 The constraints are: 2*X1 + 3*Y1 ≤ 16*5*60 time constraint at workstation 1 8*X2 + 9*Y2 ≤ 16*5*60 time constraint at workstation 2 7*X3 + 7*Y3 ≤ 16*5*60 time constraint at workstation 3 8*X4 + 5*Y4 ≤ 16*5*60 time constraint at workstation 4 X1 + X2 + X3 + X4 ≥ 700 nut production requirements Y1 + Y2 + Y3 + Y4 ≥ 1000 bolt production requirements X1,…,X4, Y1,…,Y4 ≥ 0 The following is the solution from Excel solver:

In the Excel spreadsheet, set the objective function and constraints the following way:

Check the following option of the Excel Solver: Non-negativity assumed. Set the solver in the following way:

8.8

The decision variables are: X number of nickels Y number of dimes Z number of pennies W number of quarters The objective is to minimize the total number of coins: X + Y + Z + W The constraints are: 5*X + 10*Y + z + 25*W = 97 Y - 3*X = 0 Z≥5 W≤2 X, Y, W ≥ 0 and integers The following is the solution from Excel solver:

In the Excel spreadsheet, set the objective function and constraints the following way:

Check the following option of the Excel Solver: Non-negativity assumed. Set the solver in the following way:

8.9

The decision variables are: X takes value 1 if project A is accepted and 0 otherwise Y takes value 1 if project B is accepted and 0 otherwise Z takes value 1 if project C is accepted and 0 otherwise Objective is to maximize investor’s total return: Max: 550,000*X + 750,000*Y + 675,000*Z The constraints are: 175*X + 210*Y + 195*Z ≤ 400 85*X + 105*Y + 90*Z ≤ 200 72*X + 89*Y + 73*Z ≤ 200

budget constraint for the first year budget constraint for the second year budget constraint for the third year

X, Y, Z binary The following is the solution from Excel solver. The results are presented in thousands of dollars.

In the Excel spreadsheet, set the objective function and constraints the following way:

Check the following option of the Excel Solver: Non-negativity assumed. Set the solver in the following way:

8.10

The decision variables are: X1 takes value 1 if the student studies calculus for 0-5 hours, and 0 otherwise X2 takes value 1 if the student studies calculus for 6-10 hours, and 0 otherwise X3 takes value 1 if the student studies calculus for 11-15 hours, and 0 otherwise

Y1 Y2 Y3

takes value 1 if the student studies chemistry for 0-5 hours, and 0 otherwise takes value 1 if the student studies chemistry for 6-10 hours, and 0 otherwise takes value 1 if the student studies chemistry for 11-15 hours, and 0 otherwise

Z1 takes value 1 if the student studies physics for 0-5 hours, and 0 otherwise Z2 takes value 1 if the student studies physics for 6-10 hours, and 0 otherwise Z3 takes value 1 if the student studies physics for 11-15 hours, and 0 otherwise W1 W2 W3

takes value 1 if the student studies economics for 0-5 hours, and 0 otherwise takes value 1 if the student studies economics for 6-10 hours, and 0 otherwise takes value 1 if the student studies economics for 11-15 hours, and 0 otherwise

X11 X21 X31

exact number of hours studied (if the student studies calculus 0-5 hours) exact number of hours studied (if the student studies calculus 6-10 hours) exact number of hours studied (if the student studies calculus 11-15 hours)

Y11 Y21

exact number of hours studied (if the student studies chemistry 0-5 hours) exact number of hours studied (if the student studies chemistry 6-10 hours) exact number of hours studied (if the student studies chemistry 11-15 hours)

Y31 Z11 Z21 Z31

exact number of hours studied (if the student studies physics 0-5 hours) exact number of hours studied (if the student studies physics 6-10 hours) exact number of hours studied (if the student studies physics 11-15 hours)

W11 exact number of hours studied (if the student studies economics 0-5 hours) W21 exact number of hours studied (if the student studies economics 6-10 hours) W31 exact number of hours studied (if the student studies economics 11-15 hours) The objective is to maximize the final grade: Max: 75*X1 + 84*X2 + 93*X3 +76*Y1 + 87*Y2 + 94*Y3 + 65*Z1 + 81*Z2 + 91*Z3 + 85*W1 + 92*W2 + 97*W3 Constraints: X11 + X21 + X31 + Y11 + Y21 + Y31 + Z11 + Z21 + Z31 + W11 + W21 + W31 ≤ 40 time constraint

X1 + X2 + X3 = 1 Y1 + Y2 + Y3 = 1 Z1 + Z2 + Z3 = 1 W1 + W2 + W3 = 1 The student spends the same amount of time studding calculus and chemistry X11 + X21 + X31 - Y11 – Y21 – Y31 = 0 The student spends the double the amount of time studding physics than economics Z11 + Z21 + Z31 - 2*W11 – 2*W21 – 3*W31 = 0 The time spend studding calculus is between the time spend for physics and economics W11 + W21 + W31 ≤ X11 + X21 + X31 ≤ Z11 + Z21 + Z31 The following constraints link continuous and binary variables together 0 ≤ X11 ≤ 5*X1 6*X2 ≤ X21 ≤ 10*X2 11*X3 ≤ X31 ≤ 15*X3 0 ≤ Y11 ≤ 5*Y1 6*Y2 ≤ Y21 ≤ 10*Y2 11*YX3 ≤ Y31 ≤ 15*Y3 0 ≤ Z11 ≤ 5*Z1 6*Z2 ≤ Z21 ≤ 10*Z2 11*Z3 ≤ Z31 ≤ 15*Z3 0 ≤ W11 ≤ 5*W1 6*W2 ≤ W21 ≤ 10*W2 11*W3 ≤ W31 ≤ 15*W3 X1, X2, X3, Y1, Y2, Y3, Z1, Z2, Z3, W1, W2, W3 are binary X11, X21, X31, Y11, Y21, Y31, Z11, Z21, Z31, W11, W21, W31 ≥ 0 The following is the solution found from Excel solver. The student studding 40 hours a week will have an average of 92 points. The student has to spend 11 hours studding calculus and chemistry, 12 hours studding physics and 6 hours studding economics.

In the Excel spreadsheet, set the objective function and constraints the following way:

Set the solver in the following way:

8.11

The decision variables are: X1 number of police officers that start working 12pm X2 number of police officers that start working 4am X3 number of police officers that start working 8am X4 number of police officers that start working 12am X5 number of police officers that start working 4pm X6 number of police officers that start working 8pm The objective is to minimize the total number of police officers needed to meet the daily requirements: Min: X1 + X2 + X3 + X4 + X5 + X6 The following constraints insure that the daily requirements for police officers are met: X1 + X6 ≥ 8 X1 + X2 ≥ 7 X2 + X3 ≥ 6 X3 + X4 ≥ 6 X4 + X5 ≥ 5 X5 + X6 ≥ 4 X1,…,X6

are positive integers.

The following is the solution found using Excel solver.

In the Excel spreadsheet, set the objective function and constraints the following way:

Set the solver in the following way:

8.12

The decision variables for this problem are: X1 number of employees that start working on Monday X2 number of employees that start working on Tuesday X3 number of employees that start working on Wednesday X4 number of employees that start working on Thursday X5 number of employees that start working on Friday X6 number of employees that start working on Saturday X7 number of employees that start working on Sunday The weekly salary for the employees that start working on: Monday is: 5*100 = $500, Tuesday and Sunday is: 4*100 + 150 = $550 Wednesday through Saturday is: 3*100 + 2*150 = $600 The objective is to minimize the cost of staffing the call center: Min: 500*X1 + 550*(X2 + X7) + 600*(X3 + X4 + X5 + X6) The constraints are: X1 + X4 + X5 + X6 + X7 ≥ 18 requirements for Monday X1 + X2 + X5 + X6 + X7 ≥ 10 requirements for Tuesday X1 + X2 + X3 + X6 + X7 ≥ 12 requirements for Wednesday X1 + X2 + X3 + X4 + X7 ≥ 13 requirements for Thursday X1 + X2 + X3 + X4 + X5 ≥ 15 requirements for Friday X2 + X3 + X4 + X5 + X6 ≥ 10 requirements for Saturday X3 + X4 + X5 + X6 + X7 ≥ 15 requirements for Sunday X1,…,X7 are positive integers. The following is the solution found using Excel solver:

In the Excel spreadsheet, set the objective function and constraints the following way:

Set the solver in the following way:

8.13

In addition to the decision variables defined in the hands-on exercise 8.13, we have the following variables: Y1 number of part-time employees that start working on Monday Y2 number of part-time employees that start working on Tuesday Y3 number of part-time employees that start working on Wednesday The objective function is: Min: 500*X1 + 550*(X2 + X7) + 600*(X3 + X4 + X5 + X6) + 330*(Y1+Y2+Y3) The constraints are: X1 + X4 + X5 + X6 + X7 + Y1 ≥ 18 X1 + X2 + X5 + X6 + X7 + Y1 + Y2≥ 10 X1 + X2 + X3 + X6 + X7 + Y1 + Y2 + Y3 ≥ 12 X1 + X2 + X3 + X4 + X7 + Y2 + Y3≥ 13 X1 + X2 + X3 + X4 + X5 + Y3≥ 15

requirements for Monday requirements for Tuesday requirements for Wednesday requirements for Thursday requirements for Friday

X2 + X3 + X4 + X5 + X6 ≥ 10 X3 + X4 + X5 + X6 + X7 ≥ 15 Y1 + Y2 + Y3 ≤ 5

requirements for Saturday requirements for Sunday no more than 5 part-time employees should be hired

X1,…,X7, Y1,…,Y3 are positive integers. The following is the solution found using Excel solver:

In the Excel spreadsheet, set the objective function and constraints the following way:

Set the solver in the following way:

8.14

The decision variables are: X amount of fertilizer 1 produced Y amount of fertilizer 2 produced The objective is to maximize profits: (70 – 0.4*15 – 0.6*10)*X + (40 – 0.3*15 – 0.7*10)*Y The constraints are: 0.4*X + 0.3*Y ≤ 80 0.6*X + 0.7*Y ≤ 100 X ≥ 80 Y ≥ 30 The following is the solution found using Excel solver:

In the Excel spreadsheet, set the objective function and constraints the following way:

Set the solver in the following way:

8.15

The decision variables are: X1 number of cheesecakes produced in month 1 X2 number of cheesecakes produced in month 2 X3 number of cheesecakes produced in month 3 Y1 Y2 Y3

number of black forest cakes produced in month 1 number of black forest cakes produced in month 2 number of black forest cakes produced in month 3

Z1 Z2 Z3

number of cheesecakes in inventory in the end of month 1 number of cheesecakes in inventory in the end of month 2 number of cheesecakes in inventory in the end of month 3

W1 W2 W3

number of black forest in inventory in the end of month 1 number of black forest in inventory in the end of month 2 number of black forest in inventory in the end of month 3

The objective is to minimize costs:

Min: 3*X1 + 3.4*X2 + 3.8*X3 + 2.5*Y1 + 2.8*Y2 + 3.4*Y3 + 0.5*(Z1 + Z2 + Z3) + 0.4*(W1 + W2 + W3) The constraints are: X1 + Y1 ≤ 65 X2 + Y2 ≤ 65 X3 + Y3 ≤ 65

production capacity constraints

X1 = 40 + Z1 X2 + Z1 = 30 + Z2 X3 + Z2 = 20 + Z3

flow conservation constraints

Y1 = 20 + W1 Y2 + W1 = 30 + W2 Y3 + W2 = 10 + W3

flow conservation constraints

X1, X2, X3, Y1, Y2,Y3, Z1, Z2, Z3, W1, W2, W3 positive integers The following is the solution found using Excel solver:

In the Excel spreadsheet, set the objective function and constraints the following way:

Check the following option of the Excel Solver: Non-negativity assumed. Set the solver in the following way:

8.16

The decision variables for this problem are: X11 amount shipped from warehouse 1 to customer 1 X12 amount shipped from warehouse 1 to customer 2 X13 amount shipped from warehouse 1 to customer 3 X21 amount shipped from warehouse 2 to customer 1 X22 amount shipped from warehouse 2 to customer 2 X23 amount shipped from warehouse 2 to customer 3 The objective is to minimize the total of shipping and shortage costs: Min: 15*X11 + 35*X12 + 25*X13 + 10*X21 + 50*X22 + 40*X23 + 90*(30 – X11 – X21) + 80*(30 – X12 – X22) + 110*(30 – X13 – X23)

The constraints are: X11 + X21 ≤ 30 X12 + X22 ≤ 30 X13 + X23 ≤ 30 X11 + X12 + X13 ≤ 40 X21 + X22 + X23 ≤ 30

customer demand should be met

capacity constraint at warehouse 1 capacity constraint at warehouse 2

X11,…,X23 ≥ 0 The following is the solution found using Excel solver:

In the Excel spreadsheet, set the objective function and constraints the following way:

Check the following option of the Excel Solver: Non-negativity assumed. Set the solver in the following way:

8.17

To reformulate the problem we are adding a dummy warehouse that has shipping costs to customers $100. The additional decision variables are: X 31 amount shipped from the dummy warehouse to customer 1 X 32 amount shipped from the dummy warehouse to customer 2 X33 amount shipped from the dummy warehouse to customer 3 The objective function is now formulated as follows: Min: 15*X11 + 35*X12 + 25*X13 + 10*X21 + 50*X22 + 40*X23 + 100*(X31 + X32 + X33) The constraints are: X11 + X21 + X31 = 30 X12 + X22 + X32 = 30 X13 + X23 + X33 = 30

customer demand should be met

X11 + X12 + X13 ≤ 40 capacity constraint at warehouse 1 X21 + X22 + X23 ≤ 30 capacity constraint at warehouse 2 There is no capacity constraint for the dummy warehouse X11,…,X33 ≥ 0 The solution found using Excel solver is:

In the Excel spreadsheet, set the objective function and constraints the following way:

Check the following option of the Excel Solver: Non-negativity assumed. Set the solver in the following way:

8.18

The decision variables are:

X12 X13 X21 X23 X31 X32

the amount of U.S. dollars exchanged for English pounds the amount of U.S. dollars exchanged for Japanese yen the amount of English pounds exchanged for U.S. dollars the amount of English pounds exchanged for Japanese yen the amount of Japanese yen exchanged for U.S. dollars the amount of Japanese yen exchanged for English pounds

The objective is to maximize the surplus inventory of U.S. dollars: Max: (40,000 + 1.6*X21 + 0.88*X31 - X12 - X13) – 50,000 The constraints are: 40,000 + 1.6*X21 + 0.88*X31 - X12 - X13 ≥ 50,000 90,000 + 0.61*X12 + 0.55*X32 - X21 - X23 = 95,000 100,000 + 1.11*X13 + 1.82*X23 - X31 - X32 = 90,000 X12,…, X32 ≥ 0 The following is the solution found using Excel solver:

In the Excel spreadsheet, set the objective function and constraints the following way:

Check the following option of the Excel Solver: Non-negativity assumed. Set the solver in the following way:

8.19

The decision variables for this problem are: X1 number of drivers purchased from company A X2 number of drivers purchased from company B X3 number of drivers purchased from company C Y1 takes the value 1 if drivers are purchased from company A, 0 otherwise Y2 takes the value 1 if drivers are purchased from company B, 0 otherwise Y3 takes the value 1 if drivers are purchased from company C, 0 otherwise The objective is to minimize costs: 16,000*Y1 + 250*X1 + 370*X2 + 9,000*Y3 + 270*X3 The constraints are: X1 + X2 + X3 = 700 X1 ≤ 300*Y1 X2 ≤ 450*Y2 X3 ≤ 550*Y3 X1, X2, X3 positive integers Y1, Y2, Y3 binary variables The following is the solution found using Excel solver:

In the Excel spreadsheet, set the objective function and constraints the following way:

Set the solver in the following way:

8.20

Use the same decision variables as for hands-on exercise 8.19.

The objective is to minimize costs: 16,000*Y1 + 250*X1 + 370*X2 + 9,000*Y3 + 270*X3 The constraints are: X1 + X2 + X3 = 700 X1 ≤ 300*Y1 X2 ≤ 450*Y2 X3 ≤ 550*Y3 0 ≤ X1 ≤ 0.65*700 0 ≤ X2 ≤ 0.65*700 0 ≤ X3 ≤ 0.65*700 X1, X2, X3 integers Y1, Y2, Y3 binary variables The following is the solution found using Excel solver:

In the Excel spreadsheet, set the objective function and constraints the following way:

Set the solver in the following way:

8.21

The decision variables for this problem are: X12 takes value 1 if arc (1, 2) is in the route that connects the plant to the warehouse, and 0 otherwise X13 takes value 1 if arc (1, 3) is in the route that connects the plant to the warehouse, and 0 otherwise X23 takes value 1 if arc (2, 3) is in the route that connects the plant to the warehouse, and 0 otherwise X24 takes value 1 if arc (2, 4) is in the route that connects the plant to the warehouse, and 0 otherwise X25 takes value 1 if arc (2, 5) is in the route that connects the plant to the warehouse, and 0 otherwise X35 takes value 1 if arc (3, 5) is in the route that connects the plant to the warehouse, and 0 otherwise

X36 X45 X46 X56

takes value 1 if arc (3, 6) is in the route that connects the plant to the warehouse, and 0 otherwise takes value 1 if arc (4, 5) is in the route that connects the plant to the warehouse, and 0 otherwise takes value 1 if arc (4, 6) is in the route that connects the plant to the warehouse, and 0 otherwise takes value 1 if arc (5, 6) is in the route that connects the plant to the warehouse, and 0 otherwise

The objective is to minimize the distance traveled from the plant to the warehouse: Min: X12 + 3*X13 + 2*X23 + X24 + 3*X25 + X35 + 4*X36 + 6*X45 + 7*X46 + 5*X56 The constraints are: X12 + X13 = 1 X23 + X24 + X25 – X12 = 0 X35 + X36 – X13 –X23 = 0 X45 + X46 – X24 = 0 X56 – X25 – X35 – X45 = 0 X36 + X56 = 1 X12,…, X56 binary The following is the solution found using Excel solver. The shortest route that starts at the plant and ends at the warehouse includes arcs (1, 2), (2, 4), (4, 6).

In the Excel spreadsheet, set the objective function and constraints the following way:

Set the solver in the following way:

8.22

The decision variables and constraints for this exercise are the same as for hands-on exercise 8.21. The objective function changes to the following: Max: 3*X12 + 4*X13 + 7*X23 + 5*X24 + 20*X25 + 2*X35 + 10*X36 + 8*X45 + 12*X46 + 15*X56

The following is the solution found using Excel solver:

In the Excel spreadsheet, set the objective function and constraints the following way:

Set the solver in the following way:

8.23

The following is the solution found by Excel solver:

In the Excel spreadsheet, set the objective function and constraints the following way:

Check the following option of the Excel Solver: Non-negativity assumed. Set the solver in the following way:

Chapter 9 Review Exercises 9.1

Simulation is a modeling tool which is used to imitate a real-world process in order to understand system behavior.

9.2

Simulation differs from optimization in that instead of seeking to optimize an objective function value, it simulates the behavior of a system to asses its performance under several scenarios which may be used in what-if analysis.

9.3

To activate the Scenarios function one can go to Tools > Scenarios in the main menu. Click on the add button to add a new scenario.

9.4

Scenarios function is a useful tool as allows the user to vary up to 32 input cells for various values, or scenarios, and observe the results of several output cells. The Scenarios function will create a Scenario Report which shows the resulting output values for each scenario of input values.

9.5

The following are four distribution functions under the Statistical category of functions is Excel: LOGNORMDIST, NORMDIST, NORMSDIST, and BETADIST.

9.6

The following are four inverse distribution functions under the Statistical category of functions is Excel: LOGINV, NORMINV, NORMSINV, and BETAINV.

9.7

In order to generate a random number within a given distribution, one must use the inverse functions of the corresponding distribution function. For example, to generate a random number from the Normal distribution, one should use the following formula: ‘=NORMINV(RAND(), mean, std dev)’.

9.8

LN or LOGINV are the inverse distribution functions to be used for generating a random number within an Exponential distribution.

9.9

‘=GAMMAINV(RAND(),5,8)’

9.10

Companies analyze the sensitivity of demand to price. In this case, an Excel simulation model could be used to calculate the demand for the final product as a function of price.

Hands-on Exercises 9.1

The following are the results from the simulation:

To original data used to generate the scenarios is given below:

The results of the simulation indicate that: (a) if the student gets 100 in one of the exams, in the other exam he can get as low as 95 and still reach his goal; (b) if the student gets 99 in one of the exams, in the other exam he can get as low as 96 to have an overall average of 93; (c) if the students gets 98 in one of the exams, then in the next exam he could get as low as 97. 9.2 a. To generate 5 random numbers exponentially distributed with mean 12 and standard deviation 2, use the following Excel function: ‘=LOGINV(RAND(),12,2)’. b. To generate 5 random numbers from Beta distribution with an alpha of 4 and a beta of 3, use the following Excel function: ‘=BETAINV(RAND(),4,3)’. c. To generate 5 random numbers from Chi-squared distribution with 6 degrees of freedom, use the following Excel function: ‘=CHIINV(RAND(),6)’. d. To generate 5 random numbers from standard normal distribution, use the following Excel function: ‘=NORMSINV(RAND())’. The following table presents the numbers generated:

9.3

The following are the results of the simulation:

9.4

The following table presents the results from the scenario analysis. We created 3 scenarios. The first scenario considers that the bakery purchases only 200 lbs of white flour. The total costs for each vendor are calculated. The results show that vendor C is the best choice for this product. The second scenario considers that the bakery purchases only 200 lbs of wheat flour. The total costs per vendor are calculated and again the total costs are minimized if vendor C is picked. The third scenario considers that the bakery purchases only 200 lbs of wheat sugar. The total costs per vendor are calculated. The total costs are minimized if vendor A is picked.

9.5

The following table presents 40 random numbers from Chi-squared distribution with degrees of freedom 3.5.

The Excel spreadsheet is setup as shown in the table below:

9.6

The following are the results from scenario analysis.

Based on the results from the simulation analysis, the best option is Bank E. 9.7

Considering that the time it takes to process a box is normally distributed with mean 30 seconds, and the time to failure is normally distributed with mean 4*3600 = 14,400 seconds, we expect that on average after every 480 boxes processed the blades on the Flex-O machine would fail. Keeping that information in mind, we created 12 different scenarios. In each scenario we randomly generate processing times for boxes using the following Excel function: ‘=NORMINV(RAND(),30,3)’ and failure time: ‘=NORMINV(RAND(),14400,900)’. In the first scenario we consider that 485 boxes would be processed before the blade is changed. In the second scenario, we consider 482 boxes would be processed before the blade was changed, and so on until in scenario 12 we assume that 450 boxes would be processed before we change the blade. For each scenario we performed 30 different simulation runs. We finally calculate the percentage of time that the blade failed before being changed. The following table presents the Excel functions used to generate the failure time and processing times. The same set of formulas, as in column C, are used up to column AF (each column corresponds to a run).

The following table presents the final results of this simulation. The number in cells AI2:BL13 is 1 if the blade fails prior to being changed and, 0 otherwise. The numbers in cells BM2:BM13 present the number of times (in percentage) the blade fails prior to being changed (under each scenario).

Based on the results of the simulation, if the blade is changed after 450 boxes are processed, only 7% of the time the blade would fail prior to processing the 450 boxes. The formulas used are:

9.8

The following are the results from the simulation analysis:

The Excel spreadsheet is set-up as shown below:

9.9 a.

The following table presents the expected number of batches ordered and the corresponding supply time.

To generate demand and supply time we used the following formulas:

b.

The following are the results from the simulation analysis:

9.10 a.

b.

The following table presents the profits for different sales amount. The results are generated using Data Tables.

The following results are generated using the Scenario Manager.

9.11

The following table presents the results from the simulation analysis.

To perform this analysis we set-up the Excel spreadsheet the following way:

Randomly generate 10 demand points normally distributed with mean 200 and standard deviation 30. For each demand point calculate the profits as follows:

if D >= OQ  D * $15,000 P=  D * $15,000 + (OQ − D) * $5,000 if D < OQ

.

Where, P denotes profits, D denotes demand and OQ denotes order quantity. Expected demand is the average of the 10 random demands generated, and expected profit is the average of profits generated for the 10 runs. The following presents the formulas used to generate expected demand and profits.

9.12

In order to find the probability of a win in the game of keno we followed these steps: a. Randomly generate a single game. To do so: i. Randomly generate 20 numbers (the numbers on the balls drawn) between 1 and 80 using the Excel function ‘=RANDBETWEEN(1,80)’. ii. Randomly generate 10 numbers (the numbers picked by the player) between 1 and 80 using the Excel function ‘=RANDBETWEEN(1,80)’. iii. Compare the numbers generated in part (i) with the ones generated in part (ii) to see if they match. One can do that using Excel functions IF and OR. iv. If there are at least 5 matches from step (iii), we have a win, b. Repeat part (a) 100 times. c. Sum-up the number of wins (step (a. iv)) and divide the sum by 100. The result presents the probability of win. Set the Excel spreadsheet the following way:

Based on this simulation, the probability of winning is 0.06, since out of 100 games simulated we had a total of 6 wins. Set the formulas in Excel spreadsheet the following way: To generate the numbers in cells B3:AE102, use the Excel function: ‘=RANDBETWEEN(1,80)’. In columns AF to AO we present the result from comparing each of the 10 Numbers Picked with the 20 balls collected from the drawing. For example, cell AL3 has a value equal to 1, because the 7-th number we picked (17) is the same with the 8-th ball collected. To generate the numbers in cells AF3:AO102:

type the following formula in cell AF3 and copy the formula in the rest of the cells: =IF(OR(V3=$B3,V3=$C3,V3=$D3,V3=$E3,V3=$F3,V3=$G3,V3=$H3,V3=$I3,V 3=$J3,V3=$K3,V3=$L3,V3=$M3,V3=$N3,V3=$O3,V3=$P3,V3=$Q3,V3=$R3,V 3=$S3,V3=$T3,V3=$U3),1,0) To generate the numbers in cells AP3:AP102, type the following formula in cell AP3 and copy it to the rest of the cells: ‘ =IF(SUM(AF3:AO3)>4,1,0)’. Type in cell AP103: ‘=sum(AO3:AO102)’. The probability of a win is then calculated as: ‘=AP103/100’. 9.13

The following are the results of the simulation.

Based on the results of this simulation, if the bidder bids $40 millions, he has a probability of win equal to 0.97. If he bids $30 millions, his probability of win is only 0.08. In order to create the different scenarios, set-up the Excel spreadsheet in the following way:

The formulas used are:

9.14

For team A to win, it should have at least 4 wins. We simulated each scenario 100 times and calculated the number of times Team A had a win.

The following figure presents the Excel spreadsheet we created for this problem:

The following formula is used to find out if in a particular run Team A had a win using Sequence 1 (column B): =IF((IF(RAND()>0.4,1,0) + IF(RAND()>0.4,1,0) + IF(RAND()>0.6,1,0) + IF(RAND() > 0.6,1,0) + IF(RAND()>0.4,1,0) + IF(RAND()>0.6,1,0)+ IF(RAND()>0.4,1,0))>3,1,0) The following formula is used to find out if in a particular run Team A had a win using Sequence 2 (column C): =IF((IF(RAND()>0.4,1,0) + IF(RAND()>0.4,1,0) + IF(RAND()>0.6,1,0) + IF(RAND() >0.6,1,0) + IF(RAND()>0.6,1,0) + IF(RAND()>0.4,1,0)+ IF(RAND()>0.4,1,0))>3,1,0) The probability of winning is then calculated by dividing the number of wins over 100 runs, by 100.

9.15

The following figure presents the results of the simulation. The expected amount won is $-1. In order to calculate the expected amount won from the Chuck-a-Luck game we followed these steps: a. Randomly generate a number between 1 and 6 in cell B3 (the number picked) using the Excel function ‘=RANDBETWEEN(1,6)’. b. Randomly generate 3 numbers between 1 and 6 in cells C3:E3 (the results from tossing 3 dices) using Excel function ‘=RANDBETWEEN(1,6)’. c. Compare the number picked (step a.) with the results from tossing the dices (step b.) and calculate the amount won, using the following Excel function in cell F3: =IF(AND(B3=C3,B3=D3,B3=E3),3,(IF(OR(AND(B3=C3,B3=D3),AND( B3=C3,B3=E3), AND(B3=E3,B3=D3)),2,IF(OR(B3=C3,B3=D3,B3=E3),1,-1)))) d. Repeat steps a to c 100 times (copy cells B3:F3 in B4:F102). e. Sum the results from part c over the 100 runs and divide this number by 100 to find the expected amount won using the formula: ‘=sum(F3:F102)/100’. Set the Excel spreadsheet the following way:

9.16

Set the Excel spreadsheet the following way:

To calculate the expected demand and expected profits follow these steps: a. Calculate demand in cell B2 using the following Excel function: =IF(RAND()<= 0.3,20, IF(RAND()<=0.5,25, IF(RAND()<=0.75,30, IF(RAND()<=1,40)))) b. Calculate the corresponding profits in cell C2 using the following Excel function: =IF($G$1>=B2,B2*5000-1000*($G$1-B2),$G$1*5000+(B2$G$1)*3000) c. Copy cells B2 and C2 to cells B3:C101. d. Calculate the expected demand in cell G1 as: ‘=AVERAGE(B2:B101)’. e. Calculate the expected profits in cell G2 as: ‘=AVERAGE(C2:C101)’. 9.17

The following figure presents the results of the simulation.

The following are the formulas used:

9.18

Let X denote the number of reservations made. 0.95*X is the number of passengers showing up. The following formula is used to calculate the profits for the airline. Profit = 150*X – (8,000 + 30*min{100, 0.95*X} + 300*max{0, 0.95*X}) We create different scenarios by changing the number of reservations (X) and calculate the expected profits. The following are the results from the simulation analysis:

Based on the results from the simulation, making 106 reservations maximizes the profits. 9.19 a. b. c.

Based on the results from the simulation study, on average a patient spends 58 minutes in the emergency room. Based on the results from the simulation study, on average a patient spends 21 minutes waiting for the doctor. The doctor is busy 63% of the time.

In order to answer parts a, b and c of this problem we created the following Excel spreadsheet:

The formulas used are:

9.20

Based on simulation we find that the probability of a win is 0.2. The following table presents the results of the simulation:

The following are the formulas used:

9.21

The following are the results from using the two-way Data Table to calculate the monthly payments for the loan under different scenarios:

9.22 a. The following are the results from simulating the fast food restaurant.

The following figure presents the formulas used:

b. Based on the simulation, we found out that: Expected waiting time per customer is 1.82 minutes Expected total time in the system is 6.18 minutes Maximum waiting time is 6.50 minutes The server is idle 5.73% of the time. c. After hiring the new employee, the results of the simulation show that: Expected waiting time per customer is 0.16 minutes Expected total time in the system is 4.14 minutes Maximum waiting time is 1.40 minutes The server is idle 13.80% of the time. 9.23

The following table presents the results of the simulation:

Based on the results of the simulation order size of 40 maximizes the total profits. The formulas used are:

9.24

Set the Excel spreadsheet the following way to calculate the time in the system, waiting time and server idle time for FIFO service rule:

The formulas used are:

The results from using FIFO and LIFO are: For the FIFO System Expected waiting time is 37.10 minutes Expected total time in the system is 83.85 minutes Maximum waiting time is 110 minutes The server is idle 6.69% of the time. For the LIFO System Expected waiting time is 37.00 minutes Expected total time in the system is 83.75 minutes Maximum waiting time is 615 minutes The server is idle 7.88% of the time.

Chapter 10 Review Questions 10.1

Sorting is the ordering of all entries in a database by a particular field. Field is the name of a category by which items are grouped in a database. Sorting and field are related as you sort a database by a particular field.

10.2

The data can be sorted in an ascending or descending order.

10.3

Filtering differs from sorting because it selects a specified set of data from the database instead of ordering the entire database. Filtering allows us to select a group of entries in our database that are equal to a particular data entry within a field.

10.4

Auto filter will transform the display of the spreadsheet by adding drop-down arrows to each field.

10.5

The Top 10 option gives the option of showing on the top or bottom 10 entries (or another specified amount), or the top or bottom 10 percent of entries in the database.

10.6

The Custom option from a filtered drop-down list gives the user a set of inequality specifications. The user can view entries whose field value is less than, greater than, or equal to a certain value.

10.7

Consider the case where, in the end of the semester, a professor has to decide about the final grades. He will give: an A to the students that have an overall average between 90 and 100; a B to the students with an overall average between 80 and 89; and so on. Filtering would help in this case. He can filter the database, so that it presents only the students with an overall average greater than or equal to 90; next he can filter the database so that it presents the students with an overall average between 80 and 89; and so on.

10.8

Dfunctions is a group of Excel functions that are designed for working with Excel as a database. These functions are essentially the same as the (ordinary) Excel functions discussed in Chapter 4. However, Dfunctions differ from the previously described functions because they specify certain criteria before performing the function.

10.9

Five Dfunctions available in Excel are: DSUM, DAVERAGE, DMIN, DMAX, and DSTDEV.

10.10 The criteria parameter must include a field name and a criteria cell. 10.11 Dfunctions are useful for databases as they allow the user to consider many options in the search for a particular value. 10.12 The data from an Excel database can be exported to another workbook or to a database in another program, such as Microsoft Access. 10.13 We will show how to refer to an external database when using Dfunctions with an example. Consider the Dfunction DSUM. The format of this function is: ‘=DSUM(database, field, criteria)’. Suppose that we want to sum up the numbers in the column B, based on a certain criteria, in a Database that is saved in the Excel file Database1.xls. To refer to this database, we would use DSUM Dfunction as follows: =DSUM([Database1.xls]Sheet1!$A$1:$B$7,[Database1.xls]Sheet1!$B$1, [Database1.xls]Sheet1!$B$8:$B$9) 10.14 A query is a search for a particular set of data from a certain database, similar to filtering. 10.15 In order to run a query before data has been imported from an external database, follow these steps: choose Data > Import External Data > New Database Query from the main menu. The window “Choose Data Source” appears. This window enables the user to configure the data source for the query. The next window that appears is the “Select Database” window that gives a list of files that match the data source type. Next step is to specify which column of data should appear in the final table and finally specify how the user wants to filter the data. Hands-on Exercises 10.1

a.

The following table presents the names of the workers that will be promoted.

b. The following table presents the name of the employee that is qualified to be the team leader.

10.2

a. The following figure presents the data sorted by employee name in an ascending order.

b. The number of employees that attended the assembly of more that 400 units of output during week 1, is 11. The formula to be used is: ‘=COUNTIF(B3:B22, ">= 400")’. c. The number of employees that attended on average the assembly of at most 5 defective products per week is 15. The formula used is: ‘=COUNTIF(M3:M22, "<=5")’. 10.3

The following figure presents the results for this exercise.

The following figure presents the formulas used.

10.4

The following figure presents the results for this exercise.

The following figure presents the formulas used.

10.5

a. The following are the results from filtering the database to show only the students that never missed a class.

b. The following are the results from filtering the database to show the students that missed at least one class:

c. The average grade for the students that never missed a class is: 87.33. d. The average grade for the students that missed 3 classes is: 82.37. The results show that there is a relationship between the classes missed and the average grade. The figure below shows the formulas used to calculate the averages.

10.6

a. The class average for the 1-st exam is 81.43. The following figure gives the names, grades and attendance for the students that performed better than class average in this exam.

b. The class average for the 2-nd exam is 87.34. The following figure gives the names, grades and attendance for the students that performed better than class average in this exam.

Looking to the results from parts (a) and (b), one can see that most of the students (6 out of 8) that did well in the first exam, did well in the second exam too. 10.7

The total number of cardholders that are eligible for the platinum-level membership is 6. The following figure presents the formulas used.

10.8

The following figure presents the names, enrollment date, credit limit and number of late payments for the eligible cardholders.

To find this information, follow these steps:

a. Select cells A2:D22. Select Data > Filter > AutoFilter from the main menu. b. Select Custom from the drop-down list of values in the Enrollment Date field. Set the Custom AutoFilter to: is less than 1/1/2002. c. Select Custom from the drop-down list of values in the Credit Limit field. Set the Custom AutoFilter to: greater than or equal to 3000. d. Select Custom from the drop-down list of values in the Nr. of Late Payments field. Set the Custom AutoFilter to: less than or equal to 1. 10.9

The following figure presents the results for this exercise:

The following figure presents the formulas used.

10.10 The following figure presents the results for this exercise.

The following figure presents the formulas used:

10.11 In order to ensure that each cell in the range A1:A10 contains at most 5 characters, follow these steps: a. Select cells A1:A10. b. Choose: Data > Validation from the main menu. The “Data Validation” window appears. c. In the “Data Validation” window, select the Setting tab. From the combo box Allow select Text length. From the combo box Data, select less than or equal to. In the textbox Maximum type 5. d. In the “Data Validation” window, select the Input Message tab. In the Title textbox type Text length!. In the Input Message textbox type There should be at most 5 characters in a cell.

e. In the “Data Validation” window, select the Error Alert tab. In the Title textbox type Text length!. In the Error Message textbox type Error! There are more than 5 characters in this cell. f. Click on the OK command button. The following figure presents an example of the error message received in case that the user typed a text with more than 5 characters.

10.12 Use the Custom validation option and enter the following formula. =COUNTIF($B$1:$B$10, B1)<2 10.13 To ensure that in the column that contains the employee names, the user enters only text, follow these steps: a. Select column A of the database. In this column the user types-in the employee names. b. Choose: Data > Validation from the main menu. The “Data Validation” window appears. c. In the “Data Validation” window, select the Setting tab. From the combo box Allow select Custom. From the combo box Formula, type ‘=Istext(A2)’. d. In the “Data Validation” window, select the Error Alert tab. In the Title textbox type Error! In the Error Message textbox type: This is a text field! e. Click on the OK command button. The following figure presents an example of the error message received in case that the user does not type text on the column with the names of the employees.

To ensure that the phone numbers have 10 digits, follow these steps: a. Select column B of the database. In this column the user types-in the phone numbers. b. Choose: Data > Validation from the main menu. The “Data Validation” window appears. c. In the “Data Validation” window, select the Setting tab. From the combo box Allow select Whole number. From the combo box Data, select between. In the textbox Minimum type 99999999, and in the textbox Maximum type 999999999. d. In the “Data Validation” window, select the Error Alert tab. In the Title textbox type Error! In the Error Message textbox type: The phone number should have 10 digits! e. Click on the OK command button. The following figure presents an example of the error message received in case that the user types in column B, a number with less than or more than 10 digits.

To ensure that the price owed is not negative, follow these steps: a. Select column C of the database. In this column the user types-in the price owed. b. Choose: Data > Validation from the main menu. The “Data Validation” window appears. c. In the “Data Validation” window, select the Setting tab. From the combo box Allow select Decimal. From the combo box Data, select greater than or equal to. In the textbox Minimum type 0. d. In the “Data Validation” window, select the Error Alert tab. In the Title textbox type Error! In the Error Message textbox type: The price should be positive! e. Click on the OK command button. The following figure presents an example of the error message received in case that the user types in column C, a negative number.

10.14 The following figure presents the data imported from the website ‘http://www.baseball-reference.com/b/bondsba01.shtml’. In order to import the data, follow these steps: a. Choose Data > Import External Data > New Web Query from the main menu. b. In the New Web Query window that appears type at the textbox Address ‘http: //www.baseball-reference.com/b/bondsba01.shtml’. c. In the website that appears in New Web Query window, select the data you are interested in and click the command button Import.

The data that we imported presents the annual salary for the Pittsburgh Pirates and San Francisco Giants baseball teams. We calculated the average annual salary for each team using these formulas: ‘=DAVERAGE(A1:C19,C1,B20:B21)’ to calculate the average salary for the Pittsburgh Pirates, and

‘=DAVERAGE(A1:C19,C1,C20:C21)’ to calculate the average salary for the San Francisco Giants. 10.15 The following figure presents the data imported from the website ‘http://www.xrates.com/’. In order to import the data, follow these steps: c. Choose Data > Import External Data > New Web Query from the main menu. c. In the New Web Query window that appears type at the textbox Address ‘http://www.x-rates.com/’. c. In the website that appears in New Web Query window, select the data you are interested in and click the command button Import.

In order to update the exchange rates, click at the Refresh All button in the External Data toolbar. 10.16 In order to import the text file to Excel follow these steps: a. Choose File > Open from main menu. As the user opens a text file in Excel, the Text Import Wizard window appears. b. Check the Delimiter option in the Step 1 of the Text Import Wizard. c. Check Tab option in the Delimiter option box in Step 2 of the Text Import Wizard. d. Click on the command button Ok in Step 3 of the Text Import Wizard. 10.17 In order to import the text file to Excel follow these steps: a. Choose File > Open from main menu. As the user opens a text file in Excel, the Text Import Wizard window appears. b. Check the Delimiter option in the Step 1 of the Text Import Wizard. c. Check Tab option in the Delimiter option box in Step 2 of the Text Import Wizard. d. In Step 3 of the Text Import Wizard, in the Data Preview list box, select the column that carries the addresses. Check the Do not import column (skip) option in the Column Data Format option box.

e. In Step 3 of the Text Import Wizard, in the Data Preview list box, select the column that carries the zip code. Check the Do not import column (skip) option in the Column Data Format option box. f. In Step 3 of the Text Import Wizard, click the OK command button. 10.18 The following figure presents the results for this problem.

10.19 In order to filter the database select Data > Filter > AutoFilter from the main menu. e. The following table presents the results of filtering. To filter the data select Top 10 from the drop-down list of values in the Dollar field. Set the Top 10 AutoFilter to: top 5 percent.

f.

The following table presents the results of filtering. To filter the data select Custom from the drop-down list of values in the Units field. Set the Custom AutoFilter to: is greater than 50. Select Custom from the drop-down list of values in the Date field. Set the Custom AutoFilter to: greater than 12/31/2003 and less than or equal to 12/31/2004.

g.

The following table presents the results of filtering. To filter the data select lip gloss from the drop-down list of values in the Product field. Select Custom from the drop-down list of values in the Date field. Set the Custom AutoFilter to: greater than 12/31/2003 and less than 7/1/2004.

h.

The following table presents the results of filtering. Calculate the average money made on eye liners using the formula: ‘= DAVERAGE (A1:G26, F1, D27:D28)’. The average money made on eye liners is $134.58. To filter the data select eye liner from the drop-down list of values in the Product field. Select Custom from the drop-down list of values in the Dollar field. Set the Custom AutoFilter to: greater than 134.58.

10.20 a. The following table presents the Max sales for each product, in each month for both regions.

b. The following table presents the total number of sales made for each product in each month.

10.21 a. The average amount sold during the first half of March in region 1 is 108.13. b. The average amount of product A sold during the second half of January in region 1 is 462. c. The minimum amount of product B sold during the month of February in region 1 is 0. d. The total amount sold in January 1st in region 1 is 647. The formulas used for this problem are:

10.22 a. The following table summarizes the information presented in the three tables.

To create the table, follow these steps: - Select Data > Consolidate from the main menu. The Consolidate window appears. - In the consolidate window, select any of the options from the Function combo box.

-

Select the three tables together with their row and column headings, one at a time and add their address to the Address list box. Finally check Top Row and Left Column from the Use Labels In option box.

b. The following table presents the average, maximum and minimum grade a student received in the exams. To create the tables, follow these steps: - Select Data > Consolidate from the main menu. The Consolidate window appears. - In the consolidate window select Average/Maximum/Minimum from the Function combo box. - Select the three tables together with their row headings only, one at a time and add their address to the Address list box. - Finally check Left Column from the Use Labels In option box.

10.23 a. Select the column that carries product identification numbers. - Choose Data > Validation from the main menu. The “Data Validation” window appears. - In the “Data Validation” window, select the Setting tab. From the combo box Allow select Whole number. From the combo box Data, select between. In the textbox Minimum type 9999. In the textbox Maximum type 99999. - In the “Data Validation” window, select the Error Alert tab. In the Title textbox type Error!. In the Error Message textbox type Identification number should have exactly 5 digits!. - Click on the OK command button. b. Select the column that carries the information about the inventory level. - Choose Data > Validation from the main menu. The “Data Validation” window appears. - In the “Data Validation” window, select the Setting tab. From the combo box Allow select Decimal. From the combo box Data, select greater than or equal to. In the textbox Minimum type 0. - In the “Data Validation” window, select the Error Alert tab. In the Title textbox type Error!. In the Error Message textbox type Inventory level should be positive!. - Click on the OK command button. c. Select the column that carries the information about the unit price. - Choose Data > Validation from the main menu. The “Data Validation” window appears.

-

In the “Data Validation” window, select the Setting tab. From the combo box Allow select Decimal. From the combo box Data, select greater than or equal to. In the textbox Minimum type 150. In the “Data Validation” window, select the Error Alert tab. In the Title textbox type Error!. In the Error Message textbox type The unit price should be at least $150!. Click on the OK command button.

d. Type fair, good very good in three different cells in the same excel spreadsheet. Select the column that carries the information about the product condition. - Choose Data > Validation from the main menu. The “Data Validation” window appears. - In the “Data Validation” window, select the Setting tab. From the combo box Allow select List. In the text box Source specify the location of the cells that carry the text (fair, good and very good) typed earlier. - In the “Data Validation” window, select the Error Alert tab. In the Title textbox type Error!. In the Error Message textbox type The condition of the inventory should either be Fair, or Good or Very Good!. - Click on the OK command button. e. Select the column that carries the information about the purchase date. - Choose Data > Validation from the main menu. The “Data Validation” window appears. - In the “Data Validation” window, select the Setting tab. From the combo box Allow select Date. From the combo box Data, select greater than or equal to. In the textbox Start Date type 1/1/2000. - In the “Data Validation” window, select the Error Alert tab. In the Title textbox type Error!. In the Error Message textbox type The purchased date cannot be smaller than 1/1/2000!. - Click on the OK command button. 10.24 a. In order to import the database to Excel follow these steps: - Select Data > Import External Data > Import Data from the main menu. The Select Data Source window appears. - Specify the path to the School.dbm file and click-on the OK button. - The Select Table window appears. Select the table tblStudents and clickon the OK button. b. The following table presents detailed information about the graduate students enrolled not earlier than 1/1/2000. - Select Data > Filter > AutoFilter from the main menu. - Select Graduate from the drop-down list of values in the Category field. - Select Custom from the drop-down list of values in the EnrollDate field. Set the Custom AutoFilter to: greater than or equal to 1/1/2000.

c. The following table presents detailed information about the undergraduate students that have earned at least 6 credit hours. -

Select Data > Filter > AutoFilter from the main menu. Select Undergraduate from the drop-down list of values in the Category field. Select Custom from the drop-down list of values in the Credits field. Set the Custom AutoFilter to: greater than or equal to 6.

d. The following table presents detailed information about the undergraduate students from Florida. -

Select Data > Filter > AutoFilter from the main menu. Select Undergraduate from the drop-down list of values in the Category field. Select FL from the drop-down list of values in the State field.

10.25 a. In order to import the database to Excel follow these steps: - Select Data > Import External Data > Import Data from the main menu. The Select Data Source window appears. - Specify the path to the School.dbm file and click-on the OK button. - The Select Table window appears. Select the table tblInstructorss and click-on the OK button. b. -

In order not to allow for salaries less than $40,000, use the data validation tools as follow. Choose Data > Validation from the main menu. The “Data Validation” window appears. In the “Data Validation” window, select the Setting tab. From the combo box Allow select Decimal. From the combo box Data, select greater than or equal to. In the textbox Minimum type 40000. In the “Data Validation” window, select the Error Alert tab. In the Title textbox type Error!. In the The annual salary cannot be less than $40,000. Click on the OK command button.

c. The following table presents detailed information about the instructors that have earned the title Professor. - Select Data > Filter > AutoFilter from the main menu. - Select Professor from the drop-down list of values in the Title field.

d. The following table presents detailed information about the professors that are not USA citizens. - Select Data > Filter > AutoFilter from the main menu. - Select Blank from the drop-down list of values in the State field.

e. The following table presents detailed information about the professors that make top 10% of the annual salary. - Select Data > Filter > AutoFilter from the main menu. - Select Top 10 from the drop-down list of values in the Salary field. - Set the Top 10 AutoFilter window to show top 10 percent. Click-on the OK command button.

Chapter 11 Review Exercises: 11.1

The three parts of an Excel project are: objects, modules and forms. Objects are elements in a workbook, including the workbook and worksheets. Modules contain the VBA code for any macro. Forms are the user interface which has both user display design and VBA code.

11.2

Properties are the physical descriptions of all Excel objects. For example, a range object would have a length property and a cell object may have a font size property. Methods are the actions that can be performed on the object. For example, a range can be selected and a cell can be deleted.

11.3

Properties of the Chart class are: ChartTitle, ChartType and ChartArea.

11.4 a. b. c. d.

WindowActivate: is an event and member of Workbook class. Cells: is a property and member of the Worksheet class. SumIf: is a function and a member of the WorksheetFunction class. Protect: is a function and a member of Chart, Workbook and Worksheet classes. e. ColorIndex: is property and a member of the Border class.

11.5

A procedure is a section of the code that is used to perform different tasks with Excel objects.

11.6

The two main procedure types are: sub procedures and function procedures.

11.7

Some of the options on the Edit toolbar are: List Properties/Methods, List Constants, Quick Info, Parameter Info, Complete Word, Indent, Outdent, etc.

11.8

The arguments of the Sort method are: Key1, Order1, Key2, Type, Oder2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3.

11.9

Project Explorer and Property windows do not have to be open at all times. One can write a code in VBE while the Project Explorer and Property windows are closed.

11.10 To insert a module to a project click Insert > Module in the VBE window. You can also use the

icon to see a drop down list of insert options.

Chapter 12 Review Questions: 12.1 A macro is a way to automate a series of actions in a spreadsheet application. 12.2 Macros are helpful as they automate a series of action in a spreadsheet application, saving users time and reducing the chances for making an error. In particular, when repetitive and tedious tasks are needed to be performed, macros are very useful. 12.3 The steps involved in recording a macro are: a. Choose Tools > Macros > Record New Macro or choose Record icon from VBA toolbar. b. Perform the sequence of actions in Excel. c. Choose Tools > Macros > Stop Recording or choose Stop from the Visual Basic toolbar. 12.4 The two ways to initiate the Macro Recorder are: a. Select Tools > Macros > Record New Macro from the main menu. b. Press the circular Record button

from the Visual Basic toolbar.

12.5 Some of the methods used with the Selection object are: AutoFilter, Copy, Sort, etc. 12.6 Each object in Excel has its own list of event procedures. To find the event procedures for an object: select the object, then the corresponding even procedures can be found in the drop-down menu in the upper-right hand corner of the code window in the visual basic editor. 12.7 The difference between using a command button from the Control Toolbox and using a drawing object is that: Using a control requires from us to set various properties to manipulate formatting, also requires creating a name for the control and writing an event procedure. When shapes are used, one can easily add captions and format the command button. One can also assign any macro, or public procedure from any module to the command button created. 12.8 The first step is to create the new toolbar. One can do that by choosing: a. View > Toolbars > Customize from the main menu. The Customize window appears.

b. In the Customize window select the Toolbar tab and then click-on the New command button. In the New Toolbar window type the name of the toolbar created. c. In the Customize window select the Command tab to add the button with the macro assigned to it, to the new toolbar. In the Commands tab scroll down the Categories list and select Macros from the list. Select the Custom Button option. d. To add the command button to our new toolbar, just click and drag the Custom Button to the toolbar. The new button should automatically be selected from the toolbar and the Modify Selection option should become available on the Customize window. One can now select Modify Selection to specify the name of the toolbar button, edit the button icon, and assign a macro to the button action. 12.9 When you type the name of the new menu item, place a “&” sign before the letter to be used in the shortcut. This shortcut is performed by pressing ALT key and whatever letter you placed after the “&” sign. 12.10 Some of the properties associated with the scroll bar control are: LinkedCell; Max, Min, LargeChange, SmallChange, MouseIcon, Placement,, etc. Hands-on Exercises: 12.1

The following is the visual basic code of the macro recorded in this exercise:

12.2

The following is the visual basic code for the macro recorded in this exercise:

12.3

The following is the visual basic code for the macro recorded for this exercise:

12.4

The following is the visual basic code for the macro recorded in this exercise:

12.5

The following is the visual basic code for the macros recorded in this exercise:

12.6

The following is the visual basic code for the command button recorded in this exercise:

12.7

The following is the visual basic code for the macro recorded for this exercise:

The following are the results from running the macro:

12.8

The following is the visual basic code for the macro recorded for this exercise:

Using the macro the following were the results (sum, mean and standard deviation) for the series of numbers given: For {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}, sum = 55, mean = 5.05, standard deviation = 3.03. For {8, 6, 7, 5, 3, 0, 9}, sum = 38, mean = 5.43, standard deviation = 3.10. For {1176, 1234, 378, 2521, 2020, 1492, 1453, 1126}, sum = 11400, mean = 1425, standard deviation = 637.47. 12.9

The following is the visual basic code for macro recorded in this exercise:

12.10 The following is the visual basic code for macro recorded in this exercise:

12.11 The following is the visual basic code created for this macro:

12.12 The following are the visual basic codes for the command buttons created for this exercise:

12.13 a. The following is the visual basic code for this macro:

b. The following is the visual basic code for this macro:

c. The following is the visual basic code for this macro:

12.14 The following is the visual basic code for the macro recorded in this exercise:

Based on the Pareto Chart the long waiting for the food and cold environment are the main reasons for customer complaints. The following is the Pareto chart created.

12.15 The following are the properties of the scroll bar created:

12.16 a. The following is the visual basic code for the macro recorded in this exercise:

b. The following is the visual basic code created for this macro:

c. The following is the visual basic code created for this macro:

12.17 The following are the visual basic codes used to create the ZoomIn and ZoomOut macros:

The following is the new toolbar created that has two buttons and enables the user to zoom in or out:

To create the toolbar follow these steps: a. Select View > Toolbars > Customize from main menu. The Customize window appears. b. In the Customize window, click on the New command button. The New Toolbar window appears. c. In the New Toolbar window type the name of the toolbar: Zoom. d. Select the Command tab in the Customize window. Select the Macros in the Categories list. In the Commands option window that corresponds to Macros, select Custom Button. Drug the Custom Button to the toolbar.

e. Select the Custom Button in the toolbar and select Modify Selection to assign the macro ZoomIn to the button created as well as modify the name of the button. f. Add another Custom Button in the toolbar, select Modify Selection to assign the macro ZoomOut to the button created and modify the name of the button. 12.18

The following is the visual basic code for this macro:

12.19 The following is the visual basic code for the event procedure created in this exercise.

12.20

a. The following is the visual basic code created for the command button that fits a linear trend line to the data given.

b. The following is the visual basic code created for the command button that fits an exponential trend line to the data given.

c. The following is the visual basic code created for the command button that fits a power trend line to the data given.

12.21 The following are the visual basic codes corresponding to the macros recorded in this exercise:

12.22 The following are the visual basic codes for the macros recorded in this exercise.

The following shows the buttons and the toolbar created.

To create the toolbar follow these steps: a. Select View > Toolbars > Customize from main menu. The Customize window appears. b. In the Customize window, click on the New command button. The New Toolbar window appears. c. In the New Toolbar window type the name of the toolbar: Rand. Generator. d. Select the Command tab in the Customize window. Select the Macros in the Categories list. In the Commands option window that corresponds to Macros, select Custom Button. Drug the Custom Button to the toolbar. e. Select the Custom Button in the toolbar and select Modify Selection to assign the macro that generates random numbers uniformly distributed between 0 an 1 to the button created as well as modify the name of the button. g. Add a Custom Button in the toolbar. Select the Custom Button and click on the Modify Selection to assign the macro that generates random numbers uniformly distributed between 10 an 20, to the button created and modify the name of the button. h. Add a Custom Button in the toolbar. Select the Custom Button and click on select Modify Selection to assign the macro that generates random numbers

i.

j.

from standard normal distribution to the button created and modify the name of the button. Add a Custom Button in the toolbar. Select the Custom Button and click on select Modify Selection to assign the macro that generates random numbers from normal distribution with mean 10 and standard deviation 3, to the button created and modify the name of the button. Add a Custom Button in the toolbar. Select the Custom Button and click on select Modify Selection to assign the macro that generates random numbers from exponential distribution with lambda 2, to the button created and modify the name of the button.

12.23 The following are the visual basic codes for the macros recorded:

12.24 The following are the visual basic codes for the macros recorded in this exercise:

The following figure presents the buttons created and the “Trig. Functions” menu item.

To create the toolbar follow these steps: a. Select View > Toolbars > Customize from main menu. The Customize window appears. b. Select the Command tab in the Customize window. Select the New Menu in the Categories list. In the Commands option window select New Menu option. Drug the New Menu to the menu bar. c. Select the New Menu item in the menu bar and select Modify Selection to modify the name of the menu item to “Trig. Functions”. At this point do not assign a macro to this menu item. d. Select the Command tab in the Customize window. Select the Macros in the Categories list. In the Commands option window that corresponds to Macros, select Custom Button. Drug the Custom Button to the menu item “Trig. Functions”. e. Select the Custom Button in the menu bar and select Modify Selection to assign the macro that generates the sinus of an angle to the button created as well as modify the name of the button. f. Add (drug) a new Custom Button in the menu item “Trig. Functions”. Select the Custom Button and click on the Modify Selection to assign the macro that generates the cosine of an angle to the button created in the menu item, and modify the name of the button. g. Add (drug) a new Custom Button in the menu item “Trig. Functions”. Select the Custom Button and click on the Modify Selection to assign the macro that generates the tangent of an angle to the button created in the menu item, and modify the name of the button. 12.25 The following is the visual basic code for the macro recorded in this exercise:

Chapter 13 Review Questions 13.1

The purpose of activating a workbook or a worksheet is to indicate which workbook or worksheet is active. The lines of the visual basic code will be performed on the active workbook or active worksheet.

13.2

The argument used with the PasteSpecial method in order to paste only the values of a copied range is xlPasteValues.

13.3

The Visible property takes the value True or False and indicates whether or not the worksheet (along with the worksheet tab at the bottom of the screen) can be seen by the user.

13.4

To change the color of any range of cells one can use the Interior property. With this property one can use sub-properties such as ColorIndex or Color. If the subproperty ColorIndex is used, the user should identify the number signifying the special color. If the sub-property Color is used, the user should enter either a VB Constant or a specific color using RGB function. The following are examples of how to change the color of any range of cells to green: Range(“A1:B10”).Interior.ColorIndex = 43 Range(“A1:B10”).Interior.Color = vbGreen Range(“A1:B10”).Interior.Color = QBColor(2)

13.5

To format the borders of a range one can use the Borders property. The following are some of the formatting options for the borders of a range: LineStyle, Color, and Weight.

13.6

The property Font allows modifying the font of the content. Three common subproperties of property Font are: Size, FontStyle, and Italic. Range("A1").Font.Italic = True

13.7

The three most common methods that can be used to clear cells are: Clear, ClearContent, and ClearFormats. The ClearFormats method removes any formatting performed on the selected cells. ClearContents will remove all values from the specified range (without changing the formatting of the cells). The Clear method clears everything in the specified range values, formatting, formulas, comments, etc.

13.8

ActiveChart object is used to specify the chart type and the source data.

13.9

The following are four methods of the chart objects: Add, Copy, Delete and Move.

13.10 In order to apply properties and methods to a shape, it is important to name the shape. 13.11 The ScreenUpdating property helps the visual basic code run more efficiently. When ScreenUpdating property is set to False, none of the actions of the visual basic code will be reflected on the Excel screen while the macro is being run, making the code running faster. The CutCopyMode property when set to True keeps the last range which was copied highlighted until another action takes place. However, if the last action in our code is to copy and paste a range, we do not want the user to see this flashing range. To avoid this, we can simply set the CutCopyMode property to False at the end of the code. 13.12 The With construct is used to set several properties of one object in an enclosed statement. With Range("A1:A10") .Font.FontStyle = "Arial" .Font.Size = 12 .Font.Underline = True End With 13.13 The Offset property considers considers the named range to be in the 0th row and 0th column; it then offsets the range selection by a certain row count below and column count to the right of this named range. Unlike the Offset property, the Cells property considers the named range to be in the (1, 1) position. For example Range(“A1”).Offset(2, 0) and Range(“A1”).Cells(3, 1) both reference cell A2. 13.14 Columns and Rows are used to reference rows and columns in the named range. 13.15 The End property can help to find the end of row or column of any range of data. The End property can take four values: xlDown and xlUp for columns, and xlToRight and xlToLeft for rows. 13.16 The property used to enter a formula into a range is Formula. The property used to enter a formula using R1C1 notation into a range is FormulaR1C1. 13.17 The AutoFill method in VBA can be used to give the same result as copy-anddrag in Excel.

13.18 The WorksheetFunction property allows the use of Excel functions in VBA. Hands-on Exercises 13.1

The following is the visual basic code for this exercise:

13.2

The following is the visual basic code for this exercise:

13.3

The following is the visual basic code for this exercise:

13.4

The following is the visual basic code for this exercise:

13.5

The following is the visual basic code for this exercise:

13.6

The following is the visual basic code for this exercise:

13.7

The following is the visual basic code for this exercise:

13.8

The following are the visual basic codes for this exercise:

13.9

The following is the visual basic code for this exercise:

13.10 The following is the visual basic code for this exercise:

13.11 The following is the visual basic code for this exercise:

13.12 The following is the visual basic code for this exercise:

13.13 The following is the visual basic code for this exercise:

13.14 The following is the visual basic code for the Add and Calc Total buttons:

The Excel function used that shows the today’s date is: ‘=NOW()’. The following data validation allows the user to choose one of the credit cards listed:

The following data validation allows the user to choose one of the products listed:

The following figure shows how we have set the Excel spreadsheet:

13.15 The following are the visual basic codes for this exercise:

13.16 The following is the visual basic code for this exercise:

13.17 The following is the visual basic code for this exercise:

13.18 The following is the visual basic code for this exercise:

13.19 The following is the visual basic code for this exercise:

13.20 The following is the visual basic code for this exercise:

Chapter 14 Review Questions 14.1

A Message Box prints information to a small dialog box, and an Input Box prompts the user to enter information into a small dialog box. The arguments of the Message Box are: prompt, buttons, title, helpfile and context. The arguments of the Input Box are: prompt, title, default, xpos, ypos, helpfile and context. The following is an example of a Message Box: MsgBox “You are” & Age & “years old.” The following is an example of a Input Box: Age = InputBox (“Please enter your age!”)

14.2

The seven variable types are: Integer: Double: String: Boolean: Range: Worksheets: Object:

14.3

Dim Age As Integer Dim Weight As Double Dim Name As String Dim Answer As Boolean Dim CellRange As Range Dim MyWS As Worksheets Dim ExcelObject As Object

Yes, to a String variable one can assign a number variable using the CStr() conversion function. Yes, to a Integer/Double variable one can assign a string variable using the CInt()/CDbl conversion function.

14.4

Worksheet and Object variables can both be used to reference worksheets. A Worksheet variable can be assigned to an Object variable, but an Object variable cannot be assigned to a Worksheet variable.

14.5

Assuming that: the user entered 23 as the two last digits of SSN; the user entered 10 as the birthday month; and the random number generated between 1 and 11 (10*Rnd + 1) is 7.5, the answer would be: 23107.5

14.6

Len function can be used with String variable, String expression or Variant data type.

14.7

It is recommended using the CInt() or CDbl() conversion functions instead of Val() function to find the numerical value of an expression, since the Val() function will not enforce any data type on the extracted number.

14.8

The CDate() function takes as input only variables/expressions that contain a valid date in some format.

14.9

For a variable to be used only in a private procedure, should be declared using the Dim statement inside the sub procedure in which it will be used. For a variable to be used in any private procedure inside a module, should be declared using the Dim statement in the top of the module in which it will be used. For a variable to be used in a public procedure, it should be declared as a Public variable using the Public statement, in the top of any module of the VBA code.

14.10 A Variant variable assumes the data type of the last value assigned to the variable. 14.11 The Csc(X) is a derived function as Csc (X) = 1/Sin(X). 14.12 The LN(RAND()) function in Excel can be written in VBA as follows: Log(RAND()). 14.13 The ASCII numerical value of “M” is: 77. The letter for the ASCII number 100 is: “d”. 14.14 The following is the visual basic code:

14.15 The following is the visual basic code:

Hands-on Exercise 14.1

The following is the visual basic code for this problem:

14.2

The following is the visual basic code for this problem:

The length of Double data type is always 8, as the number of bytes required to store a double is 8 byte. 14.3

The following is the visual basic code for this exercise:

14.4

The following is the visual basic code for this exercise:

14.5

The following is the visual basic code for this exercise:

14.6

The following is the visual basic code for this exercise:

14.7

The following is the visual basic code for this exercise:

14.8

The following are the public variables used in the visual basic code:

The following is the visual basic code for the Reset button:

The following is the visual basic code for the Run button:

The following are the results from the Run sub-procedure:

14.9

The following is the visual basic code for this exercise:

14.10 The following is the visual basic code for this problem:

14.11 The following is the visual basic code for this exercise:

14.12 The following is the visual basic code for this exercise:

14.13 The following is the visual basic code for this exercise:

14.14 The following is the visual basic code for this exercise:

14.15 The following is the visual basic code for this exercise:

14.16 The following is the visual basic code for this exercise:

14.17 The following is the visual basic code for this exercise:

14.18 The following is the visual basic code for this exercise:

14.19 The following is the visual basic code for this exercise:

14.20 The following is the visual basic code for this exercise:

14.21 The following is the visual basic code for this exercise:

14.22 The following visual basic code calculates the Euclidean distance between two cities.

The following visual basic code compares the distance between cities:

The following visual basic code calculates the horizontal distance between two cities:

The following visual basic code calculates the vertical distance between two cities:

14.23 The following is the visual basic code for this exercise:

14.24 The following visual basic code adds a new company to the list of companies.

The following visual basic code adds new payments.

The following visual basic code calculates the total monthly payments.

14.25 The following is the visual basic code for this exercise. a.

b.

c.

14.26 The following is the visual basic code for this exercise.

14.27 The following is the visual basic code for this exercise:

14.28 The following is the visual basic code for this exercise:

14.29 The following is the visual basic code for this exercise:

14.30 The following is the visual basic code for this exercise:

Chapter 15 Review Questions 15.1

A sub procedure cannot be called from inside itself.

15.2

Functions and sub procedures are very similar. They are both used to perform a set of actions. However, the advantage of using functions is to return a value.

15.3

The default method of passing variables in sub procedures is by reference.

15.4

To pass a variable by value, one should insert the variable name as an argument/ parameter of the sub when it is called; and the variable name used as an argument when the sub procedure is called and the variable name used in the sub procedure statement should be the same.

15.5

The following is the program structure for this problem: Public CompName As String, StatmTitle As String, Date As String Public MonUnit As String Sub AnnualReport() Call Heading(CompName, StatmTitle, Date, MonUnit) Call BalanceSheet(CompName, Date) Call IncomeStatement(CompName, Date) Call StatementOfRetainedEarnings(CompName, Date) Call StatementOfCashFlow(CompName, Date) End Sub Sub Heading(CompName, StatmTitle, Date, MonUnit) ‘Create heading with the company name, statement title, date and unit of ‘monetary measure End Sub Sub Graph() ‘Create graph End Sub Sub BalanceSheet(CompName, Date) Call Heading(CompName, StatmTitle, Date, MonUnit) Call Graph ‘Create the balance sheet End Sub Sub StatementOfRetainedEarnings (CompName, Date) Call Heading(CompName, StatmTitle, Date, MonUnit) ‘Create the statement of retained earnings End Sub Sub IncomeStatement(CompName, Date) Call Heading(CompName, StatmTitle, Date, MonUnit) ‘Create the income statement

End Sub Sub StatementOfCashFlow (CompName, Date) Call Heading(CompName, StatmTitle, Date, MonUnit) Call Graph ‘Create the cash flow statement End Sub Hands-on Questions 15.1

The following is the visual basic code for this exercise:

15.2

The following is the visual basic code for this exercise:

15.3

The following is the visual basic code for this exercise:

15.4

The following is the visual basic code for this exercise:

15.5

The following is the visual basic code for this exercise:

15.6

The following is the visual basic code for this exercise:

15.7

The following is the visual basic code for this exercise:

15.8

The following is the visual basic code for this exercise:

15.9

The following is the visual basic code for this exercise:

15.10 The following is the visual basic code for this exercise:

15.11 The following is the visual basic code for this exercise:

15.12 The following is the visual basic code for this exercise:

15.13 The following is the visual basic code for this exercise:

15.14 The following is the visual basic code for this exercise:

15.15 The following is the visual basic code for this exercise:

Chapter 16 Review Questions 16.1

The difference between Do … Loop While and Do While … Loop is that the first loop performs a set of actions and then repeats the action if a condition is met. In the second loop however, the condition is checked at first. If the condition is true the loop repeats the action.

16.2

The following visual basic code counts down an integer number entered by the user until it reaches 1. We have used the Do While and Do Until loops to build the countdown function. The differences between the two loops are: a. The Do While loop will count down to 1. In other words it will present number 1 too. The Do Until loop will countdown to 2, it will not present number 1. For example, if the user entered 5 as the number to start the countdown, the Do While loop will present the following series of numbers: 5, 4, 3, 2, and 1. The Do Until loop would present: 5, 4, 3, and 2. b. If the user entered a number less than 1, the Do While loop would not be executed as the condition i >= 1 is not met. On the other side the Do Until will enter an infinite loop, as the condition i = 1 will never be met.

16.3

The function used with an If, Then statement to check if a variable value is a number is IsNumeric().

16.4

If the variable to which the result of the InputBox is assigned is empty, that is equal to “”, then we know that the “Cancel” button has been pressed.

16.5

To check if multiple conditions have all been met we use the AND logical check.

16.6

One can also place a condition on a range of values using the Is statement. This may be more efficient than repeating the Select Case object name for each Case.

16.7

One can use the Exit Sub statement in a For, Next Loop if s/he wants to exit the sub procedure. One can use the Exit For statement to exit the For, Next Loop.

16.8

The following visual basic code asks the user to enter a letter and then uses Select, Case to identify the position of this letter on the first name. For this example we consider the first name to be “John”.

16.9

The following is the visual basic code for the Countdown function.

16.10 The following message box is the output of the visual basic program.

Hands-on Exercises

16.1

The following is the visual basic code for this exercise:

16.2

The following is the visual basic code for this exercise:

16.3

The following is the visual basic code for this exercise:

16.4

The following is the visual basic code for this exercise:

16.5

The following is the visual basic code for this exercise:

16.6

The following is the visual basic code for this exercise:

16.7

The following is the visual basic code for this exercise:

16.8

The following is the visual basic code for this exercise:

16.9

The following is the visual basic code for this exercise:

16.10 The following is the visual basic code for this exercise:

16.11 The following is the visual basic code for this exercise:

16.12 The following is the visual basic code for this exercise:

16.13 We created the following tables of data in an Excel spreadsheet:

The following is the visual basic code for this exercise:

16.14 The set-up of this game in Excel is as follows:

The following is the visual basic code for this exercise:

16.15 The following is the visual basic code for this exercise:

16.16 The following is the visual basic code for this exercise:

16.17 The following is the visual basic code for this exercise:

16.18 The following is the visual basic code for this exercise:

16.19 The following is the visual basic code for this exercise:

16.20 The following is the visual basic code for this exercise: a.

b.

c.

Chapter 17 Review Questions 17. 1

Assigning entry i to entry j of the same array means that entry i and j have the same value. In the example given, the values stored in the 4th and 10th position of the array List() are the same.

17.2

To define an array of variables as when defining a variable, one can use the Dim, Private, or Public variable declarations. However, different from defining a variable, when defining an array the array’s name is followed by with a set of parenthesis. If the user knows the size of the array, the corresponding value goes in the parenthesis.

17.3

Arrays enable the user to store, retrieve and manipulate the data. In particular, arrays are efficient tools for programming systems with multiple series of data. A multi-dimensional array can be defined for up to 60 dimensions in VBA.

17.4

When using Option Base 0, the last index of an array is N-1. When using option Base 1, the last index of an array is N.

17.5

To declare an array of variable size, you could write the following: Dim N As Integer, MyArray() As Integer N = InputBox(“Enter size of array.”) ReDim MyArray(N)

17.6

Yes, one can re-dimension an array without loosing the data that is already in the array. The following declaration is used: ReDim Preserve data(1 to size + size2). In this declaration the statement ReDim enables re-dimensioning the array to size + size2. The statement Preserve enables preserving the values that are already in the array.

17.7

To find the length of a string expression or variable value one can use the function Len(expression or variable). The function Len() can be used with any variable data type, however if the data type of a variable is String or Variant, this function gives the total number of characters in the variable. However, if the variable data type is integer or double, the Len() function returns the number of bytes required to store an integer or a double variable.

17.8

Yes, arrays can be passed to function procedures.

17.9

To format information in a message box one can use the following function: Format(Expression, Format)

17.10 The Count property can be applied to the Columns property of a range to count the number of columns. The following example shows how to count the number of columns in the range: Range(“A1”,”D1”): n = Range("A1", "D1").Columns.Count

Hands-on Exercises 17.1

The following is the visual basic code for this exercise:

17.2

The output of the program is 7. The program copies the integer part of the data presented in the spreadsheet to an array and then presents the value of the fifth element of the array.

17.3

The following is the visual basic code for this exercise:

17.4

The following is the visual basic code for this exercise:

17.5

The following is the visual basic code for this exercise:

17.6

The following is the visual basic code for this exercise:

17.7

The following is the visual basic code for this exercise:

17.8

The following is the visual basic code for this exercise:

17.9

The following is the visual basic code for this exercise:

17.10 The following is the visual basic code for this exercise:

17.11 The following is the visual basic code for this exercise:

17.12 The following is the visual basic code for this exercise:

17.13 The following is the visual basic code for this exercise:

17.14 The following is the visual basic code for this exercise:

17.15 The following is the visual basic code for this exercise:

17.16 The visual code for the Phonebook application given in the textbook has been modified. The modifications are circled in red in the figures below.

The following is a new sub procedure created for part (c) of this exercise.

17.17 The following is the visual basic code for this exercise. In building the code we assume that the distance from city i to j is different than the distance from city j to i.

17.18 The following is the visual basic code for this exercise:

17.19 The following is the visual basic code for this exercise:

17.20 The following is the visual basic code for this exercise:

Chapter 18 Review Questions 18.1

The following are five User Form controls: labels, textboxes, command buttons, check boxes, and combo boxes.

18.2

The purpose of using a frame control is to group similar items together. When frames are applied to option buttons, this makes the option buttons mutually exclusive.

18.3

Given a date by the user, to find the corresponding day, we would use the Day() function in Excel. Day(txtDate.Value)

18.4

The RefEdit control returns a Range.

18.5

The values entered in a textbox should be string or numbers.

18.6

A property of the user form is ShowModal. When this property is set to False, the form becomes modeless, or a floating form.

18.7

A multipage control is the collection of individual form objects, or pages. Each page can have a different layout and is treated as a separate form.

18.8

You can attach a code to a toggle button.

18.9

The default event of a scroll bar is Change.

18.10 The default event of a textbox is Change. 18.11 In order to check which option button in a frame has been selected follow these steps: give a name to each option button and then check the corresponding value property. If the value property is true, the option button has been selected. 18.12 The check boxes placed in the same frame are not mutually exclusive. 18.13 Basic error checking done with the forms are: check if the user entered values in all the controls and check the type of the data entered. 18.14 To ensure that the program will not continue when the Cancel button is pressed, we associated with the Cancel button a VB code to unload the form (Unload Me). 18.15 A worksheet is protected setting the Locked property of a range of cells to True. To enter values in the protected range, one should set the Locked property to False.

Hands-on Exercises 18.1

The following visual basic code enables modifications of the scroll bar value when a new value is typed in the textbox and vise versa.

18.2 18.3

Put the solution here…………….. The following figure presents the application build for this exercise:

The following sub procedure initializes the form. This sub procedure sets the RowSource property of the combo boxes and list box, as well the MultiSelect property of the list box.

The following is the code associated with the apply command button.

18.4

The following figure presents the application build for this exercise:

The following is the code for the Initialization procedure:

The following is the visual basic code for this exercise:

18.5

The following figure presents the application build for this exercise:

The following is the visual basic code for this exercise:

18.6

The following figure presents how we set up the excel spreadsheet for this exercise:

The following figure presents the user interface build for this exercise.

The following is the visual basic code for the “BOM” frame.

The following is the visual basic code for the “Components of the final product” frame.

18.7

The following figure presents the “Data Analysis” form created for this exercise:

The following is the visual basic code for this exercise:

18.8

The following figure presents the report created for this exercise:

The following figure presents the Registration form build for this exercise:

The following is the visual basic code for this exercise:

18.9

The following is the “Apartment Guide” form build for this exercise:

The following is the corresponding visual basic code:

18.10 The following presents the setup of the excel spreadsheet for this exercise:

The following presents the forms created for this exercise:

The following is the visual basic code for the “Employee Information” form:

The following is the visual basic code for the “Modify Employee Information” form:

18.11 The following is the “Loan Manager” frame:

The following is the code associated with the submit command button:

The following is the “Loan Manager” form:

The following is the corresponding visual basic code:

18.12 The following is the “Portfolio Selection” form created for this exercise:

The following is the corresponding visual basic code:

18.13 The following is the “Text Search” form.

The following is the visual basic code:

18.14 The following is the “Airline Travel” form created for this exercise.

We set the spreadsheet with the results from the search as follows:

The following is the visual basic code for this exercise:

18.15 The following is the “Quality Control” form build for this exercise:

The following is the visual basic code for this exercise:

18.16 The following is the “Random Generator” form build for this problem:

The following is the corresponding visual basic code:

18.17 The following figure shows the data used for this exercise.

The following is the “Welcome” form:

The following is the visual basic code for this form:

The following is the “Search” form:

The following is the visual basic code for this form:

The following is the “Add a New Item” form:

The following is the corresponding visual basic code:

The following is the “Delete an Item” form:

The following is the corresponding visual basic code:

18.18 The following is the “Statistical Analysis” form:

The following is the corresponding visual basic code:

18.19 The following figure presents how we organized the data we used for this exercise:

The following is the “Search the University Database” form:

The following is the corresponding visual basic code:

18.20 The following figure presents how we organized the data used for this exercise in an excel spreadsheet:

The following is the “Member Information” form:

The following is the corresponding visual basic code:

Chapter 19 Review Questions 19.1

Before using Solver functions in a program, one must reference the Solver library in the VBE.

19.2

The SolverSolve function returns an integer value classifying the result. Therefore, the variable Result will take one of the following values: 0, 1, 2, 4 or 5. Note that, 0, 1 or 2 signify a successful run in which a solution has been found. The value 4 implies that there was no convergence, and the value 5 implies that no feasible solution could be found.

19.3

Five main Solver functions are: SolverOK, SolverAdd, SolverChange, SolverDelete and SolverReset. SolverOK, SolverAdd are used to input the Solver parameters in VBA. The SolverChange and SolverDelete functions are used to modify or delete constraints. The SolverReset function is used to reset all Solver parameters.

19.4

The format of SolverOK function is as follows: SolverOK(SetCell, MaxMinVal, ValueOf, ByChange). In this function, the SetCell argument is used to specify the range of the objective function. In order to increase the efficiency of the code, and enable the user manipulate the objective function without changing the code, one could use range names instead of rage variables to set-up the SetCell argument.

19.5

It is a good idea to use the SolverReset function before any of the Solver functions are used.

19.6

The following is the format of SolverOK function: SolverOK(SetCell, MaxMinVal, ValueOf, ByChange). The MaxMinVal argument specifies if the objective function will be maximized, minimized, or solved to a particular value. Setting the value of this argument to 3 solves the problem to a particular value. Next, the argument ValueOf is used to set the value.

19.7

SolverFinish KeepFinal:=1, ReportArray:=Array(1,2)

19.8

The KeepFinal argument is used with SolverFinish function. KeepFinal argument takes the value 1 if you want to keep the Solver solution and the value 2 if you want the previous values to be kept.

19.9

The ShowRef is an argument of SolverSolve function. This argument is used when the StepThru option is set. One can pass Solver a macro to run at each pause by using the ShowRef argument of the SolverSolve function.

19.10 Yes arrays can be set to Control data type. Suppose UserControl(1 to n) is an array of controls and cmbUser1,…, cmbUsern are n different frames (controls). We initialize this array as follows:

Set UserControl(1) = cmbUser … Set UserControl(n) = cmbUsern

Hands-on Exercises 19.1

The optional code to be added to the frmParam form cmdOK_click procedure in order to hide the rows in the spreadsheet based on the number of decision variables chosen by the user, is: Range(DVRg.Offset(NumDV, 0), DVRg.Offset(4, 0)).Rows.Hidden = True

19.2

The following are the decision variables for this problem: X1 X2 X3 X4 X5 X6 X7 X8 X9

amount of cotton produced in the interval 4 to 12 amount of cotton produced in the interval 13 to 40 amount of cotton produced in the interval 41 to 100 amount of peanuts produced in the interval 4 to 12 amount of peanuts produced in the interval 13 to 40 amount of peanuts produced in the interval 41 to 100 amount of soybeans produced in the interval 4 to 12 amount of soybeans produced in the interval 13 to 40 amount of soybeans produced in the interval 41 to 100

Y1

is a binary variable that takes value 1 if the amount of cotton produced is in the interval 4 to 12, and 0 o/w is a binary variable that takes value 1 if the amount of cotton produced is in the interval 13 to 40, and 0 o/w is a binary variable that takes value 1 if the amount of cotton produced is in the interval 41 to 100, and 0 o/w is a binary variable that takes value 1 if the amount of peanuts produced is in the interval 4 to 12, and 0 o/w is a binary variable that takes value 1 if the amount of peanuts produced is in the interval 13 to 40, and 0 o/w is a binary variable that takes value 1 if the amount of peanuts produced is in the interval 41 to 100, and 0 o/w is a binary variable that takes value 1 if the amount of soybeans produced is in the interval 4 to 12, and 0 o/w is a binary variable that takes value 1 if the amount of soybeans produced is in the interval 13 to 40, and 0 o/w is a binary variable that takes value 1 if the amount of soybeans produced is in the interval 41 to 100, and 0 o/w

Y2 Y3 Y4 Y5 Y6 Y7 Y8 Y9

The objective is to maximize profits:

Maximize: 33000*(Y1 + Y2 + Y3) + 41000*(Y4 + Y5 + Y6) + 37500*(Y7 + Y8 + Y9) – (1250*X1 + 110*X2 + 850*X3 + 1750*X4 + 1500*X5 + 950*X6 + 1600*X7 + 1450*X8 + 1100*X9) The constraints are: X1 + X2 + X3 + X4 + X5 + X6 + X7 + X8 + X9 <= 200 Y1 + Y2 + Y3 <= 1 Y4 + Y5 + Y6 <= 1 Y7 + Y8 + Y9 <= 1 X1 <= 12* Y1 X2 <= 40* Y2 X3 <= 100* Y3 X4 <= 12* Y4 X5 <= 40* Y5 X6 <= 100* Y6 X7 <= 12* Y7 X8 <= 40* Y8 X9 <= 100* Y9 All X are positive. Y variables are binary. Excel spreadsheet is set-up as follows:

The formulas used are:

The visual basic code is:

19.3

The decision variables for this exercise are: PA takes value 1 if paint A is chosen as primer and 0 otherwise PB takes value 1 if paint B is chosen as primer and 0 otherwise PC takes value 1 if paint C is chosen as primer and 0 otherwise BA BB BC

takes value 1 if paint A is chosen as base and 0 otherwise takes value 1 if paint B is chosen as base and 0 otherwise takes value 1 if paint C is chosen as base and 0 otherwise

CA CB CC

takes value 1 if paint A is chosen as the clear coat and 0 otherwise takes value 1 if paint B is chosen as the clear coat and 0 otherwise takes value 1 if paint C is chosen as the clear coat and 0 otherwise

The objective is to minimize the total cost of painting. Therefore, it is equal to: =SUMPRODUCT(F2:F4,B2:B4) + SUMPRODUCT(F5:F7,B7:B9) + SUMPRODUCT(F8:F10,B12:B14) The constraints for this problem are: C1: SUM(F2:F4) = 1 C2: SUM(F5:F7) = 1 C3: SUM(F8:F10) = 1 C4: SUMPRODUCT(F2:F4,C2:C4) + SUMPRODUCT(C7:C9,F5:F7) + SUMPRODUCT(C12:C14,F8:F10) < = 12 We have set-up the Excel spreadsheet as follows:

The following is the corresponding visual basic code:

19.4

Since the product is ordered in batches of five, it is more convenient to transform the unit costs and profits in term of batch costs and profits. Thus, the following is the data given in this problem: Purchase Cost per Batch Product Men's Bottoms Men's Tops Women's Bottoms Women's Tops

<= 4 $85 $70 $105 $90

Quantity 5 to 8 $70 $60 $90 $75

>8 $60 $50 $75 $65

Sell Price $140 $190 $170 $215

Furthermore, we update the table above to present the profit per batch. Profit per Batch Product Men's Bottoms Men's Tops Women's Bottoms Women's Tops

<= 4 $55 $120 $65 $125

Quantity 5 to 8 >8 $70 $80 $130 $140 $80 $95 $140 $150

Demand D1 D2 D3 D4

The decision variables are: X1 number of batches of men’s bottoms purchased in the interval 0 to 4 X2 number of batches of men’s bottoms purchased in the interval 5 to 8 X3 number of batches of men’s bottoms purchased in the interval 8 to infinity X4 number of batches of men’s tops purchased in the interval 0 to 4 X5 number of batches of men’s tops purchased in the interval 5 to 8 X6 number of batches of men’s tops purchased in the interval 8 to infinity X7 number of batches of women’s bottoms purchased in the interval 0 to 4

X8 X9 X10 X11 X12

number of batches of women’s bottoms purchased in the interval 5 to 8 number of batches of women’s bottoms purchased in the interval 8 to inf. number of batches of women’s tops purchased in the interval 0 to 4 number of batches of women’s tops purchased in the interval 5 to 8 number of batches of women’s tops purchased in the interval 8 to inf.

Y1 Y2 Y3

takes value 1 if 0 to 4 batches of men’s bottoms were purchased, 0 o/w takes value 1 if 5 to 8 batches of men’s bottoms were purchased, 0 o/w takes value 1 if more than 8 batches of men’s bottoms were purchased, 0 o/w Y4 takes value1 if 0 to 4 batches of men’s tops were purchased, 0 o/w Y5 takes value1 if 5 to 8 batches of men’s tops were purchased, 0 o/w Y6 takes value1 if more than 8 batches of men’s tops were purchased, 0 o/w Y7 takes value1 if 0 to 4 batches of women’s bottoms were purchased, 0 o/w Y8 takes value1 if 5 to 8 batches of women’s bottoms were purchased, 0 o/w Y9 takes value1 if more than 8 batches of women’s bottoms were purchased, 0 o/w Y10 takes value1 if 0 to 4 batches of women’s tops were purchased, 0 o/w Y11 takes value1 if 5 to 8 batches of women’s tops were purchased, 0 o/w Y12 takes value1 if more than 8 batches of women’s tops were purchased, 0 o/w The objective is to maximize profits: Maximize: 55*X1 + 70*X2 + 80*X3 + 120*X4 + 130*X5 + 140*X6 + 65*X7 + 80*X8 + 95*X9 + 125*X10 + 140*X11 + 150*X12 - 6*( X1 + X2 + X3 – D1 + X4 + X5 + X6 – D2 + X7 + X8 + X9 – D3 + X10 + X11 + X12 – D4) The constraints are: X1 + X2 + X3 >= D1 X4 + X5 + X6 >= D2 X7 + X8 + X9 >= D3 X10 + X11 + X12 >= D4 Y1 + Y2 + Y3 = 1 Y4 + Y5 + Y6 = 1 Y7 + Y8 + Y9 = 1 Y10 + Y11 + Y12 = 1 X1 <= 4* Y1 X2 <= 8* Y2 X3 <= 100* Y3 X4 <= 4* Y4 X5 <= 8* Y5 X6 <= 100* Y6 X7 <= 4* Y7 X8 <= 8* Y8 X9 <= 100* Y9 X10 <= 4* Y10

demand should be satisfied, however, since the products are bought in batches, there might be some inventories there cannot be purchases from more than one interval

X11 <= 8* Y11 X12 <= 100* Y12 X2 >= 5* Y2 X3 >= 9* Y3 X5 >= 5* Y5 X6 >= 9* Y6 X8 >= 5* Y8 X9 >= 9* Y9 X11 >= 5* Y11 X12 >= 9* Y12 All X are positive integers. Y variables are binary. Excel spreadsheet is set-up as follows:

The following are the formulas used:

The objective function is: =F3*(C3*C22 + D3*C23+E3*C24) + F4*(C4*C25+D4*C26+E4*C27) + F5*(C5*C28+D5*C29+E5*C30) + F6*(C6*C31+D6*C32+E6*C33) + 6*(F3SUM(C10:C12) + F4-SUM(C13:C15) + F5-SUM(C16:C18) + F6-SUM(C19:C21)) The following is the visual basic code:

19.5

The decision variables for this exercise are: Xij amount shipped from manufacturing facility i to distribution center j for i = 1, 2, 3 and j = 1, 2, 3, and 4 Constraints C1 to C4 state that demand in each distribution center should be satisfy. Constraints C5 to C7 state that the amount shipped to distribution centers cannot be more than production capacity at the facilities. The objective is to minimize costs. The excel spreadsheet is set-up as follows:

The following are the formulas used to calculate costs and set-up constraints:

The visual basic code for this exercise is:

19.6

The decision variables for this problem are: X1 X2 X3

amount of product 1 produced (cell C9) amount of product 2 produced (cell D9) amount of product 3 produced (cell E9)

The objective is to maximize profits (cell F8) The constraints limit the numbers of hours scheduled for each worker to the amount of hours available (cells F4:H7). We set-up the excel spreadsheet as follows:

The following presents the formulas used:

The following is the corresponding visual basic code:

19.7

The decision variables for this problem are: Xij amount of product i produced in machine j The objective is to minimize production costs. The constraints are: Demand constraints imply that demand for the products should be satisfied Capacity constraints imply that amount produced should not be bigger than production capacity The excel spreadsheet is set-up as follows:

The formulas used are:

The visual basic code is:

19.8

The decision variables for this problem are: Xij amount of coffee shipped from supplier i in week j The objective is to minimize the total costs. The constraints are: Demand constraints that imply demand for coffee should be satisfied Capacity constraints that imply: suppliers cannot ship more than what they have in their inventory.

The excel spreadsheet is set-up as follows:

The formulas used are:

The visual basic code is:

19.9

The decision variables for this problem are:

X11 X12 X21 X22 X31 X32 X41 X42

the number of officers that start working on the 1-st shift and work 12 hours the number of officers that start working on the 1-st shift and work 18 hours the number of officers that start working on the 2-nd shift and work 12 hours the number of officers that start working on the 2-nd shift and work 18 hours the number of officers that start working on the 3-rd shift and work 12 hours the number of officers that start working on the 3-rd shift and work 18 hours the number of officers that start working on the 4-th shift and work 12 hours the number of officers that start working on the 4-th shift and work 18 hours

The objective is to minimize the total costs: Min: 60*(X11 + X21 + X31 + X41) + 102*(X12 + X22 + X32 + X42)

The following constraints limit the number of employees that start working in each shift to the specific requirements of the department: Shift 1: Shift 2: Shift 3: Shift 4:

X11 + X12 + X32 + X41 + X42 X11 + X12 + X21 + X22 + X42 X12 + X21 + X22 + X31 + X32 X22 + X31 + X32 + X41 + X42

>= 12 >= 8 >= 6 >= 15

Finally, all X are integers. The excel spreadsheet is set-up as follows:

Note that: the Linked cell of the textboxes are set as follows: the Linked cell for the textboxes that present the number of officers required are set equal to cells G15 to G18. The Linked cell for the hourly rates is set equal to cells B2 and B3 respectively. The formulas used are:

The visual basic code is:

19.10 The decision variables for this problem are: X Y

amount of money invested in stocks amount of money invested in loans

The objective is to maximize the return from this investment: Max: 0.15X + 0.2Y The constraints are: X >= 500 the amount invested in stock should be at least 0.25*$2000 Y > = 350 the amount invested in loans should be at least $350 X + Y <= 2000 the total amount invested is limited by the amount of money owned The excel spreadsheet is set-up as follows:

Note that: the linked cell for the textbox that presents the amount of money available is cell J11. The linked cell for the scrollbar that presents the amount invested in loans is cell D10. The linked cell for the scrollbar that presents the % invested in stocks is cell D13. The formulas used are:

The visual basic code for this exercise is:

19.11 The decision variables for this exercise are: X1 amount produced in period 1 X2 amount produced in period 2 X3 amount produced in period 3 I1 amount in inventory in the end of period 1 (anything over 0.5*X1) I2 amount in inventory in the end of period 2 (anything over 0.5*X2) I3 amount in inventory in the end of period 3 (anything over 0.5*X3) The objective is to minimize costs: Minimize: 10*X1 + 11*X2 + 12*X3 + 2*(I1 + I2 + I3 + 0.5*(X1 + X2 + X3)) The constraints imply that the requirements in each period should be met: Period 1: 5 + 0.5*X1 = 30 + I1 Period 2: I1 + 0.5*(X1 + X2) = 15 + I2 Period 3: I2 + 0.5*(X2 + X3) = 25 + I3 All variables are non-negative. The excel spreadsheet is set-up as follows:

The formulas used are:

The visual basic code is:

The user form for this problem is presented in the figure below:

The corresponding visual basic code is:

19.12 The decision variables for this problem are: X11 amount of medicine purchased from company 1 in the 1-st month X12 amount of medicine purchased from company 1 in the 2-nd month X21 amount of medicine purchased from company 2 in the 1-st month

X22

amount of medicine purchased from company 2 in the 2-nd month

The objective is to minimize the total cost of purchasing the medicine: Minimize: 700*X11 + 650*X12 + 670*X21 + 660*X22 The constraints are: C1: X11 + X21 = 3 C2: X12 + X22 = 4 X11 <= 5; X12 <= 5; X21 <= 5; X22 <= 5 All variables are non-negative. The excel spreadsheet is set-up as follows:

The formulas used are:

The following is the user form for this exercise:

The visual basic code for this exercise is:

19.13 The decision variables for this problem are: X1 amount of product 1 produced X2 amount of product 2 produced Y1 a binary variable that takes the value 1 if product 1 is produced, and 0 o/w Y2 a binary variable that takes the value 1 if product 2 is produced, and 0 o/w The objective is to maximize profits: Maximize: 3*X1 + 7*X2 – 10*Y1 – 20*Y2 The constraints are: C1: 3*X1 + 6*X2 <= 120 raw material constraint C2: X1 <= M*Y1 C3: X2 <= M*Y2 X1 and X2 take non-negative values Y1 and Y2 are binary variables We set the excel spreadsheet as follows:

The formulas used are:

The following is the user form for this exercise:

The visual basic code for this exercise is:

19.14 The decision variables for this problem are: X1 X2 X3 X4

amount produced in machine 1 amount produced in machine 2 amount produced in machine 3 amount produced in machine 4

Y1 Y2 Y3 Y4

takes the value 1 if machine 1 is set-up for production, and 0 o/w takes the value 1 if machine 2 is set-up for production, and 0 o/w takes the value 1 if machine 3 is set-up for production, and 0 o/w takes the value 1 if machine 4 is set-up for production, and 0 o/w

The objective is to minimize costs: Minimize: 19*X1 + 23*X2 + 17*X3 + 27*X4 + 1000*Y1 + 900*Y2 + 800*Y3 + 700*Y4 The constraints are: X1 + X2 + X3 + X4 = 2000 X1 <= 2000*Y1 X2 <= 2000*Y2 X3 <= 2000*Y3 X4 <= 2000*Y4 0 <= X1 <= 900 0 <= X2 <= 1000 0 <= X3 <= 1100 0 <= X4 <= 1200

demand should be satisfied

machine 1 production capacity constraint machine 2 production capacity constraint machine 3 production capacity constraint machine 4 production capacity constraint

Excel spreadsheet is set-up as follows:

The formulas used are:

The user form for this exercise is:

The visual basic code is:

19.15 A problem formulation for this exercise can be found in the solutions for chapter 8, hands-on exercise 8.8. Excel spreadsheet for this exercise is set-up as follows:

The user form for this exercise is:

The corresponding visual basic code is:

19.16 Details about problem formulation can be found to the solution of hands-on exercise 8.5. The following presents the excel spreadsheet for this exercise:

The following is the corresponding user form:

The following is the visual basic code:

19.17 Problem formulation can be found in the solutions for chapter 8 (hands-on exercise 8.13). Excel spreadsheet is set-up as follows:

The following is the user form:

The following is the visual basic code:

19.18

Problem formulation can be found in the solutions for chapter 8 (hands-on exercise 8.17). Excel spreadsheet is set-up as follows:

The formulas used are:

The following is the corresponding user form:

The visual basic code is:

19.19 Problem formulation can be found in the solutions for chapter 8 (hands-on exercise 8.19). Excel spreadsheet is set-up as follows:

The formulas used are:

The visual basic code is:

19.20 Problem formulation can be found in the solutions for chapter 8 (hands-on exercise 8.21). Excel spreadsheet is set-up as follows:

The formulas used are:

The following is the visual basic code:

Chapter 20 Review Questions 20.1

To generate random numbers from Exponential distribution we use the following formula: ‘=(-1/Lambda)*ln(RAND())’

20.2

The following distributions have inverse functions: Beta (BetaInv); Chi (ChiInv); F (FInv); Fisher (FisherInv); Gamma (GammaInv); Lognormal (LogInv); Normal (NormInv); Standard Normal (NormSInv); T (TInv).

20.3

If using ‘Application.WorksheetFunction.NormInv()’ function, in order to specify a random probability value one can use the RND function as follows: ‘Application.WorksheetFunction.NormInv(RND, mean, stdev)’

20.4

The code used to activate the screen updating functionality in VBA is ScreenUpdating = True The code used to deactivate the screen updating functionality in VBA is ScreenUpdating = False The following is an example where it is useful to use the ScreenUpdating property. For i = 1 to 100 ScreenUpdating = False Range(“A1”).Offset(i,0) = (-1/Lambda)*ln(RAND()) ScreenUpdating = True Next i

20.5

Application.Wait(Now + TimeValue(“0:0:10”))

20.6

The ScreenUpdating is useful in simulation as it reduces screen flickering while the program runs and increases the efficiency (due to decreased running time) when the screen is not updated after every action in the code. The Wait method is useful in creating some animation of the simulation.

20.7

Animation is an important part of a simulation program as makes the program interactive. Animation makes it easy to the user to understand how the program runs.

20.8

No, it is not possible to “pause” the VBA code while it is running. If you wanted to incorporate a feature like this, you would have to create a user interface which allows the user to “play” the simulation one loop at a time.

20.9

Results for each run should be recorded during the simulation loop.

20.10 The code use to create a histogram in VBA is: Application.Run "ATPVBAEN.XLA!Histogram", BinRange, Labels, Pareto, Cumulative, Chart

InputRange,

Hands-on Exercises 20.1

The following is the visual basic code for this exercise:

20.2

Excel spreadsheet is set-up as follows:

The visual basic code for this exercise is:

OutputRange,

20.3

The following is the visual basic code for this exercise:

20.4

In this exercise we use the 4-periods moving average method to forecast next period’s sales. The following presents the excel spreadsheet:

The following is the corresponding visual basic code:

20.5

The following is the visual basic code for this exercise:

20.6

The following is the visual basic code for this exercise:

20.7

In tossing the dice 5 times, the total number of outcomes is 65. The total number of 4-straights is 62. The following are all the cases when we get a four straight: 1,2,3,4,X; X can take values 1 to 6 2,3,4,5,X; or 3,4,5,6,X; or X,1,2,3,4; or X,2,3,4,5; or X, 3,4,5,6; Therefore, the proportion of time we get a 4-straight is 1/63 = 0.00463.

The total number of straight-3-of-a-kind is 3*63. The following are all the cases when we get straight-3-of-a-kind: 1,1,1,X,Y; X,Y, can take values 1 to 6 2,2,2,X,Y; or 3,3,3,X,Y; or 4,4,4,X,Y; or 5,5,5,X,Y; or 6,6,6,X,Y; or X,1,1,1,Y; or X,2,2,2,Y; or X,3,3,3,Y; or X,4,4,4,Y; or X,5,5,5,Y; or X,6,6,6,Y; or X,Y,1,1,1; or X,Y,2,2,2; or X,Y,3,3,3; or X,Y,4,4,4; or X,Y,5,5,5; or X,Y,6,6,6; Therefore, the proportion of time we get a straight-3-of-a-kind is 3/62 = 0.08333. The following is the visual basic code for 4-straight game:

This is the visual basic code for 3-of-a-kind game:

20.8

The following is the user form created for this exercise:

The following is the corresponding visual basic code:

20.9

Excel spreadsheet is set-up as follows:

The visual basic code for this exercise is:

20.10 The simulation model built for this exercise consists of these steps: (1) generate 1000 scenarios. For each scenario, randomly generate the number of attendants using normal distribution. (2) Set the number of the rooms reserved to 5000, and calculated the expenses for each scenario. (3) Calculate the average expenses (over the 1000 scenarios). (4) Use goalseek function in excel to find the number of rooms to be reserved so that the average expenses would be minimized. We set-up the excel spreadsheet as follows:

The corresponding user form is:

The visual basic code is:

20.11 Note that for John and Smith to meet in the final game the following should happen: (a) John’s position 1 or 2; Smith’s position is 7 or 8; and they both win the games in the first and second round. (b) John’s position 7 or 8; Smith’s position is 1 or 2; and they both win the games in the first and second round. The following is the visual basic code for this exercise:

20.12 Excel spreadsheet is set-up as follows:

The visual basic code is:

20.13 Excel spreadsheet is set-up as follows:

The visual basic code for this exercise is:

20.14 The following is the user form created for this problem:

The visual basic code is:

20.15 The excel spreadsheet is set-up as follows:

The visual basic code is:

20.16 The following is the user form created for this exercise:

The following is the corresponding visual basic code

20.17 The following presents the Excel spreadsheet created for this exercise.

The following is the corresponding user form:

The following is the visual basic code:

20.18 The following is the corresponding excel spreadsheet:

The following is the user form created for this exercise:

The following is the corresponding visual basic code:

20.19 The following is the user form build for this exercise:

The following is the corresponding visual basic code:

20.20 The following is the user form build for this exercise:

The following is the corresponding visual basic code:

Chapter 21 Review Questions 21.1 21.2

The PivotTableWizard method is used to create a pivot table. RowGrand and ColumnGrand properties specify whether or not grand totals should be calculated for row or column fields of a pivot table. The possible values for these properties are True or False. 21.3 The GetPivotData method in VBA has the same functionality as the GETPIVOTDATA function. 21.4 The method used to set row fields, column fields, and page fields of the pivot table is AddFields. Each field is an optional argument of the method: RowFields, ColumnFields, and PageFields. To enter these argument values, simply type the name of the field. 21.5 The object used to import text and webpages is QueryTables. 21.6 TEXT and URL are used to specify the source type of the import in the Add method, argument Connection. 21.7 To use a space delimiter when importing text is VBA, one should set the TextFileSpaceDelimiter property of the Add method to TRUE. 21.8 The property used to set the delimiters when importing a webpage in VBA using the Add method is: WebConsecutiveDelimitersAsOne. 21.9 DAO stands for Data Access Object and ADO stands for ActiveX Data Objects. Both systems are used to import and manipulate data from databases. Both systems have similar functionalities; however ADO objects are simpler to use. 21.10 The two main ADO objects used to import data are: Connection and Recordset.

Hands-on Exercises 21.1

The following is the user form for this exercise:

The following is the visual basic code for this exercise:

21.2

The following is the user form build for this exercise:

The following is the corresponding visual basic code:

21.3

The following are the two forms build for this exercise:

The following is the visual basic code for frmEx3:

The following is the visual basic code for the frmCompare:

21.4

The following is the visual basic code for this exercise:

21.5

The following is the form created for this exercise:

The following is the corresponding visual basic code:

21.6

The following is the user form build for this exercise:

The following is the corresponding visual basic code:

21.7

The following is the visual basic code for this exercise:

21.8

The following is the form created for this exercise:

The following is the corresponding visual basic code:

21.9

The following is the form build for this exercise:

The following is the corresponding visual basic code:

21.10 The following is the user form build for this exercise:

The following is the corresponding visual basic code:

21.11 The following is the visual basic code for this exercise:

21.12 The following is the visual basic code for this exercise:

21.13 Prior to building the pivot tables, we added 2 additional column with data to the excel worksheet. The data in row I shows for each observation whether the restaurant had a late or on time delivery. The data in row J shows for each observation whether the waiting time was more than 3 min or not.

The following is the visual basic code for this exercise:

21.14 The following are the VBE codes for this exercise:

21.15 The following is the visual basic code for this exercise:

Chapter 22 Review Questions 22.1

Syntax errors are created by: misspelling words in variables or ranges, forgetting to declare variables, forgetting to initialize counting variables or range variables, forgetting to dimension arrays before using them, or misnaming user form objects. Runtime errors are user-created errors. For example, the users may delete cells whose names are referenced in the code, input data incorrectly, or press buttons in a different order than you intended. Logical errors are created by improper use of properties or methods. Logical errors are usually found in loop structures, especially nested loops. For these errors, the code runs without a problem, but the results are inaccurate.

22.2

The Step-Into stepping code is used to execute every line of code. The Step-Over tool will skip over code called procedures. The Step-Out tool is also used primarily with called procedures. If you are stepping through a procedure in which another procedure is called, use Step-Out tool to finish executing all of the code in this called procedure at once and return to the main procedure.

22.3

The difference between step-over and step-out is that when the step-over tool is used it skips a code called procedure/loop; while the step-out is usually used inside a the called procedure/loop and it finishes the execution of the called procedure/loop and returns the user to the main procedure.

22.4

The breakpoint is a tool that allows you to execute code in normal run time until the point in which the breakpoint is located in the code; at this point the code is paused. This procedure helps with debugging the code. It can be used when the user wants to check a particular part of the code for possible errors. In this case, the user would set breakpoints at the part of the code s/he is concerned about. The execution of the previous lines of the code will be quickly and then at the breakpoint the code pauses allowing the user to step through.

22.5

The IsEmpty function is used to check whether or not a variable has been initialized.

22.6

While the The IsEmpty function is used to check whether or not a variable has been initialized, the IsNull function checks whether or not a variable has a valid value.

22.7

The GoTo command is used to skip part of a code and go to a specific procedure.

22.8

An example when the user would get an error message is the following. Suppose an input box is used to receive a numerical value from the user. This value would be then assigned to an integer variable. If the user entered a string instead, we

would get an error. The error number in this case is 13. This signifies type mismatch. 22.9

A Line label is used to label a line of code. To add a line label to the code, one would type the label followed by a colon in the beginning of the line of code.

22.10 The On Error statement should be placed before a line of code in which an error may occur.

Hands-on Exercises 22.1

The following is the corrected procedure: Deleted: ¶

22.2

The following is the corrected code:

22.3

The following is the corrected code:

22.4

The user did not declare, initialize variable i and also was not updating its value within the Do While loop. As a result, the previous code was entering an infinite loop.

22.5

The following is the corrected code.

22.6

The following is the corrected code:

22.7

The following is the corrected code:

22.8

The following is the VBE code for this exercise:

22.9

The following is the visual basic code for this exercise:

22.10 The following is the visual basic code for this exercise: