Spreadsheet Models for Managers
Problem Set 7Session Links
Managing Spreadsheet Modeling Projects
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.
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.
7.1 [100]
Using the Inspection Checklist in the readings, examine the model contained in
either LCGames.xls (Office 2000-4) or LCGames.xlsx (Office 2007+). A file called ReferenceGuide.doc (Office 2000-4) or ReferenceGuide.docx (Office 2007+), which explains
how the model works, accompanies it. 
(These files were submitted by a team of students a few years ago as part
of their Course Project. They've been modified to contain numerous errors.) There are 23 different checklist items. Some
apply to worksheets, and some to workbooks.
Your task is to find places in the model that represent Issues relative to the Inspection Checklist. You are to search
for both workbook issues and worksheet issues. To limit somewhat the actual effort involved, when searching for
worksheet issues, you need search only in the following sheets:
- Input
- BalanceSheet
- NI Chart
- TSE Chart
- IncomeStatement
- StatementOfRetainedEarnings
Here's how credit will be awarded. Of course, in a real inspection, credit isn't an issue.
| Only 25 items count |
Your score on this assignment will be determined by
whether or not the issues you find include any of the 25 issues we've already identified.
You'll receive up to 4 points for each of these issues that you find. You might find and
report additional issues, but they will not count towards your score.
You'll receive credit only for the 25 issues we've selected. Also, if you indicate that
something is an issue, but it actually is not, we'll deduct one point from your
score. |
|
For example, suppose you identify three issues on the worksheet
Input. And suppose that only one of the issues you identified is on our list of issues
to be graded. Further suppose that one of the other issues you identified is not actually an issue.
Then your score for that worksheet would be + 4 - 1 = 3 points. The third issue, which you identified correctly,
won't count toward your score, because it isn't one of the issues we decided to grade. |
| For sheet items |
To receive full credit for an issue that involves multiple cells on a single worksheet,
you must report all such cells on that worksheet. |
| Multiple occurrences |
If the item pertains to a cell formula, then you can count it as a separate
occurrence for each cell, except for array formulas. For array formulas, you get only one occurrence for each entire
array. |
To report your results, use the file report.xls (Office 2000-4) or report.xlsm (Office 2007+). It contains one issues list worksheet (Summary)
for the workbook, and one issues list worksheet for each sheet in the model you're inspecting. Record the location of each
first occurrence of an issue on each sheet, and the ranges of any additional occurrences. Use the
Inspection Checklist in the readings.
You can use the tools provided to make the entries,
or you can simply type the cell references of the cells where you find the issues. For instance, if
you find a range B7:M7 that has an issue, you can enter B7:M7 in either of the occurrence cells for that
issue. You could also enter it as B7 in the first occurrence cell, and C7:M7 in the additional occurrences
cell. It really doesn't matter how you decompose the range into components. Your choice.
If you find that a particular checklist item has no issues on a particular sheet, enter "None
found" in that item's Comment cell. In a real inspection, entering "None found" or something
equivalent is actually important. It captures the information that none were found. If you
simply leave the cell blank, it could mean that none were found, or it could mean that you
haven't looked for that checklist item yet. Entering "None found" when none were found
eliminates the ambiguity.
To start your inspection, copy both LCGames.xls and report.xls (Office 2000-4), or both
LCGames.xlsx and report.xlsm (Office 2007+), into the same directory, and load the
two files into
Excel. Follow the instructions for using the reporting workbook.
Some checklist items aren't violated. Some are violated a lot.
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.
