Spreadsheet Models for Managers
Problem Set 8Session Links
Financial Models
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.
8.1 [60] 
You're in
charge of capital planning for personal computer acquisitions for Eggplant Technologies, Inc., the leading supplier of
hardware for intensive cultivation of eggplants. You're producing the plan for purchases over the next three years.
Computers for Sales Representatives and Engineers have different prices, as shown in the table below.
  |
Cost (k$) |
| Sales Representatives |
1.5 |
| Engineer |
4.8 |
The hiring stream you're supporting is as shown below.
| |
Y1Q1 |
Y1Q2 |
Y1Q3 |
Y1Q4 |
Y2Q1 |
Y2Q2 |
Y2Q3 |
Y2Q4 |
Y3Q1 |
Y3Q2 |
Y3Q3 |
Y3Q4 |
| Sales Rep |
2 | 3 | 4 | 4 | 2 | 2 | 3 | 5 | 2 | 4 | 2 | 2 |
| Engineer |
0 | 0 | 1 | 1 | 2 | 0 | 1 | 2 | 2 | 0 | 2 | 1 |
ETI depreciates this equipment uniformly over a period of three years after the equipment is acquired, and pays for all
computer purchases in cash. You've been asked to deliver figures that show both cumulatively and for each quarter in the
next three years:
- How much cash these purchases will require
- How these purchases will affect corporate assets
- How much additional depreciation expense these acquisitions will generate
Show all results in units of 1000s of dollars.
(a) [10]
Create a range on your worksheet that holds the data for the hire stream analysis. Directly below that, create a range
for the equipment cost analysis. Name these arrays HireStreamAnalysis and EqptCostAnalysis,
respectively. The top row of HireStreamAnalysis should contain the data for Sales Reps; the bottom row should contain
the data for Engineers. AnswerA should be a compound range consisting of a 2x1 range and a 2x12 range.
(b) [25]
Compute the total cumulative cash required, in thousands, in each quarter to support the hiring streams. Your result should be a single 1x12 range.
(c) [15]
Compute the total cumulative depreciation expense, in thousands, in each quarter. Your result should be a single 1x12 range.
(d) [10]
Compute the cumulative net increase in assets, in thousands, in each quarter resulting from these purchases, after accounting
for depreciation. Your result should be a single 1x12 range.
8.2 [40]
This problem deals with the same situation as Problem 8.1, with the following
changes. For every ninth Sales Rep you add, you must purchase one printer valued at
$1.1K, and for every fifth additional engineer, you must purchase one additional
file server valued at $5.2k.
(a) [10]
Create ranges on your worksheet that hold the equipment cost analysis and equipment stream analysis. The equipment cost
analysis should be a 4x1 range that holds the cost of each of these four kinds of equipment, in the order Sales Rep PC,
Engineer PC, Printer and File Server. The equipment stream analysis should
be a 4x12 range that contains the numbers of each type of equipment that must be purchased in each quarter. The rows of
the equipment stream analysis should also be in the order Sales Rep PC,
Engineer PC, Printer and File Server. Your result should be a single 4x12 range.
(b) [10]
Compute the total cumulative cash required in each quarter to support the hiring streams. Your result should be a single 1x12 range.
(c) [10]
Compute the cumulative depreciation expense in each quarter. Your result should be a single 1x12 range.
(d) [10]
Compute the cumulative net increase in assets in each quarter resulting from these purchases, after accounting
for depreciation. Your result should be a single 1x12 range.
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.
