If you 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.
To Order On Line
Order "Spreadsheet Models for Managers, online 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, online 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:

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.
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.
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.

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.2 Expected Unit Sales of new products by the 10 wholesalers of El Capitan Insurance. 

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 singlecell 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, 27Apr2016 04:15:26 EDT
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 builtin worksheet functions. That effort might clarify for you the kernel of the computation that benefits most from a macrobased approach.