Part 1
9/9/18
8:40:49 AM
Generate Data
Part 2
9/9/18
8:23:08 AM
Generate Data
Part 3
9/9/18
9:18:51 AM
285407
0.045
17
Generate Data
DinnerCosts
Dinner Price per Dinner
Burgers $ 10.99
Chicken $ 13.99
Fish and Chips $ 11.99
Meatloaf $ 15.99
Pasta $ 16.99
Pork Chops $ 20.99
Ribs $ 25.99
Steak $ 30.99
Turkey $ 14.99
Excel Capstone Project
The project will evaluate the following Excel skills:
Use of Excel functions/formulas
Creating different types of graphs
Pivot tables
What If Analysis (Data Table)
Formatting
Cell referencing (absolute, relative, mixed)
Points possible: 150
Note, there will be only one graded attempt for this project.
Students will submit their Excel file through Canvas Assignments.
Note, students are to work on this project by themselves. No sharing of work.
Students are to maintain academic honesty and professionalism while working on
this
project and possible other academic ramifications. If you have any questions
regarding this please let me know.
The work needs to be clear and well organized. Points will be deducted for work that is
not clear or missing.
Clearly label each part of the project.
All the data for the project can be found in the Excel file: Excel_Project_Data.
Note, this project is the property of Dr. Marcy Jance (business faculty member at
Indiana University East). Under no circumstances is the project to be posted to any
online web site or used outside of this class without the permission of Dr. Marcy
Jance.
Part 1: Sales Data (60 points)
Step 1: Press the button Generate Data. This will populate columns A through B with data.
Please note you must press this button. Do not copy data from another place. I will be checking
to see if you indeed pressed the button to generate your own unique data for the project! Do not
sort the data or add to it in any way.
(Note: Save the project as LastName_FirstName_Excel_Capstone.xlsx)
Step 2: Complete the following on the Part 1 worksheet.
1. You will create a summary table that includes the following for each month of the year:
(10 points).
Here is an example showing the first three months of the year.
Use functions SUMIFS and AVERAGEIFS to determine the total and average sales per month
(20 points)
2. Create a pivot table on a new page that shows the total, average, min, and max sales for
each month. Change the tab title to Pivot Table. (15 points).
3. Create a Line chart showing the total sales per month. Add axis titles and data points
(10 points).
4. Cell referencing and formatting: Make sure all amounts are probably formatted. Use
bold appropriately for headings. Put a border around your summary table results. Use
appropriate cell referencing (e.g. absolute, relative, mixed) when setting up the functions
(5 points).
Part 2: Favorite Dinners (60 points)
Step 1: Press the button Generate Data. This will populate column A with data. Please note
you must press this button. Do not copy data from another place. I will be checking to see if you
indeed pressed the button to generate your own unique data for the project!
Step 2: Complete the following on the Part 2 worksheet.
1. You will create a summary table that shows for each dinner type: the number sold,
price, and total sales. Here is an example of a table showing the first two dinner types
(chicken and steak).
Dinner Number Sold Price Total Sales
Chicken
Steak
Use the COUNTIF function to determine the number of each dinner purchased. Then use a
VLOOKUP function to show the price of each dinner type. The dinner prices are in the
DinnerCosts worksheet. Then setup an Excel formula (or function) to determine the total sales
for each dinner type (30 points).
2. Create a Column Chart showing the total dollar amount sold for each dinner type.
Include axis titles and data labels (10 points).
3. Create a Pie Chart showing the percentage of each dinner type sold. Include a
legend and data labels showing the percentages (10 points).
4. Cell referencing and formatting: Make sure all amounts are probably formatted. Use
bold appropriately for headings. Put a border around your summary table results. Use
appropriate cell referencing (e.g. absolute, relative, mixed) when setting up the
functions (10 points).
Part 3: Determining the House Payment and What If Analysis (30
points)
Step 1: Press the button Generate Data. This will populate columns A and B with data. Please
note you must press this button. Do not copy data from another place. I will be checking to see if
you indeed pressed the button to generate your own unique data for the project!
Step 2: Complete the following on the Part 3 worksheet.
1. Determine the monthly house payment using the appropriate Excel function. The
rate, years of the mortgage, and loan amount are generated when pressing the button
(6 points).
2. Using the What If Analysis tool, construct a two-way data table that will determine
what your monthly house payment will be for rates of 0.03, 0.035, 0.04, 0.045, 0.05,
0.055, 0.06, and 0.065 and years: 5, 10, 15, 20, 25, and 30 (18 points).
3. Make sure all rates and amounts are appropriately formatted (6 points).
Save the completed project as LastName_FirstName_Excel_Capstone.xlsx.