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.”
Since macros aren’t permitted in this homework assignment, be certain that the workbook you submit for grading has no macros. Read about how to check your workbooks for macros.
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 this problem you’ll become more familiar with the ways of accessing portions of ranges using the worksheet functions INDEX and OFFSET, and some of the reference operators, as well as other techniques and functions. Throughout this course, when we refer to an “m by n” or “m x n” array (or range or matrix), we mean an array (or range or matrix) of m rows and n columns.
In a 5x5 range of a worksheet, enter the data from the table below.

Define the name InputArray to be the above range. Work each of the parts of this problem independently. That is, no formula you write may use any previous result; all formulas must refer directly to InputArray; no formula you write can refer to cells by their A1 or R1C1 references. Use only the worksheet functions INDEX or OFFSET, or any of the worksheet functions we’ve introduced so far in this course. Do not use any other worksheet functions. Do not use explicit range references, explicit cell references, or the names of any ranges other than InputArray.
In a 1x5 range of the worksheet, enter an array formula that extracts row 3 of InputArray. Your result should be a single 1x5 range.
In a 2x5 range of the worksheet, enter an array formula that extracts rows 3 and 4 of InputArray. Your result should be a single 2x5 range.
In a 5x3 range of the worksheet, enter an array formula that extracts columns 3 through 5 of InputArray. Your result should be a single 5x3 range.
In a 2x3 range of the worksheet, enter an array formula that extracts the elements of the InputArray that lie in columns 1 through 3 and rows 3 and 4 of InputArray. Use the intersection operator. Your result should be a single 2x3 range.
In a 3x2 range of the worksheet, enter an array formula that produces the transpose of the subarray of elements of the InputArray that lie in columns 3 through 5 and rows 3 and 4 of InputArray. Use the intersection operator. Your result should be a single 3x2 range.
Note: In this problem, you may wish to use the Convolve macro.
You have been asked to develop a better model of invoice payment flows for your company, because the CFO is interested in more accurate projections of cash flow. By examining historical data, you’ve discovered that your customers generally pay bills earlier if they exceed $10,000, but generally wait longer if they are smaller than $10,000. All invoices are mailed so that customers receive them on the first of the month. Below is a table showing the percentage of invoices paid as a function of the number of days after mailing for two categories of invoices, large and small. The number of days after mailing are themselves grouped into categories — 9 tenday periods.
110  1120  2130  3140  4150  5160  6170  7180  8190  
Small Invoices  35%  15%  15%  15%  13%  7%  0%  0%  0% 
Large Invoices  60%  15%  10%  5%  5%  5%  0%  0%  0% 
The Sales department has provided a projection of business for the next three months. From this, you have developed an anticipated distribution of the sizes of invoices expected over that time period. Expressed in terms of tenday periods it is:
110  1120  2130  3140  4150  5160  6170  7180  8190  
Small Invoices  125  0  0  170  0  0  175  0  0 
Large Invoices  50  0  0  65  0  0  50  0  0 
Find the expected inflow of paid invoices, for both large and small invoices for the 9 tenday periods. Display your answer as a 2x9 range, with the top row representing small invoices, and the bottom row representing large invoices. Fractional results are OK. Your result should be a single 2x9 range.
The average sizes of large and small invoices are:

Find the expected cash inflow for the 9 tenday periods. Your result should be a single 1x9 range.
Last Modified: Wednesday, 27Apr2016 04:15:26 EDT
The space character, in many cases, doesn’t change the value of a formula. For instance, these two formulas return the same value:
Some people think that wellplaced spaces make formulas easier to read. Although that might be true, the practice is both inconvenient and extremely dangerous. More
Excel’s online help, and many of the howto books you can buy, provide long lists of keystroke shortcuts for carrying out specific operations, such as inserting rows, selecting regions, or deleting columns. And they are useful.
But the true power of the keyboard comes not from using these particular commands. Rather, it comes from learning combinations that are useful for particular situations that you encounter frequently.
For instance, there’s no command for deleting the rows that contain the selected cells, but there is a combination:
And so, Shift+Space Ctrl+ deletes the rows containing the selection.
Learning a vast array of keystroke commands is probably less useful than learning the keystroke combinations that do exactly what you need to do most often.