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

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.

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 it's easy to fail.

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 do not meet some of them, 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

Your project name consists of 5-8 alphanumeric characters. An alphanumeric character is a letter or a number.

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 capabilites described 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 will not grant approval to use them. These restricted computational capabilities include: pivot tables, the solver, the scenario manager, macros, add-ins other than those used in class, and many more.

There are also some restrictions on formatting. 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.

We'll reduce the grades of any submitted projects that exploit restricted capabilities of Excel, or that use without advance permission any computational capabilities of Excel that are not described in the Web site. Restricted computational capabilities include all worksheet functions that are not listed in the reading on Worksheet Functions. That is, unless you find a particular worksheet function listed there, in the section titled "Which worksheet functions will you need?", or unless you seek and are granted special permission, you may not use that worksheet function in your projects. For example, if you want to use the worksheet function average, you need special permission.

To obtain permission to use one or more of the worksheet functions not in the approved list, send email to rbrenner@ChacoCanyon.com. Include in your message, for each function requested: (a) the name of the function, (b) why you believe you need it, and (c) what will be the consequences for your model if the function is not approved. If your request is approved, approval will arrive by email. Use of unapproved worksheet functions will be penalized.

Below is a table showing the range of sizes, and the required application program format. The use of blank lines or formatting, or forced page breaks, to achieve minimum page count requirements by introducing white space, will be considered a violation of the minimum page count requirement. Word documents may be submitted in RTF format as an alternate.

1. The project name shall be included in the project proposal.
2. All documents shall have revision numbers. The revision number shall appear in the header of every page.
3. All documents, whether for Excel or Word, shall be in Office 97-2003 format. A penalty applies for each document submitted in Office 2007 format.
4. For Word documents, the following items shall appear on the first page in the top left corner:
   • The document type ("Proposal", "Midpoint Status Report", "Final Report", "User Guide" or "Reference Guide")
   • The revision number
   • The project name
   • 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:
   • The project name
   • The revision number
   • The version of Excel used
   • The names and email addresses 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 type ("Proposal", "Midpoint Status Report", "Final Report", "User Guide" or "Reference Guide"), and the revision number. This applies to all documents: your Proposal, your Midpoint Status Report, the Final Report, the Reference Guide, the User Guide.
7. 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 right, top and bottom margins of 1.00 inch. Use a 10-point Times New Roman or 10-point Verdana typeface. Boldface and italics are permitted, but you may not use underlining, outline face, shadow face, or other font effects.
8. Graphs, charts, images and tables are permitted in Microsoft Word documents, but 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 pages of the appendix are not included in the required page count.
9. Your project name shall appear in cell A1 of every Excel worksheet.
10. The name of each document is determined by the name of the project. For example, if the project name is BigDig, then the names of the documents are as shown in the table:

    Item	Document Name
    Project Proposal	BigDig-proposal
    Midpoint Status Report	BigDig-status
    Final Report	BigDig-final
    Reference Guide	BigDig-reference
    User Guide	BigDig-user
    Project Model	BigDig-scenario1 BigDig-scenario2

11. Each document must be free of hyperlinks whether they lead to places external to the document or internal to the document.
12. Each document shall meet specific content requirements
    1. Project proposal
    2. Midpoint status report
    3. Final report
    4. Reference guide
    5. User guide
    6. Project model

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 that you or your immediate superior, a business manager, can use to explore alternatives before making a decision. Your project is a decision support tool.

Before you go ahead and build it, you must convince yourself, and possibly your superiors or colleagues, that what you'll build will actually be useful. Your project proposal is the document that does this — it makes a case that the model you propose to build will actually help you make a decision.

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

A well-chosen problem will be one that can be modeled parametrically. The model you develop must be a dynamic model. 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.

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 Project Proposal should consist of the following five sections, numbered and titled as indicated. No other sections are permitted, and you must use exactly these titles:

1. 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.
2. 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 time 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: Midpoint Status Report, Final Report, User Guide, Reference 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.

3. 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.
4. Inputs, Parameters and Outputs
   Enumerate the input streams, the parameters, and the output streams. Describe each one briefly.
5. 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. Decide on a schedule for meeting these milestones, and describe that schedule and the significance of the milestones in this section.

Note 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 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 the Midpoint Status Report, the Final Report, the User Guide and the Reference Guide.

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

Your final report should describe what you accomplished. It should consist of the following five sections, 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? How did you go about it?
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 achive the purpose of the study.
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? Compare your budget and schedule projections to your budget and schedule actuals.
5. Lessons learned
   Describe at least five things you would have done differently if you were doing the project again. Your grade will not be lowered by any admission of error — quite the opposite, in fact. Consider all aspects of the project effort — choice of problem, design, organization, management, use of technology, and budget and schedule performance.

The user guide describes how to use your model to develop and explore a scenario. It 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.

Your User Guide should consist of the following four sections, numbered and titled as indicated. No other sections are permitted, and you must use exactly these titles:

1. Location and meaning of input parameters and input streams
   Explain to the user what each input parameter or input stream does, and how to locate it in the workbook.
2. Location and meaning of outputs
   Explain to the user what each output stream represents, and how to locate it in the workbook.
3. Guide to visual cues
   Explain to the user any formatting features that help the user find inputs, outputs, or other features of the model workbook.
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.

The reference guide is a maintenance manual for your model. It is not a guide to the scenarios you explored. 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 reference guide, the easier it will be to see how wonderful your work is. Explain, for example, visual cues, any abbreviations used in composing names, what the inputs, outputs and parameters are, any significant intermediate results, what changes a maintainer might wish to make, and how to make them.

Your Reference Manual should consist of the following four sections, numbered and titled as indicated. No other sections are permitted, and you must use exactly these titles:

1. How the calculation works
   Explain to the maintainer how the model uses the inputs to compute the outputs. Include descriptions of intermediate results and how they are computed.
2. How to locate inputs, outputs and intermediate results
   Explain to the maintainer how to find the inputs, outputs and important intermediate results.
3. Guide to visual cues
   Explain to the maintainer any formatting features that help the maintainer find inputs, outputs, or other features of the model workbook. These visual cues should include all of the visual cues in the User Guide, plus any additional cues intended for use by maintainers.
4. 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.

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.
4. The model contains (and uses) at least five named parameters, each of which can be independently adjusted.
5. The model calculates projected values for at least two named output streams of data that span the entire time period. For example, if you're launching satellites for a telecommunications system, one projected data stream might be revenue per month.
6. The model must be entirely free of circular references.
7. The model must not contain blank sheets.
8. The model may or may not use macros. If macros are used, they must be those contained in the course add-ins. The files containing your model shall not contain macros.
9. The model must be entirely free of error values (#VALUE!, #NAME?, etc.). Attempts to conceal the display of error values by means of formatting will be penalized.
10. The model must not contain undefined names
11. 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 in the values of all cells that depend on those values). In all other structural respects, the two scenarios must be identical. Identical.
12. The model must not contain links to other workbooks or add-ins, except for the course add-ins, properly installed in the Excel add-ins folder.

All elements of the Course Project are to be delivered by electronic mail to the course homework submission address, ismte130-homework@dce.harvard.edu. The total size of all files submitted as the final project (2 scenarios, Final Report, User Guide and Reference Guide) must be smaller than 2.0 MBytes. Do not mail your proposal, status report, or final project to the instructor or teaching fellows.