Getting Access to Spreadsheet Models for Managers

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.

• What’s in your project
• Objectives
• Requirements
• Library of past projects
• Common mistakes in past student projects

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.

What’s in your project

Your project consists of five elements:

• A project proposal, describing what you intend to do, and how you’ll do it.
• A mid-term status report, delivered at about the half-way point. It describes how you’re doing compared to your proposal.
• The final package, consisting of four elements:
  • The model itself
    Since you’ll be exploring two scenarios, this item consists of two Excel workbooks, one for each scenario
  • A Final Report, describing the problems you encountered, and how you dealt with them
  • A Usage and Maintenance Guide, which explains how to use the model, how the model works, and how to maintain and extend it

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.

Objectives

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:

• Working with a team on a complex project
• Making design decisions when it isn’t really clear what to do
• Making reasonable approximations and simplifications that preserve fidelity
• Producing models and accompanying documentation that meet specified requirements with respect to content, presentation, and formatting.

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.

Requirements

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.

We’ve divided the requirements for this project into categories:

• Project name
• Excel capabilities restrictions
• Document requirements
• How to submit your deliverables

Project name

Your project name consists of 5-8 alphanumeric characters. An alphanumeric character is a letter or a number. You may use upper case or lower case — it’s up to you.

Excel capabilities restrictions

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.

Document requirements

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

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.

Below is a list of additional document requirements.

1. The project name shall be included in the Course Project Proposal.
2. All documents shall have revision numbers. The revision number shall appear in the header of every page that has a header.
3. All documents, whether for Excel or Word, shall be in OpenXML format (Office 2007+). A penalty applies for each document submitted in any other format.
4. For Word documents, the following items shall appear on the first page in the top left corner:
   • The project name
   • The document title (“Course Project Proposal”, “Midpoint Status Report”, “Final Report”, or “Usage and Maintenance Guide”)
   • The revision number
   • The names and email addresses of your team members
5. For Excel workbooks, the following information shall appear in a worksheet called “Info”, which shall be the first worksheet of each workbook:
   • Cell A1: The project name
   • Cell A2: The revision number
   • Cell A3: The version of Excel used
   • Cells A4:B4, A5:B5, …: The names and email addresses, respectively, of your team members
6. All Microsoft Word document pages (except the first page) shall have a header containing the page number, the project name, the document title (“Course Project Proposal”, “Midpoint Status Report”, “Final Report”, or “Usage and Maintenance Guide”), and the revision number. No other information, and no graphics objects whatsoever, are permitted in the page headers. This applies to all Microsoft Word documents: the Course Project Proposal, the Midpoint Status Report, the Final Report, the Usage and Maintenance Guide.
7. All Microsoft Word documents must be in OpenXML (“.docx”) file format for Word 2007+. Rich Text Format (RTF) is not permitted.
8. Microsoft Word documents must not contain macros.
9. Boldface is not permitted in Word documents except for section titles. Boldface is required for section titles.
10. All Microsoft Word documents shall be sized for U.S. Letter paper. Size A4 is not acceptable.
11. All Microsoft Word documents shall be in portrait format (not landscape), shall be single-spaced, shall be left justified, shall have a 1.25 inch left margin, and shall have right, top, and bottom margins of 1.00 inch. Margins must be set in inches. Metric units (cm, mm) are not acceptable. Use a 10-point Times New Roman or 10-point Verdana typeface. Except in section titles, italics are permitted, but you may not use underlining, outline face, shadow face, or other font effects.
12. Section titles must not be indented.
13. Section titles must appear on their own lines. No other text can appear on a section title line.
14. The first line of each paragraph can be indented up to 0.5 inch, but no other lines of any paragraph can be indented.
15. Do not include in Word documents tables of definitions of user-defined names.
16. Microsoft Word documents must not contain any comments or remnants of “Track Changes” operations or document comparison operations.
17. Microsoft Word documents must not contain change bars.
18. Graphs, charts, images, and tables are permitted in Microsoft Word documents, but with one exception, they must appear in an appendix. They must be numbered in sequence, and they must be referred to by number in the text of the document. The exception is that in your proposal, you may present as part of the text, your project development budget and schedule in tabular form. Likewise, in your Midpoint Status Report and Final Report, you may include tables as part of the body of the report to present your discussions of budget and schedule performance. The pages of the appendix are not included in the required page count. If an appendix is included, it should be positioned and numbered as the last section of the document. Its title is “Appendix”.
19. Do not include images of worksheets or portions of worksheets in the Proposal, the Midpoint Status Report, the Usage and Maintenance Guide, or the Final Report.
20. When documenting the various features of your model, do not refer to cells by cell references, row labels or column labels. This makes maintaining your document very difficult. Use worksheet names, row captions, column headings, or other worksheet features instead.
21. Your project name (and only your project name — nothing else) shall appear in cell A1 of every Excel worksheet.
22. The filename of each document is determined by the name of the project. For example, if the project name is NovaLoan, then the filenames of the documents are as shown in the table:

 

