Capstone Project: Excel

Description

In this project, you will demonstrate the Excel skills that you have gained over the past several weeks. In this capstone project, you get to choose what your specific spreadsheet will look like and what information it will include.

Learning Outcomes

At the end of this project, you will be able to:

Assignment Tasks

  1. Choose one of the following spreadsheet types for your project. Yes, you get to choose the one you will create! If you already have some tabular data for another project or class that could be used in your spreadsheet, feel free to use that. Be sure that you create a spreadsheet that is your own work - i.e., be original and avoid the urge to just "borrow" someone else’s work. Here are the types of spreadsheets that you can choose from:

    1. Product/Inventory Listing create an Excel spreadsheet that lists the items found in some collection. This could be a list of the items found in your desk drawer, tools in your workbench, clothes in your closet, parts of an assembly, etc. For each item, you should identify/quantify at least 4 characteristics (color, mfg, name, size, quantity, value/price, etc.). These would most likely make up the columns of your spreadsheet.
    2. Personal Budget create an Excel spreadsheet that defines a list of expense and income items on a more frequent (weekly/monthly) basis and a less frequent (quarterly/annual) basis. Consider using two or more worksheets to consolidate the data between the more frequent data and the less frequent data. For your columns, consider including a budgeted amount, actual amount, and difference between the actual and budgeted.
    3. Expense Tracking List create an Excel spreadsheet that lists the expenses paid for some period of time (e.g., a 30 day period). Consider the details of a bank or credit card statement. A detailed and extended invoice is another option. The columns of data could define date, item description, quantity, vendor, amount paid, ship date, etc.
    4. To-Do or Procedural List create an Excel spreadsheet that details a list of items that have to be done or the tasks that make up a defined procedure. Consider breaking out each to-do item by the task performed, time to complete, due date, required tools or "inputs", completion "outputs", etc.

  2. Using Microsoft Excel, create a spreadsheet (based on the type you selected in step 1) that includes at least 2 worksheets. The worksheets should be appropriately named.

  3. At least one of your worksheets should include enough detailed data to minimally fill 8 columns and 5 rows of data (or 5 columns and 8 rows). In other words, you should have at least 40 (8 * 5) cells of data to work with. This data can be numeric (quantity, price, time, etc.) and/or alphabetic (names, descriptions, etc.).

  4. In creating your spreadsheet, include at least 8 of the following 14 key features/functionalities of Excel (the corresponding SIMBook SLO is in parentheses):
    1. Format your worksheets with cell styles, themes, and worksheet names (SLO 1.4, 1.5, 1.6)
    2. Use absolute, mixed, and/or relative cell references in at least one cell formula (SLO 2.3)
    3. Use one or more Statistical functions (SLO 2.5)
    4. Use one or more Date & Time functions (SLO 2.5)
    5. Use one or more functions from the Financial category (SLO 2.6)
    6. Use one or more functions from the Logical category (SLO 2.6)
    7. Use a VLOOKUP or HLOOKUP function (SLO 2.6)
    8. Use one or more functions from the Math & Trig category (SLO 2.7)
    9. Embed well-formatted Excel chart objects and chart sheets (SLO 3.1, 3.2, 3.3, 3.4)
    10. Use pictures, shapes, and/or WordArt in a chart (SLO 3.5)
    11. Embed one of the "advanced" chart types (3-D Pie, Combination, Sunburst, Waterfall, or Sparklines) (SLO 3.6, 3.7, 3.8)
    12. Include at least one table using an Advanced Filter (SLO 4.4)
    13. Use subtotals, groups, and outlines for tabular data in a worksheet (SLO 4.5)
    14. Embed a formatted a PivotTable (SLO 4.8)

Grading Criteria

This capstone project is worth 150 points or 15% of your overall grade. Your submission will be evaluated based on the following criteria:

Submitting Your Work

  1. Save your final spreadsheet as an Excel .xlsx file with a filename that follows the naming convention: Capstone-AB.xlsx where the last two letters (AB) are your initials.
  2. Submit this file into the SIMnet assignment by the designated Due Date.