Washington State University



MgtOp 470—Business Modeling with Spreadsheets

Washington State University

Spring 2018

Problem Set 2

Due: February 27, midnight

E-mail your completed files to lan.luo@wsu.edu. This assignment may be completed in a group of up to four people. Please state in the email message the names of all persons turning in this assignment. Please put “MgtOp 470 Homework 2” followed by the name of the person who sent the email in the Subject of the email.

Note: The file related to this assignment is available on our course website.

1. (6 points)

Body mass index (BMI) is a measure of body fat based on height and weight: BMI= weight (kg) / [height (m)]2. Create a user-defined function called BMI that takes two arguments: weight (lb) and height (in) and returns the BMI for the given weight and height. (Hint: Convert the weight and height into metric measures.) Show that the function works in a worksheet by inserting the formula into Cell A1.

2. (12 points)

The file Training.xlsx contains the data set of five exam scores. Also, there is a heading in cell A1, and the data set begins in row 3. Do the following with VBA. (Place the code for all of the parts into a single sub.)

(a) Boldface the font of the label in cell A1, change the font color to blue and change its font size to 36.

(b) Boldface and italicize the headings in row 3, and change their horizontal alignment to the right.

(c) Change the color of the font for the employee names in column A and B to pink (any shade you like).

(d) Change the background (the Interior property) of the range with scores (D4:H23) to orange (any shade you like).

(e) Enter the label Median in cell C24 and boldface it (use VBA code).

(f) Enter a formula in cell D24 that finds the median of the scores above it. Copy this formula to the range E24:H24 (use VBA code).

3. (12 points)

Write a reference (in VBA code) to each of the following ranges. You can assume that each of these ranges is in the active worksheet of the active workbook, so that you don’t have to qualify the references by worksheet or workbook. (Hint: You can “reference” the ranges by using the “Select” method e.g., Range(“A2:C4”).Select.) Before you begin, create a range name in Excel called Best that covers range A2:L34.

(a) The fifth cell of the range C3:C18.

(b) The cell at the intersection of the 16th row and 7th column of a range that has the range name Best.

(c) The cell at the intersection of 13th row and 3rd column of a range that has been set to the Range object variable Hello. Have Hello cover B2:M50.

(d) The cell at the intersection of the 19th row and 12th column of A1:Z900.

(e) The entire row corresponding to cell A1.

(f) The set of entire columns from column E through column G.

(g) A range of city names, assuming the first is in Cell B2 and they extend to the right in row 2 (although you don’t know how many there are).

(h) A range of inventory figures in the rectangular block, assuming that location labels are above them in row 3 (starting in Cell F3) and week labels are to their left in column E (starting in Cell E4). You don’t know how many locations or weeks there are and you want the range to include only the sales figures, not the labels.

(i) The cell that is 3 rows up from and 4 column to the left of the active cell. (The active cell is the cell currently selected. If a rectangular range is selected, the active cell is the first cell that was selected when the range was selected. It can always be referred to in VBA as ActiveCell.

4. (5 points)

The file Training.xlsx has five exam scores, in columns D through H, for each of the employees listed in column A.

Write a VBA sub that sorts the scores in ascending order on exam 3.

5. (10 points) In the field of operations management, for a continuous review system, safety stock is often determined by the formula: SS = zσL, where z is the number of standard deviations from the mean needed to achieve the service level, and σL is the standard deviation of demand during lead time. We obtain z from the standard normal probability table (for example, a service level of 95% corresponds to a z-value of 1.645), but it can also be obtained by using the NORMSINV function in Excel. The standard deviation of demand during lead time equals the standard deviation of demand per period times the square root of the number of periods in the lead time. For example, if the lead time = 4 days and the standard deviation of demand per day is 20 units, then σL = [pic]

Create a user-defined function called SS that provides the safety stock level when the user provides three arguments: (1) the standard deviation of demand per period, (2) the length of the lead time, and (3) the desired service level.

6. (5 points) Create a file with two worksheets. Name the first worksheet Go and the second worksheet Cougs. Create a VBA codename for the first sheet as wsGo and a codename for the second sheet as wsCougars. In the first worksheet enter the number 1 in cell A1, and enter “Klay” in cell A2. In the second worksheet enter the number 2 in cell A1, and enter “Thompson” in cell A2.

Write a SUB that adds the values in cell A1 from the two worksheets and displays the result in a message box. Reference those sheets using their Excel sheets names. Next concatenate the names appearing in cell A2 from the two worksheets, and display that full name in a message box, followed by, “ is the 3-point champion!”. For the names, reference those sheets by using their VBA codenames. Also, be sure to insert a space after the first name.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download