Spreadsheet Models for Managers

Problem Set 4Session Links
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 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:

• Convolution
• Reference Operators
• Worksheet Functions

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.

2 3 1 1 5 4 2 3 0 2 4 8

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 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%	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 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	65	0	0	50	0	0

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. Your result should be a single 2x9 range.

The average sizes of large and small invoices are:

Avg Size ($) Small Invoices 8000 Large Invoices 24300

Find the expected cash inflow for the 9 ten-day periods. Your result should be a single 1x9 range.

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