Excel Capstone Project

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.