INFS 6610 – Spreadsheet Assignment #3 | Complete Solution

Summer 2016
INFS 6610 – Spreadsheet Assignment #3
 
You are to design and create a spreadsheet model of your own choosing, but it should relate to your industry / work environment.  You may not use any prior example or assignment used in this class as the model. You will also be graded on originality and appropriate spreadsheet design – you should not use another application that looks like examples / assignments used in class and just make a few cosmetic modifications. You may not use a payroll or commission based application. The spreadsheet must meet the following criteria:  
 
1.    The following functions must be used in the spreadsheet:  
 
a. Use a minimum of 3 different database functions of your choice. A separate “criteria” area should be used for each of these three (or more) database functions. All your criteria should be on a separate worksheet named “Criteria”. Name the criteria ranges associated with these functions using a prefix of CRIT1, CRIT2, and CRIT3. You may use the prefix as the name by itself or add to it to be more descriptive.  
b. IF  
c. VLOOKUP  
 
2.    A summary / assumptions area must be created and utilized for outside referencing of data to be used in “What If?” analysis. This assumptions area must be on a separate worksheet by itself named “Summary”.  
 
3.    The spreadsheet must be setup as an Excel database (there is no Access component to this assignment) and have a minimum of 15 records which should be on a separate worksheet named “Database”.  

4.    At least one “Advanced Filter” must be utilized to extract data from the database to an extract area that is designed as a report. This report should have a descriptive title and headings and have a professional appearance. Include some subset but not all of the fields from your database in this report. The criteria range for this extract should be located on the Summary worksheet. The extract (report) area should be on a separate worksheet by itself with the worksheet named “Reports”.  
 
5.    Absolute cell referencing or range names must be used where appropriate. Use the following range names for ranges associated with the database and advanced filter:  
-    database range name “Database”  
-    extract range name “ReportCriteria”  
-    criteria range name for extraction “ExtractReport”  
 
6.    One macro should be created and assigned to a button on the “Summary” worksheet.  The macro should execute the forms option to allow for manipulation of records in the database (new, etc.). Set the macro to display the form on a background without gridlines or other data (similar to the sample spreadsheet covered in your tutorials).  The macro should end by returning to cell A1 on the “Summary” worksheet.
 
7.    Change the names of the worksheet tabs to more accurately reflect the information on each worksheet. The spreadsheet should have a neat and professional appearance with appropriate use of $, commas, etc. Submit the completed workbook to the appropriate D2L dropbox by the due date and time provided in the course calendar.