Spreadsheet Models for Managers

Getting Access to Spreadsheet Models for Managers

If Spreadsheet Models for Managersyou 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.

As a stand-alone Web site
It resides on your computer, and you can use it anywhere. No need for Internet access.
At this Web site
If you have access to the Internet whenever you want to view this material, you can purchase on-line access. Unlimited usage. I’m constantly making improvements and you’ll get them as soon as they’re available.

To Order On Line

Order "Spreadsheet Models for Managers, on-line edition, one month" by credit card, for USD 69.95 each, using our secure server, and receive download instructions by return email.
Order "Spreadsheet Models for Managers, on-line edition, three months" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email.
Order "Spreadsheet Models for Managers, downloadable hyperbook edition" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email.

To Order by Mail

Make your check payable to Chaco Canyon Consulting, for the amount indicated:
  • For the download: USD 199.00
  • For access online for three months: USD 199.00
  • For access online for one month: USD 69.95
And send it to:
Chaco Canyon Consulting
700 Huron Avenue, Suite 19C
Cambridge, MA 02138

To use the course software you’ll need some other applications, which you very probably already have. By placing your order, you’re confirming that you have the software you need, as described on this site.

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:

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

Problem 7.1 [100]

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. An
inspection meeting (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

Before you get started, be very certain that you understand what each checklist item means. A common error in this kind of work is misinterpretation of what the actual standard is. Check with your teammates and see if you agree on the interpretations at the very outset of your work together on this assignment. And check in later, too, to ensure that everyone still agrees about the interpretations. Whether you’re working alone or in a team, read the descriptions of the checklist items to ensure that your understanding is clear.

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.

Save your work frequently. To make it possible to backtrack in case you make a mistake, make copies of your solution under different filenames. For instance, the first time you save, if your homework name is DuckD, you might use the filename DuckD07-1. The next time you save, use the filename DuckD07-2, and so on. Of course, when you submit your homework, you have to submit it as DuckD07.

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

Inspect Your Project Early

Many believe that the main benefit of spreadsheet inspections is that they locate issues so they can be fixed. Certainly they do accomplish that. But spreadsheet inspections, when performed early enough and often enough, can actually prevent problems. And preventing problems is certainly more valuable than locating them.

We hope that you’ll apply what you learn about spreadsheet inspections when you work on your projects. If you’re working in a team, review your project schedule and decide when would be advantageous times to insert an inspection or two. If you’re working alone, ask someone else who’s working alone if they would be willing to inspect your project in exchange for your inspecting theirs.

Since we don’t grade on a curve, helping someone else doesn’t hurt you. Inspection exchanges raise the quality of both projects — and both grades. Whatever you do, don’t wait until the end to do your inspections.