Spreadsheet Models for Managers
Problem Set 4
Temporal Response
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 one.
For a quick way to copy homework problem data into your homework solution, see "Avoid retyping homework problem data."
Since macros are not permitted in this homework assignment, be certain that your workbook has no macros.
Remember that some problems are slight extensions of what we show you in demonstrations and 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 at the left in square brackets indicate point values.
4.1 [50] 
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.
| 2 | 3 | 1 | -3 | 4 |
| 1 | 6 | -3 | 2 | 0 |
| -1 | 6 | 2 | 1 | 2 |
| 3 | 2 | 1 | 0 | -1 |
| 2 | 4 | 8 | 8 | 10 |
|
Define the name InputArray to be the above range. Work each of the parts of this problem independently. That
is, no formula may use any previous result; all formulas must refer directly to InputArray; no formula can
refer to cells by their A1 or R1C1 references. Use only the worksheet functions INDEX or OFFSET. Do not use
explicit range references or the names of any ranges other than InputArray.
(a) [10]
In a 1x5 range of the worksheet, enter an array formula that extracts row 3 of InputArray.
(b) [10]
In a 2x5 range of the worksheet, enter an array
formula that extracts rows 3 and 4 of InputArray.
(c) [10]
In a 5x3 range of the worksheet, enter an array
formula that extracts columns 3 through 5 of InputArray.
(d) [10]
In a 2x3 range of the worksheet, enter a 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.
(e) [10]
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.
4.2 [50]
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're 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 ten-day periods.
|
1-10 |
11-20 |
21-30 |
31-40 |
41-50 |
51-60 |
61-70 |
71-80 |
81-90 |
| Small Invoices |
35% | 15% | 15% | 15% | 12% | 8% | 0% | 0% | 0% |
| Large Invoices |
60% | 10% | 10% | 10% | 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 ten-day periods it
is:
|
1-10 |
11-20 |
21-30 |
31-40 |
41-50 |
51-60 |
61-70 |
71-80 |
81-90 |
| Small Invoices |
125 | 0 | 0 | 170 | 0 | 0 | 175 | 0 | 0 |
| Large Invoices |
50 | 0 | 0 | 60 | 0 | 0 | 55 | 0 | 0 |
(a) [25]
Find the expected inflow of paid invoices, for both large and small invoices for the 9 ten-day 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.
(b) [25]
The average sizes of large and small invoices
are:
|
Avg Size ($) |
| Small Invoices |
8000 |
| Large Invoices |
24500 |
|
Find the expected cash inflow for the 9 ten-day periods.
For more about my spreadsheet consulting
and training services, visit SpreadsheetAce.com.
We run our companies, in part, based on spreadsheets, yet we often wonder
if they're really right.
Learn more about improving the reliability and effectiveness of your
financial models.
With dozens of solved problems and special course tools, this course can dramatically increase your
spreadsheet skills. For just USD 199.00 you receive three months of access with unlimited
email support.
Learn more about improving your spreadsheet sophistication and effectiveness.
Identical to the edition you're viewing, but with dozens of solved problems
and special course tools, and
it stands alone — no Internet connection required. For just USD 199.00, it includes
three months of unlimited
email support.
Learn more about improving your spreadsheet sophistication and effectiveness.
f 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.