1. Each document must be free of hyperlinks whether they lead to places external to the document or internal to the document.
2. Each document must contain the required sections and only the required sections, except possibly an appendix if desired. The appendix may include charts, tables, and diagrams.
3. No documents other than those specified above are to be submitted as part of the course project.
4. Each document shall meet specific content requirements
   • Course Project Proposal
   • Midpoint Status Report
   • Final Report
   • Usage and Maintenance Guide
   • Project Model

Proposal requirements

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:

1. Preface
   In the Preface, you will present, in your own words, the distinguishing characteristics of dynamic models, as they are defined in this course. Explain what dynamic models are, and explain how they are different from spreadsheet projects that are merely tools, or projects that are merely static models. We ask this because we want to encourage you to grapple with this question right away, rather than later on, after you’ve invested much effort in a project that might not meet the very basic requirement that it be a dynamic model. We recommend that you write this preface before you even consider possibilities for a project.
2. Overview
   The Overview describes what is being modeled and what the business objectives are. It tells who the model’s users would be, and defines the scope of the problem. The overview should also include a project name of five to eight characters, and descriptions of the two scenarios you intend to explore. Don’t describe an imaginary situation in which your team is providing consulting services, or your fictional company needs a model for some purpose. We aren’t interested in a novel. What we want to know is (a) the business issue at hand; (b) the question your model will help resolve; and (c) the two scenarios you intend to explore to help you resolve the question.
3. Budget
   Estimate the cost of delivering your project. Since this is a course (and not the real world), the units of cost are Hours, instead of Dollars. Supply a total cost estimate — the total number of hours required to complete the project. Also, include a cost estimate for the total hours of effort expected, and the hours of effort expected to be spent in each of the following categories:

Planning

Planning includes all the effort needed to define the problem, to allocate the pieces of the problem amongst the team members, and to devise a schedule for completion of the entire project.

Modeling

Modeling includes the actual implementation of the model and its components.

Documents

The cost of producing each document: Course Project Proposal, Midpoint Status Report, Final Report, and Usage and Maintenance Guide.

Execution

You’ll use your model to explore the problem you chose. In this portion of the effort, you select at least two scenarios to explore, and make observations of your model’s behavior. These observations are to be described in the Final Report.

1. Team
   State who will be working on this project. If your team is three or more, it would be wise for one of you to act as coordinator. If you’re the only one working on the project, this is the place to say so.
2. Input streams, input parameters, and output streams
   Describe all input streams, all input parameters, and all output streams. Describe each one briefly. If you have any doubt at all about the meanings of these terms, read Terminology for Modeling.
3. Schedule and milestones
   Break up your effort into at least five major steps, or milestones. Each step should correspond to some concrete, deliverable, or demonstrable item. Specify a schedule for meeting these milestones, and describe that schedule and the significance of the milestones in this section.
4. Appendix
   The Appendix is optional. Use it for graphs, charts, and tables that are not permitted to appear in the body of the document. The pages of the Appendix do not count toward the page count requirement.

Midpoint Status Report requirements

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:

1. Problem definition
   This section contains a careful definition of your objectives. To write it, think about answering this question: What do I want to learn from doing this? Your answer will tell you what parts of the problem are important to you, and how to trim away parts of the task that are unimportant. What you have left should still have an element of realism. That is, it should still be something like what you might encounter in a real situation. Also include a summary of changes you’ve made in your overall concept since you wrote the proposal.
2. Who does what
   If you’re working as part of team, you’ll probably allocate parts of the project to different people. Include a task list of at least seven items, and tell us who is responsible for what. Generally, the more finely detailed the task list, the more smoothly your project will proceed. It is not acceptable to have everyone responsible for everything. If you’re working alone, then just include the task list.
3. Refined schedule and budget
   Now that you’ve defined the problem a bit better, and perhaps even started the work, you might want to revise the schedule and budget in your Course Project Proposal. Even if you haven’t discovered a need to revise them, include the original schedule and budget, and state that they’re unchanged.
4. Appendix
   The Appendix is optional. Use it for graphs, charts, and tables that are not permitted to appear in the body of the document. The pages of the Appendix do not count toward the page count requirement.

Final Report requirements

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:

1. Problem statement and approach
   Clearly state the purpose of the study. What are you trying to accomplish? What is the question you are posing? How does your solution go about answering that question? A history of your attempts is not needed or wanted. Rather, we want to know specifically how the results of your two scenarios help you to answer the question posed as the purpose of your study.
2. Descriptions of scenarios
   Include descriptions of your two scenarios. How do they differ? Describe how and why you expected these two scenarios to help you achieve the purpose of the study. At least two input parameters and two input streams must differ in the two scenarios.
3. Conclusions of the study
   Describe what you learned by comparing the results of the two scenarios. This discussion should have a business orientation, comparing the salient features of what was exposed by comparing your two scenarios.
4. Budget and schedule performance
   How much effort was required? Were you able to meet your scheduled milestones? Present a side-by-side comparison of your planned budget with your actual expenditures, and a side-by-side comparison of your planned schedule with your actual schedule. Compare your budget and schedule projections to your budget and schedule actuals, and explain any deviations.
