Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

PAM 5679 Desktop and Modeling Solutions Excel and ILRID4699/6990 Advanced Desktop Applications

Decision Support Modeling

Assignment Overview

Continuing to work from your proposal and the file you submitted, where you have identified the inputs, outputs, and formulas required for your model, continue to translate these into Excel. You should have a solid understanding of how the pieces will go together, continuing to refine it to decide what will go where, how exactly each sheet will look, and what value ranges work best in your model. For example, when you create an input cell, you should know exactly what type of value will go in that cell, and when you create an output cell, you should know exactly what formula (process) will go in that cell, and what other cells it references.  Remember, working backwards isn’t a bad idea.

Specific Requirements

I. Your model must minimally have styles created for input cells, output cells, labels, backgrounds, nav bar, ActiveX Control links and admin tables.  Your overall design should include parallel construction in terms of where items are placed on worksheets as well as titles at the top of each sheet indicating what the sheet will be used for.

II. One sheet named Documentation that describes in detail the purpose for building the model, the decision the model is helping to address, and an explanation of how to use the model.  

a. At the beginning of the Documentation sheet state the question that you are trying to have this decision support model address.

b. Include a description of your base formula(s), and a general description of how information will flow from sheet to sheet.  

c. Include any “notes to grader” and assumptions in this sheet as well.

d. Provide a list and/or statement of what you did for the “15%.”

III. A Title Page with an appropriate model overview/executive summary.

IV. An Admin sheet(s) with relevant lookup tables and/or other non-variable data used in formulas for your output (intermediate or final).

V. A minimum of two Input worksheets. Input sheets should include simple output cells (“intermediate output”) to display totals or other feedback relevant and helpful to the user.

VI. At least one “hard to quantify” variable in your output (ie, weather, customer loyalty, chance of success, etc.).  Please note in the documentation what you chose to do.

VII. An Output worksheet which includes output in a report-like format with picture lookups and output in a chart (the chart can be on the same or a different sheet).

VIII. At least two Help modules (1 for input, 1 for output). More if appropriate.

IX. At least one example of an ActiveX  control and two of conditional formatting.

X. At least two uses of nested functions. For example:  =IF(OR(…))

XI. Hyperlink navigation to and from all worksheets.

XII. At least one macro.

Best Practices Expected

1. Formulas should be built with any combination of cell references, operators, functions, named ranges, and/or non-variable cells (constants).  Note: Avoid hard-coding of non-variable data. This is what your Admin sheet is for.  Remember:  information should only enter the model once.

2. Correct use of absolute and relative references and/or named ranges within your formulas.

3. Appropriate data validation on all worksheets and worksheet and workbook protection (but NO passwords!)   

4. Viewing/finishing touches for a smooth and professional user interface (hiding worksheet tabs, etc.).

5. Any other “Christina says…” best practices noted in class or on the “fussy list”.