Spreadsheet Models for Managers

Problem Set 14Session Links
Using Macros II

Be sure to check the list of worksheet functions that are needed for the homework assignments, to see which new functions (if any) might help with this assignment.

For a quick way to copy homework problem data into your homework solution, see “Avoid retyping homework problem data.”

Remember that some problems are slight extensions of what we show you in class, in the demonstrations and in the session notes, and some problems are somewhat ambiguous. This is intended to parallel what you’ll frequently encounter at work. If you feel a bit confused, there are some things you can do to help clarify things.

Before attempting this homework, you might find it helpful to read about:

• Excel Macros in Visual Basic for Applications

Numbers in square brackets to the right of the problem numbers indicate point values.

In Problem 13.1, you wrote a macro Round100. Write another macro called TurboRound100, which meets the same requirements as Round100, except that it also accepts arrays of any size, and returns an array of the same size, with each entry rounded as if by Round100. Your returned result should contain the same numbers that would be obtained by applying Round100 to each cell in the array range. Use your macro to produce the second column of the table below (the same data as in Problem 13.1) when it receives the first column as an argument. Your result should be a single 4x1 range.

x TurboRound100(x) 457.2 500 0.0 0 100.0 100

In this problem you’ll repeat the computations of Problem 2.3 (El Capitan Insurance), but this time you’ll use macros. Copy these two tables into your worksheet, either from the Homework Data document or from your solution to Problem Set 2, or from our solution to Problem Set 2, solve02.xlsx, and give them the names EffortAnalysis and WholesalerProductAnalysis. Throughout this problem, all of your macros should be capable of handling arrays of any size. That is, write your macros in such a way that they do not need to be modified if the number of wholesalers is increased or decreased, or if the number of products is changed.

Table 14.2.1 Expected effort required of veterans and rookies to brief a wholesaler on each of the five new products.

Vets Rookies Product A 1.00 1.23 Product B 0.50 0.75 Product C 0.76 1.26 Product D 0.63 0.76 Product E 0.74 1.36

Table 14.2.2 Expected Unit Sales of new products by the 10 wholesalers of El Capitan Insurance.

Wholesaler 1 2 3 4 5 6 7 8 9 10 Product A 397 503 914 680 62 268 810 528 801 278 Product B 141 923 170 603 18 45 217 796 744 951 Product C 754 571 273 164 69 839 372 557 128 679 Product D 515 978 354 561 379 355 196 77 310 407 Product E 139 67 906 615 648 951 15 522 635 591

Write a macro called Threshold that takes two arguments. The first argument should be the data. The second argument is the threshold. Your macro should return 1 if its first argument is equal to or greater than the threshold (the second argument) and 0 if the argument is less than the threshold. Your macro should work equally well on data (first argument) consisting of single-cell numeric arguments and on arrays of numeric arguments. When your macro receives an array for the first argument, it should return an array of 1s and 0s, where each 1 or 0 signifies whether or not the corresponding array element in its first argument is less than the threshold (1 if greater than or equal to threshold, 0 if less than threshold).

Use your macro Threshold to produce the Threshold1 array of Problem 2.3 (b). That is, compute the array of 1s and 0s that corresponds to

Your result should be a single 5x10 range.

Write a macro called TotalEffort that carries out the complete computation of the effort expended by the Vets and Rookies without requiring the intermediate computation of the Threshold1 array as was done in part (a). Your macro should accept the following arguments:

Effort Analysis	An array containing the data of Table 14.2.1.
Wholesaler Product Analysis	An array containing the data of Table 14.2.2.
Minimum Units	The threshold of unit sales a wholesaler must meet to be briefed by a rep.

Your macro should return an array with two rows. The top row is the total effort expended by Veterans for each of the wholesalers, and the bottom row is the total effort expended by Rookies for each of the wholesalers. This is equivalent to the computation you performed in Problem 2.3 (b). What you’re doing is creating a macro that replicates the work you did in Problem 2.3 (b), and suppresses the display of the Threshold1 array. If your department had a lot of work like this, this macro would be very handy. Your result should be a single 2x10 range.

Recall that in Problem 2.3 (c), Marketing decided to drop Product E. You would like to build a version of TotalEffort that accepts an additional argument that determines whether or not to include the last product. Call your macro TotalEffortE. In addition to the arguments of TotalEffort, TotalEffortE must accept a fourth Boolean argument, which is TRUE if Product E is to be included and FALSE if not. Remember: you’re to write your macro in such a manner that it will work for any number of products. If the fourth argument is TRUE, your macro includes the last product. If FALSE your macro excludes the last product. Your result should be a single 2x10 range.

Last Modified: Wednesday, 27-Apr-2016 04:15:26 EDT