Spreadsheet Models for Managers

Getting Access to Spreadsheet Models for Managers

If Spreadsheet Models for Managersyou use Excel to model businesses, business processes, or business transactions, this course will change your life. You’ll learn how to create tools for yourself that will amaze even you. Unrestricted use of this material is available in two ways.

As a stand-alone Web site
It resides on your computer, and you can use it anywhere. No need for Internet access.
At this Web site
If you have access to the Internet whenever you want to view this material, you can purchase on-line access. Unlimited usage. I’m constantly making improvements and you’ll get them as soon as they’re available.

To Order On Line

Order "Spreadsheet Models for Managers, on-line edition, one month" by credit card, for USD 69.95 each, using our secure server, and receive download instructions by return email.
Order "Spreadsheet Models for Managers, on-line edition, three months" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email.
Order "Spreadsheet Models for Managers, downloadable hyperbook edition" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email.

To Order by Mail

Make your check payable to Chaco Canyon Consulting, for the amount indicated:
  • For the download: USD 199.00
  • For access online for three months: USD 199.00
  • For access online for one month: USD 69.95
And send it to:
Chaco Canyon Consulting
700 Huron Avenue, Suite 19C
Cambridge, MA 02138

To use the course software you’ll need some other applications, which you very probably already have. By placing your order, you’re confirming that you have the software you need, as described on this site.

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:

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

Blue square Problem 14.1 [40]

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.

Retrieve homework data in 2007+ format
x TurboRound100(x)
Blue square Problem 14.2 [60]

El CapitanIn 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.
Retrieve homework data in 2007+ format Vets Rookies
Product A 1.001.23
Product B 0.500.75
Product C 0.761.26
Product D 0.630.76
Product E 0.741.36

Table 14.2.2
Expected Unit Sales of new products by the 10 wholesalers of El Capitan Insurance.
Retrieve homework data in 2007+ format
Wholesaler 1 2 3 4 5 6 7 8 9 10
Product A 39750391468062268810528801278
Product B 1419231706031845217796744951
Product C 75457127316469839372557128679
Product D 51597835456137935519677310407
Product E 1396790661564895115522635591
Gray square Problem 14.2 (a) [30]

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.

Gray square Problem 14.2 (b) [20]

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.

Gray square Problem 14.2 (c) [10]

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

How to Measure the Value of a Function Macro

The value of a function macro increases with frequency of use, the complexity of the calculation it performs, and the area of the result it returns. As you examine the computations you perform routinely in your work, make note of those computations that meet these criteria. Before committing yourself to writing a macro to carry out one those computations, try various methods for implementing it using standard built-in worksheet functions. That effort might clarify for you the kernel of the computation that benefits most from a macro-based approach.