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 assignment.

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:

• Design Reviews and Spreadsheet Inspections
• Formula Silliness

Numbers in square brackets to the right of the problem numbers indicate point values.

Using the Inspection Checklist in the readings, examine the model contained in LCGames.xlsx. A file called LCGames-reference.docx, 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 26 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. When searching for sheet issues, you are to confine your search to the following sheets:

• False
• Inputs
• 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 won’t 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 Inputs. 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.xlsm. 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.

In the report workbook, you’ll notice that the names of the checklist items, which correspond to the checklist items in the Inspection Checklist reading, are in cells that are colored red, with white text. This indicates that nothing has been entered in that checklist item’s First Occurrence cell, More Occurrences cell, or Comment cell. When you enter anything in any of that checklist item’s Occurrence or Comment cells, and recalculate, the Checklist Item cell turns white with black text. This behavior is intended to help you spot those checklist items that you haven’t yet addressed.

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 checklist items. For instance, if you find a range B7:M7 that has an issue with a checklist item, you can enter B7:M7 in either of the Occurrence cells for that checklist item. You could also enter it as B7 in the First Occurrence cell, and C7:M7 in the More 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.

Since entering “None Found” is such a common operation, we’ve provided a shortcut for entering “None Found”. Select any cell in the row in which you want to enter “None Found”, and then press Ctrl+Shift+N. Excel then enters “None Found” in the Comment cell of that row.

In some cases, a workbook might be 100% compliant with respect to a specific checklist item. There’s a special tool in the report workbook, a button labeled “Group Sheets” that makes this case easy to handle. As you go through the workbook, sheet by sheet, you can go back to the report workbook and enter “None Found” in that checklist item’s comment cell on each sheet. That’s a little laborious. Alternatively, you can start with the first sheet and inspect each sheet, one by one, until you find a sheet that isn’t compliant. At that point, enter “None Found” for all the sheets up to that point, and then address the non-compliant sheet. In some cases, you’ll get all the way through the workbook without finding a non-compliant sheet, and that’s when the Group Sheets button becomes useful. Activate the report workbook, click the Group Sheets button, and enter “None Found” in that checklist item’s Comment cell. This has the effect of entering “None Found” in all sheets except the Summary sheet.

Caution: Clicking the Group Sheets button selects all report workbook worksheets corresponding to sheets in the inspected workbook. When that group of sheets is selected, entering anything in any cell of any sheet in the selected group has the effect of entering that same thing in the corresponding cells of all sheets in the group. Be very careful about what you enter and where you enter it. Also, after you’ve made the entry, remember to deselect the group by clicking the Group Sheets button again, or by selecting any other sheet that isn’t in the group.

As an alternative to clicking the Group Sheets button, you can use the keyboard: pressing Ctrl+Shift+T is entirely equivalent to clicking the Group Sheets button. In the case of 100% compliance, then, all you have to do is follow these steps:

• Press Ctrl+Shift+T to group the sheets
• Select a cell in the row corresponding to the pertinent checklist item
• Press Ctrl+Shift+N to enter “None Found” in the Comment cells of all sheets in the group
• Press Ctrl+Shift+T to ungroup the sheets and prevent the danger described above

Or you can do it all by hand, one by one. Yuck.

As you work, you’ll move around from checklist item to checklist item, and worksheet to worksheet. It can get confusing. Sometimes, you might lose track of which items you’ve addressed for which worksheets. The worksheet called Status contains a summary of your progress. Clicking the button labelled “Go to Status” activates the Status worksheet. (If you have Excel’s recalculation option set to Manual, you might need to recalculate to see the latest status. See The Basics of Recalculation for more information about recalculation.)

To start your inspection, download LCGames.xlsx; and report.xlsm. Load the two files into Excel. Follow the instructions for using the reporting workbook. You might also want to review the reference guide written for this project: LCGames-reference.docx.

Some checklist items aren’t violated. Some are violated frequently.

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