MRCH 2760 FIDM Gradebook and Sales Inventory Planning Worksheet OBJECTIVE: Create a gradebook using Microsoft Excel® so that when data is entered the wor

MRCH 2760 FIDM Gradebook and Sales Inventory Planning Worksheet OBJECTIVE:

Create a gradebook using Microsoft Excel® so that when data is entered the worksheet performs the necessary calculations, displays the calculated final grade and includes a dashboard. Workbook must have a different layout than your MRCH.1950 gradebook file.

PURPOSE:

This challenge will provide you with the opportunity to keep track of your grades using Excel®. Our class grades are weighted and this spreadsheet will require applying the the Excel® skills acquired in MRCH.150 and enhanced by skills developed this quarter.

This spreadsheet is not your official gradebook, so grades missing from Canvas garadebook at the time of assignment submission are not required, but the spreadsheet still needs to have proper formulas present so when the data is entered, the spreadsheet calculates.

What matters is that your spreadsheet meets the following criteria.

CRITERIA:

Begin with a new workbook and name the sheet (tab) MRCH2760. Use the gradebook located in the class eLearning site for all required spreadsheet data.

Step 1: Plan the spreadsheet

Look ahead while planning where in the spreadsheet to input data
Review the assignment completely and the resources required
Sketch out how you see the data organized
Keep in mind how data is best grouped to create charts and graphs (linking and consolidation of data should be used here)
STEP 2: INPUT DATA

Input data from Canvas Gradebook.

Assignments will take you to the list of gradebook categories, their weight percent, and all course gradebook items
Include:
Category titles
Category percent weights
Title of each item organized by category
Points earned for each item with a labeled total for each category
Points possible for each item with a labeled total for each category
STEP 3: FORMATTING
FORMAT YOUR GRADEBOOK TO BEST DISPLAY THE DATA (EASY TO READ AND PLEASING TO THE EYE). KEEP IN MIND THE REQUIREMENTS OF THE ENTIRE ASSIGNMENT WHEN FORMATTING.
The following formatting tools must be used appropriately within the spreadsheet design
Boarders
Fill cells
Bold (ctrl + B) or Italic (ctrl + I) or Underline (ctrl + u) (ctrl on PC = COMMAND on Mac)
Cells are formatted to percent where percents need to be displayed with two decimal places
NOTE: For cell reference to work in formula or function, a cell must hold numbers only. No words or signs such as $, %, &< etc. in the cell STEP 4: FORMULAS & FUNCTIONS CREATE FORMULAS AND USE FUNCTIONS TO CALCULATE YOUR GRADE FOR WEIGHTED CATEGORIES (MATH DIRECTIONS BELOW) Conditional Formatting Cell reference must be used in formulas and functions Absolute cell reference must be used appropriately where/if needed AutoSum must be used appropriately where/if needed Add functions: AVERAGE, MINIMUM, MAXIMUM of points earned for each category Math Instructions for Weighted Grades: For this course your course grade is weighted by category. Here is how the math goes for your Weighted Gradebook. Sum the total points earned for each category Sum total points possible for each category Calculate the CATEGORY POINTS PERCENT; Divide the total category points earned by the total category points possible Calculate the WEIGHTED CATEGORY PERCENT CONTRIBUTION; Multiply the product of step 3 by the category weight percent Repeat steps 3-7 for each gradebook category Calculate the TOTAL COURSE GRADE; Sum up the weighted category percent for each category STEP 5: DASHBOARD OF KPIS Think of a car. The dashboard displays the status of the key information needed by the driver to operate the car. This is the same for our Gradebook! Your dashboard should display charts that communicate the key information concerning your grade/performance in the course. Also, it needs to be linked to the data on your gradebook page. Module 5 of the Introductory course textbook explains consolidating data in Excel. In business, Dashboards provide the visual communication of Key Performance Indicators (KPIs). Instead of sales numbers, or inventory counts and value, or clicks on the new marketing campaign from all marketing channels, grades are the data used to monitor performance for our course. What do you want to monitor as a KPI in order to reach the goal of getting the most from the course? The goal of your Dashboard is to tell the story of your data - we usually call this analyzing data. Each chart used within your dashboard is a visual display of the data analysis. DASHBOARD reports have THREE layers: Data (like a table, Textbook Module 7 & 8) Analysis of the data (formulas and functions applied to data transforming the data into information) Presentation or visual display and organization of the report (your charts) Remember in MRCH.1950 where we created the two small data sets to analyzed the greadebook data? They were the Min, Max, Ave data set and the Final Grade data set displaying and calculating your final grade. These two parts of the MRCH.1950 gradebook are the data analysis layer of the gradebook. The data portion are the gradebook item names, points earned, and points possible. DASHBOARD VIDEO: EVALUATION (SEE RUBRIC): When working on course assignments your number one priority is following directions, this is what you will be graded on. Following directions will achieve the SLOs. ✏️ Student Learning Outcomes Demonstrate competency in using spreadsheet technology (Excel) entering appropriate retail math formulas. Exhibit strong information literacy skills by looking up values in a table. Show expertise in planning and executing a macro to maintain table data. Demonstrate technical proficiency by enhancing charts to improve the user's understating of the information displayed. Combine strong critical thinking skills with information literacy by using the data in PivotTables to make decisions.