If 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.
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:
||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.
Your Course Project is an opportunity for you to practice some of the skills you’ve acquired in this course, while constructing a model of a business or a business process. You’ll be building something significant enough that you’ll encounter some of the difficulties you can expect to find in a real situation.
There’s a lot to read on this page. If you’re just beginning to think about your term project, read “Starting Your Project” first. Once you get started, you might encounter difficulties in keeping up a regular pace between deadlines. Read “Maintaining Forward Momentum on Your Project” for some tips about how to keep up a regular pace.
Your project consists of five elements:
All documents, except for the model itself, are delivered electronically as Microsoft Word documents. The model consists of two scenarios, each submitted as a Microsoft Excel workbook.
The purpose of the Course Project is to provide you with an opportunity to face many of the issues that arise when you build a model “for real.” These include:
Part of the work of designing a useful model is often in the nature of research. For example, you might want to know approximate values of some parameters, such as how many customers per hour are serviced at a help desk. This kind of research is beyond the scope of your project for this course. Instead of doing such research to find realistic values, make plausible guesses. We want you to focus your effort on the mechanics of the problem of creating a model, rather than on the problem of gathering data.
You can get some idea of what constitutes an acceptable project by reviewing the project library. The library consists of projects contributed over the years — some are well done, others not. Since the requirements have evolved over time, too, the library projects are useful only to get a sense of what your project will be like. They aren’t patterns to be followed.
But one aspect of the projects is especially valuable: the section of the final reports that describes lessons learned. It’s generally very helpful to read these sections, because they can help you avoid common errors.
In a real situation, requirements are conditions that must be met by the model. For example, next month at work, you might receive an assignment that has this requirement:
The model shall predict revenues for a three-year period, by quarter.
Note: This is an example of a requirement you might get at work. It isn’t meant to be one of the requirements for this project.
If you were to then build a model that predicted revenues for a four-year period, or a two-year period, your model would then not meet the requirement. In the real world, there is no partial credit — you either meet the requirements, or you don’t. The real world is pass-fail — and in the real world, it’s easy to fail.
The real world also imposes a wide array of requirements that seem to be irrelevant to the content of models. These requirements restrict the formatting of spreadsheets, but they do much more. They might restrict the formatting of your accompanying documents, the titles you use for the sections of your documents, what fonts you can use, how big the margins should be, and so on. Often, there are good reasons for these requirements, but sometimes there are no good reasons. Whether justified or not, in the real world we must comply with these requirements, too. To help you learn how to develop project materials that meet even these kinds of requirements, we’ve included requirements of this type as well.
This page tells you all you need to know to meet the requirements for your Course Project. In this course, we do give partial credit. We’ll grade your project on the basis of how well it meets the requirements. If you don’t meet some of the requirements, for whatever reason, your grade will be affected.
Many of the requirements on this page apply to all projects, independent of topic. For example, the title of Section 1 of the proposal is “Preface”. One way to address these requirements is to implement them early in your project effort, possibly even before you decide what your project is about. For instance, you can create the documents that will eventually become your Proposal, Midpoint Status Report, Usage and Maintenance Guide, and Final Report, complete with all section titles, headers, margins, fonts, and so on, all at once, right at the beginning of your project. Once this work is out of the way, all you have to do is verify that it is still correct before you submit each document for grading.
We’ve divided the requirements for this project into categories:
You may use any computational features of Excel that we’ve described in the course materials. We might, at times, discuss additional computational capabilities, but you may use only the computational capabilities described as approved for use in the course Web site. If you wish to use additional computational capabilities, please consult us in advance for approval. Some computational capabilities are restricted, and we won’t grant approval to use them. These restricted computational capabilities include: pivot tables, the solver, the scenario manager, macros, add-ins other than those we supply, and many more.
There are also some restrictions on formatting Excel worksheets and cells. In general, you can use any formatting capabilities that don’t hide or lock or protect cells, rows, or columns. Nor are you permitted to protect sheets or workbooks. We need these restrictions because we have to examine your project in detail. Restricted formatting capabilities include outlining, hiding rows or columns, workbook or worksheet protection, hidden names, hidden worksheets, and many more. Some formatting capabilities that we haven’t discussed are perfectly fine for you to use. For example, you may use styles.
Below is a table showing the range of acceptable file sizes, and the required application program format. Blank lines, multiple-line spacing, spacing before or after paragraphs, forced line breaks, or forced page breaks are not permitted. Word documents must be in “Word Document” format — they may not be submitted in Rich Text Format (RTF).
|Item||Application||Range of Sizes|
|Course Project Proposal||Word||3-4 pages|
|Midpoint Status Report||Word||1-2 pages|
|Final Report||Word||6-8 pages|
|Usage and Maintenance Guide||Word||6-8 pages|
|Project Model (two scenarios)||Excel||For each scenario, 1 Workbook of 10 worksheets or fewer, smaller than 1 MByte|
You may submit as part of your project only the files listed above. We won’t read or grade any files other than those listed above.
Do not include in your Proposal, Midpoint Status Report, Final Report, or Usage and Maintenance Guide, Excel’s definitions of user-defined names if those definitions are expressed in terms of explicit cell references. Also do not include Excel formulas that contain explicit cell references. Finally do not create cells in your model workbook that contain the definitions of names or display the formulas that appear in other cells (as we do in our homework solutions). If we find these items we’ll assess a penalty. These practices are very dangerous and expensive, because changes in the model could then require compensating changes in the documentation, if those changes result in changes to formulas or relocation of the cells involved. Making these changes correctly is very difficult and costly. We do it in our homework solutions only as a convenience for you. It is not meant to be a demonstration of good modeling practice.
Below is a list of additional document requirements.
|Course Project Proposal||NovaLoan-proposal|
|Midpoint Status Report||NovaLoan-status|
|Usage and Maintenance Guide||NovaLoan-guide|
Think of this project as a “game” or simulation. You’re simulating a situation that can occur at any time in your working life. You’re building a spreadsheet model and writing accompanying documentation that you or your immediate superior, a business manager, can use to explore alternatives before making a decision. Your project will therefore be useful to someone making an important business decision.
That decision is the answer to a specific question, and your model with its two scenarios will be providing an answer to that question. For example, if you’re modeling a health club, a question that might be posed is: How is our business affected if we serve both men and women? The two scenarios might explore two different cases: women only, and both women and men.
Before you build the model and write the documents, you must convince yourself, and possibly your superiors or colleagues, that what you’ll build will actually be useful. Your Course Project Proposal is the document that does this — it makes a case that the model you propose to build will actually help you make the decision about whether or not to build it.
Your Course Project Proposal is both a proposal to build a spreadsheet model, and your plan for doing it. As a plan, it must state explicitly how you’ll meet each of the requirements on this page.
To write this proposal, first think of a problem that requires a decision. It should be something that you feel comfortable with, something perhaps that you know about or have seen, but have not necessarily experienced, although that might be helpful. Perhaps it’s a situation in a company you read about, or in another department in a company where you worked. Once you’ve thought of the problem, think about how you could use spreadsheets to model aspects of that problem.
The model you develop must be a dynamic model. You might be wondering why we require that your project be a dynamic model. Dynamic models aren’t inherently better than static models or tools. They all have their uses. But dynamic models do tend to be richer, with more opportunities for you to apply what you learn in this course about spreadsheet modeling.
When considering the dynamic qualities of your model, keep in mind that although running sums, running differences, and running products do technically make models dynamic, they won’t earn your project much credit for being dynamic. We’re looking for something more substantial.
A well-chosen problem will be one that can be modeled parametrically. That is, by choosing different values for certain parameters of the model, you’ll be able to observe variations in the model’s behavior over time. That behavior must be complex enough that the model exhibits interactions among its output streams, and amongst the various streams of the intermediate computations.
Be careful not to choose too large a problem. The idea here isn’t to aim for complexity or completeness, but to build a model that has some substance, and most important, predictive power.
Your proposal is not a sales document. Do not tell us how wonderful your project is, or that the model is dynamic, or easy to use, or that it is not a tool, or that it obeys the ripple principle, or that it is wonderful in any way. We will determine these things for ourselves. Instead, tell us what we have asked you to tell us, as described below.
Your Course Project Proposal consists of the following six sections, plus an optional Appendix, numbered and titled as indicated. No other sections are permitted, and you must use exactly these titles:
About section titles: We require that you use exactly these titles, in exactly this order, numbered exactly as shown, using Arabic numerals (not Roman numerals), with no additional sections, because similar requirements are common in industry and especially government requests for proposals. Agencies requesting proposals often impose such requirements because they want to be able to compare proposals one to another, and because they often divide proposal review duties of their staff members along section boundaries. A uniform structure for all proposals makes such a division of labor possible. The requirement for exact section titles comes about because proposal reviewers often use the Find command to locate the section that they are to review. If they can’t locate their own section that way, they presume that it’s missing altogether, and then they score the proposal a zero for that section. In the real world, proposals that don’t conform to requirements are often rejected out of hand, even if the section titles are non-compliant in the most trivial ways. Additional sections not specified in the request for proposals are often entirely ignored. This same requirement that section structure and section titles be precisely as requested, applies also, respectively, to all Word documents to be submitted as part of your course project.
By the midpoint, you should have defined the problem, allocated responsibility amongst your team (if you plan to work with others), and revised or figured out a more detailed schedule. The Midpoint Status Report should be at most two pages in length. It should consist of the following three sections, numbered and titled as indicated. No other sections are permitted, and you must use exactly these titles:
Your final report should describe what you accomplished. It should consist of the following five sections (and an optional appendix), numbered and titled as indicated. No other sections are permitted, and you must use exactly these titles:
The Lessons learned section is your chance to explain what could have been done better. Over the years, some mistakes have been repeated in many projects. It would be very helpful as you plan your project to read over the lessons learned sections of the Final Reports that are included in the course project library. In this course, as in any real organization, there isn’t much value in repeating other people’s mistakes.
The Usage and Maintenance Guide serves two purposes:
For users, the Usage and Maintenance Guide is not a guide to the scenarios you explored. Rather, think of it as a guide for users who want to create a third scenario. Typically, your users will want to know what they can vary, what it does, how to find it, and where to look for results.
For maintainers, think of the Usage and Maintenance Guide as if it were the “engineering plans.” Maintainers will use the Guide to help them determine where problems might lie if they feel that the model is producing incorrect results. They might also use it to determine how to extend the model if at some point in the future they need to take into account effects that you did not, or if they need to produce results to augment the results that the model already produces. Typically, maintainers will want to know the inner workings of your model — what it does, and how it does it. It’s an aid in examining the inner workings of your model, so the more useful information you put in the Usage and Maintenance Guide, the easier it will be to see how wonderful your work is.
Your Usage and Maintenance Guide should consist of the following sections, numbered and titled as indicated. No other sections are permitted, and you must use exactly these titles:
The model you construct must meet the following requirements:
Only one submission is required per team. All elements of the Course Project are to be delivered by electronic mail to the course homework submission address, rbrenner@ChacoCanyon.com. The total size of all files submitted as the final project (2 scenarios, Final Report, Usage and Maintenance Guide) must be smaller than 2.0 MBytes. Do not mail your project components to the instructor or teaching fellows.
Last Modified: Wednesday, 27-Apr-2016 04:15:26 EDT
Over the years, students have submitted hundreds of course projects. Having worked with these teams, and graded their submissions, we’ve noticed patterns in the kinds of issues that tend to be challenging for project teams and individuals as they develop their projects. The most common traps are summarized in a Web page: “Common Mistakes in Past Student Projects.” It isn’t required reading, but we do recommend it.
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.
We’ve collected examples of course projects students have submitted over the years. They’re stored in the Course Project Library.
Because we change the project requirements every year, the projects in the library aren’t necessarily precise examples of what you’ll be doing, but they do give you some insight into the kind of thing we’re looking for.
Most important, in the Final Report is a section called Lessons Learned. If you take time to read the Lessons Learned from these projects, you’ll be able to avoid the troubles many of your predecessors encountered. There’s little point in repeating the mistakes of others, so take a look at their lessons learned.