Faculty.business.wsu.edu
MgtOp 470—Business Modeling with Spreadsheets
Washington State University
Fall 2016
Problem Set 2
Due: October 6, 2016, 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 1” 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 Sphere that takes one argument called radius and returns the volume of a sphere for the given radius. (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. (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 in column C to blue (any shade you like).
(d) Change the background (the Interior property) of the range with employee names (A4:B23) to green (any shade you like).
(e) Enter the label Minimum in cell C24 and boldface it (use VBA code).
(f) Enter a formula in cell D24 that finds the minimum score of Exam 1. Copy this formula to the range C24:F24 (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.) 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 the 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.
OVER(
(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). (To test this, first manually enter some course names in column B.)
(h) A range of inventory figures in a 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. (To test this, first manually enter a rectangular block with appropriate labels and numbers.)
(i) The cell that is 10 rows down from and 4 columns 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.
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 descending order on exam 2.
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 wsCougs. 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.
The following are recommended exercises for practice, but not to be turned in:
Chapter 6, Exercises 2, 3, and 4
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- comma semicolon and vba strings ntnu
- documenting functional volumetric requirements
- engr 160 exam 1 name
- dec s 340—operations management
- exercise 1 infotech 100 main
- an auditing protocol for spreadsheet models
- visual basic error codes and messages
- macros automating tasks you perform frequently
- microsoft excel tutorial 1
- edu