Washington State University



MgtOp 470—Business Modeling with Spreadsheets

Washington State University

Spring 2019

Problem Set 2

Due: February 26, 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)

Create a user-defined function called Cylinder that takes two arguments: radius and height, and returns the volume of a cylinder for the given radius and height. (Hint: Regular Excel has a function to return the value of[pic].) Show that the function works in a worksheet by inserting the formula somewhere.

2. (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 desired service level, (2) the standard deviation of demand per period, and (3) the length of the lead time.

3. (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 wsCougs. In the first worksheet enter the number 4 in cell A1, and enter “Klay” in cell A2. In the second worksheet enter the number 8 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 going for 3 straight championships!”. For the names, reference those sheets by using their VBA codenames. Also, be sure to insert a space after the first name.

4. (5 points)

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

Write a VBA sub that sorts the scores in descending order on exam 4. Break ties by increasing ID number

5. (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 in a single sub.)

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

(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 ID numbers in column A to blue (any shade you like).

(d) Change the background (the Interior property) of the range with employee names (B4:C23) to green (any shade you like).

(e) Enter the label Minimum in cell C24 and boldface it (using VBA code).

(f) Enter a formula in cell D24 that finds the minimum score of Exam 1. Copy this formula to the range E24:H24 (using VBA code).

6. (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 Profit that covers range C2:P45.

(a) The third cell of the range B2:B16.

(b) The cell at the intersection of the 5th row and 8th column of a range that has the range name Profit.

(c) The cell at the intersection of 35th row and 11th column of a range that has been set to the Range object variable totalProfit. Have totalProfit cover A3:N50.

(d) The cell at the intersection of the 10th row and 22nd column of A1:AA1000.

(e) The entire row corresponding to cell F6.

(f) The set of entire columns from column D through column K.

(g) A range of course names, assuming the first is in Cell B2 and they extend down column B (although you don’t know how many there are). [You will need to enter a range of course names to test this.]

(h) A range of inventory figures in the rectangular block, assuming that location labels are above them in row 1 (starting in Cell E1) and week labels are to their left in column D (starting in Cell D2). 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. [You will need to enter data to test this.]

(i) The cell that is 10 rows down from and 4 column to the right 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.)

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

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

Google Online Preview   Download