CIS 380 Excel Exam Fall 2013 – READ DESCRIPTION CAREFULLY. MAKE SURE YOU DO ALL REQUESTED ITEMS.
1. You have been doing problems as prescribed by myself and the author. Now I want you to take the
next step. You are going to provide the system requirements for a problem and solve it using Excel –
with some criteria caveats.
2. Write up your system requirements for your personal exam problem using Word. Use headers,
footers, cover page, automatic page numbering, an automatic table of contents, headings / outline
3. Clearly provide a description of the problem. The problem should be complex enough to include, as
much as possible, the Excel features described in the REVIEW for Excel Exam on the next page. You
must also demonstrate a clear understanding and use of the different cell reference techniques. You
should have at least one table that requires a Mixed Reference formula to compute your results.
a. You absolutely need to include:
• some form of lookup ,
• some forms of IF (conditionals),
• some financial functions
• cell referencing across multiple sheets
• proper use of relative, absolute and mixed addressing. With at least one table that uses
mixed addressing appropriately
b. Identify the inputs that are needed and where you plan to obtain them (making them up for the
purpose of this test is sufficient, or you can retrieve data from the internet or elsewhere if you
c. Identify the computations that will be done within the spreadsheet to achieve your desired
outputs. If some of the data must be sorted a particular way to achieve your goals for the
spreadsheet, please explain what, why and how sorted.
d. Describe a testing plan to identify how you will know if what you have created is a correct
solution to the problem you describe.
4. Prepare a spreadsheet that meets the requirements of the problem you described in (2 & 3).
5. Annotate your spreadsheet (use COMMENTS) so that it is obvious where you have used Excel
functions. The annotation should explain how it meets a requirement in your System Requirements
6. Provide a self assessment, in a Word document, which describes: your learning experience with Excel
this semester, and an assessment of your own exam solution. THIS MEANS assign a letter grade for
yourself for the work that you have done, for both the chapter assignments, for this exam and for your
overall score in Excel.
7. Submit your solutions and self-assessment to the appropriate folder and drop boxes on Angel.
precedence of operators
how to compute a percent increase or decrease
basic algebra similar to assignments
basic percentages and percent increase or decrease
most likely functions to be included ( MIN, MAX, SUM, AVERAGE, LARGE, SMALL, VLOOKUP, HLOOKUP,
INDEX, PMT, FV, ROUND, MEDIAN, MODE, RANK, COUNT, COUNTA, RAND, RANDBETWEEN, OR, AND, NOT, IF,
ISBLANK, COUNTIF, SUMIF, )
working with multiple sheets in a single workbook
proper addressing techniques – absolute, relative and mixed – when to use, and use properly so that you
minimize formula entry (and you are able to copy formulas appropriately)
simple charts and graphs
properly using address ranges
understand what happens with missing/blank values versus the value zero (0) when present in AVERAGE,
COUNT, COUNTA, other statistical functions
EXACT versus RANGE lookups