5. Lessons learned
   Describe at least five things you would have done differently if you were doing the project again. Your grade won’t be lowered by any admission of error — quite the opposite, in fact. Consider all aspects of the project effort — choice of problem, design, team dynamics, organization, management, use of technology, and budget and schedule performance. Include at least one thing you did right. You may present the lessons learned in any order, but each lesson learned must be in its own paragraph. That paragraph must begin with “Lesson <n>:”, where n is the lesson number (the angle brackets are not to be included).
6. Appendix
   The Appendix is optional. Use it for graphs, charts, and tables that are not permitted to appear in the body of the document. The pages of the Appendix do not count toward the page count requirement.

Usage and Maintenance Guide requirements

The Usage and Maintenance Guide serves two purposes:

• For users: It describes how to use your model to develop and explore a scenario.
• For maintainers: It describes how the model works internally, in enough detail to enable a maintainer to make repairs as needed, and to extend the model if that’s ever required.

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:

1. Location and meaning of all input parameters and input streams
   Explain to the user what each input parameter or input stream does, and how to find it in the workbook.
2. Location and meaning of all outputs
   Explain to the user what each output stream represents, and how to find it in the workbook.
3. Guide to visual cues and naming conventions
   Explain any formatting features that help the user and maintainer find inputs, outputs, or other features of the model workbook. Explain any conventions you adhere to for creating names of ranges. Include enough detail so that someone wishing to extend the model can format the extension in a manner consistent with the rest of the model.
4. Step-by-step use of the model
   Explain to the user, in a sequence of steps, how to make adjustments to a scenario, and how to select adjustments to achieve specific kinds of results.
5. How the calculation works
   Explain to the maintainer how the model uses all inputs to compute the outputs. Include descriptions of intermediate results and how they are computed. Explain the important intermediate results, and tell the maintainer how to find them.
6. How to make changes
   Explain to the maintainer how to extend the model. For example, explain how to add a new kind of input stream, or a new kind of input parameter, or how to add a new kind of output stream. Include in this section any ideas you have for extending the model. This section is not about how to change the input parameter values or the input stream values to make changes to scenarios.
7. Appendix
   The Appendix is optional. Use it for graphs, charts, and tables that are not permitted to appear in the body of the document. The pages of the Appendix do not count toward the page count requirement.

Project model requirements

The model you construct must meet the following requirements:

1. The model represents a business or a business process.
2. The model projects the behavior of the business or business process over a time period consisting of either
   • 12 weeks, week by week, or
   • 12 months, month by month, or
   • 12 quarters, quarter by quarter, or
   • 12 years, year by year.
3. The model contains representations of at least three named streams of input data that span all 12 time periods. Each cell of each input stream is independently adjustable. For example, if you’re launching satellites every month for a year, one input stream might be the number of rockets launched in each month. The values in the cells of the input streams cannot be derived from any other quantities — that is, each cell in an input stream contains a formula consisting of a number. Each cell of each input stream must be independent of everything else.
4. The model contains (and uses) at least five named input parameters, each of which can be independently adjusted. That is, each input parameter is represented as a cell that contains a formula consisting of a number.
5. If you have more than the minimum required number of input parameters, input streams, output streams, that’s fine, but you must document all of them in all documents that require these items to be documented.
6. Both scenarios must use all input parameters and all input streams in meaningful ways. For example, an output stream that links directly to an input stream would be a non-meaningful use. Another example: an input parameter that is ignored. A third example: an input parameter that is used in a manner that cancels its effect, as in a formula of the form =Parameter/Parameter.
7. At least five input parameters must be non-zero for both scenarios. At least seven cells of each input stream must be non-zero.
8. No input parameter can have the same value in both scenarios.
9. At least seven cells of each input stream in Scenario 1 must be different from the corresponding cells in that stream in Scenario 2.
10. The model calculates projected values for at least two named output streams of data that span all twelve time periods. For example, if you’re launching satellites for a telecommunications system, one projected data stream might be revenue per month.
11. The model must be entirely free of circular reference errors.
12. The model must not contain blank sheets.
13. The model may or may not use macros. If macros are used, they must be those contained in the course add-in. The files containing your model shall not contain macros.
14. The model must be entirely free of error values (#VALUE!, #NAME?, etc.). Concealing the display of error values by means of formatting will be penalized.
15. The model must not contain undefined names.
16. The model workbook is constructed using the blank workbook we provide — CourseProject.xlsx. We require this because this workbook facilitates grading. All model inputs must be on the worksheet named Inputs; all model outputs must appear on the worksheet named Outputs. You may add additional sheets if you like, but you may not delete or rename any of the worksheets named Info, Inputs, or Outputs.
17. Each scenario must be produced from the same “blank” model by inserting values for the input parameters and input streams. The scenarios differ from each other only in these values (and of course, potentially in the values of all cells that depend on those values). In all other structural respects, the two scenarios must be identical. Identical.
18. The model must not contain links to other workbooks or add-ins, except for the course add-in, properly installed in the Excel add-ins folder.

How to submit your deliverables

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