Complete the tasks below using Microsoft Excel.You may refer to your notes, the textbook, or other resources (for example, you may search the Web for information).You may not get interactive help from any other person (either in person, via email, text messaging, IM, or other communications channels).

NOTE:Be sure you SPELL CHECK on every worksheet and correct all spelling errors. If you encounter technical issues of any type during the Final Exam, please notify your instructor and contact the appropriate helpdesk.

Getting started

a.Open Excel (either on your desktop or in Citrix)

b.If you are using Citrix, you need to upload the spreadsheet to your virtual drive before you can open it in Excel

c.Open the Practical Exam workbook you downloaded from the Exam page in Canvas.

d.Save the workbook as Lastname_FirstInitial Week8 FinalExam.xlsx.Example: If your name was Jane Doe, the file name would be:DoeJ Week8 FinalExam.xlsx

Question 1: Formatting and charts (Excel; COs 1, 2, & 3; 30 points)

Using the Documentation sheet:

a. Enter your name and today's date.

Using the 'Quest 1 & 2' worksheet:

1. Adjust the widths of the columns as needed so all data is visible.
2. Center the title "Car Part Service - Sales by Product" across columns A-E.
3. Increase the font size to 20, and change the font color to red and BOLD.
4. Bold the column headings "Category", "Product", "Unit Price", "Quantity", and "Total Sales".
5. Add formulas to the Total Sales column to calculate the total sales for each product.
6. Add a grand total at the bottom of the Total Sales column, in cell E18.
7. Format the grand total so that it is easily read.
8. Format the numbers in the Unit Price and Total Sales columns as accounting or currency format with a dollar sign and two decimal places.

j.Correct Spelling by using the Spell Check feature.

1. Column chart on a separate sheet that displays the total sales for each product. (Do not include the grand total in the column chart!)
2. Move the chart to a new worksheet titled "TotalSales". Make the title of the chart "Total CPS Sales by Product".
3. Add a data label showing the amount on the outside of each bar in the chart, not in a separate legend.
4. Move the chart worksheet immediately after the Quest 1 & 2 sheet.
5. Save your work (CTRL+s) - (but leave it open to continue).

Question 2: Formulas, applications and statistical functions (Excel; COs 1, 4 &9; 25 points)

Using the 'Quest 1 & 2' worksheet.

1. Draw a box around the table at A22-B25, with a light shading color for the column labels.Add the label "Assessment" in cell F3 and resize column to a width of 15.
2. In column F, add formulas using a lookup function that will look up each total sales amount in the table of sales and assessments in A22:B25, and display the corresponding assessment.
3. For example, if the Total sales in cell E5 is $479 then "Poor" should display in cell F5.
4. In cell F22 add Label: Highest Sales. In cell G22 add the function formula for Maximum based on the Total Sales column.
5. In cell F23 add Label: Lowest Sales. In cell G23 add the function formula for Minimum based on the Total Sales column.
6. In cell F24 add Label: Average Sales. In cell G24 add the function formula for Average based on the Total Sales column.
7. Place a border around the statistics data and widen columns as needed for readability.
8. Add a comment to G22 indicating that this is the highest sale.
9. Save your work (CTRL+s).

Question 3: Lists, sorting, charts and conditional formatting (Excel; COs 1, 4 & 8; 35 points)

Using the 'Quest 1 & 2' worksheet:

1. Select the list of categories, products, unit prices, quantities, total sales, and assessment, copy, and then use Paste Special to copy values into the Quest 3 - Lists & Sorting sheet starting in cell A1.

Using the Q3 - Lists & Sorting Sheet

1. Column B contains both Product andProduct Number. Insert two empty columns to the right of column E, and use an Excel feature to separate the Product and Product Number into the new columns. In the first row, label the new columns Product and Product Number. After doing this, delete the original Product, Number column. Resize the columns.
2. Convert the data into a table.
3. Sort the table into descending order (highest to lowest) by Total Sales.
4. Apply conditional formatting to the Total Sales column so that sales greater than $1,000 are highlighted in green.
5. Convert the table back into a range. pivot table to filter and only show only the total sales (value) for products (row) with a "poor" assessment organized by category then product.
6. Apply a currency or accounting format to the total sales column.
7. Move the Pivot Table sheet to follow Q3 - Lists.Rename Q3-Pivot.
8. Save your work (CTRL+s).

Question 4: Functions and Financials (Excel TCO's 2, 6, & 7;35 Points)

Using the 'Quest 4-Financials' worksheet

a.Complete the Income Statement by filling in cells D3 through D16 by using cell references of Total Sales column from the Quest1 & 2 worksheet.

Note: Do not just re-key the data.

b.Add formulas for the shaded areas.

c.Format the Total Revenue, Expenses, and Net Income in bold and apply appropriate numeric formatting to all data.

d.Perform a one-way analysis (single data variable table, refer to Week 5, iLab 6) setup in cells G11-O15 to vary the pay rate to see the impact on the total expenses and net income.

e.Apply conditional formatting to the options that produce at least $10,000 in net income in the above table.

Continue using the 'Quest 4-Financials' worksheet:

f.Car Parts Service (CPS) wants to apply for a 10-year loan to expand their operations and they need to know how much the monthly payment will be with a $1,500 down payment or a $2,500 down payment on a loan of $50,000. The annual interest is 3.5% and payment is assumed to be made at the end of the period.

g.Complete the Payment Function and Analysis table (Part B) by calculating the Loan Amount for cells K3 and K4 and the monthly payment amount for cells M3 and M4.

h.Secure/Protect, without a password, the Quest 4-Financials worksheet tab.

i.Save your work (CTRL+s).

Question 5: Data Consolidation and Analysis (Excel CO's 5, 3, 7, & 8; 35 Points)

Using the 'Quest 5 -Data Organization' worksheet, consolidate the information on the CPS Part 5 - Data Organization spreadsheet from the three (3) worksheets (San Francisco Branch, Denver Branch, and Boston Branch), using consolidation strategies that include:

a.Group sheets and apply formatting to column headings and merge and center a title across row 1 for each sheet.

b.Use the auto-fill to complete the list of months in Row 2. Add totals for both rows and columns.

c.Add row and column summary totals on the Quest 5-Organizing Data Worksheet.

d.Apply numeric formatting to the data as appropriate.

e.Ungroup the sheets. Open the summary worksheet, Quest 5- Data Organization and use either data consolidate or 3D formulas to summarize the three locations into a total company report using the same format as each location.

f.Summarize your results using an appropriate chart nd move it to the Quest 6 - Report & Summarize sheet.

g.Include chart formatting features to improve readability.

h.Save your work (CTRL+s).

Question 6: Analysis, Summary and Recommendation (Excel CO's 8 & 9; 15 Points)

Analyze the results from Quest 5 - Data Organization worksheet and report/memo for management with the following:

a.Explain your findings concerning sales, including identifying the top two selling items and the two lowest selling items.

b.Offer management a recommendation to improve the business based upon your analysis.

c.Save your work (CTRL+s) & close your file.

Question 7:Documentation Sheet and Summary (COs 1 & 9; 25 points)

a.Complete the documentation sheet with the following information:

Purpose of workbook

Sheet titles and Descriptions

b.Class Conclusion:Your final conclusion about completing this course and what you learned

c.Format the Documentation Sheet so that all information is easily readable and styled.

To submit your work:

Go back to the Canvas Exam page, upload your completed Lastname_FirstInitial Week8 FinalExam.xlsx file and click the "Submit Quiz" button.If you encounter technical issues of any type during the Final Exam, please notify your instructor and contact DeVry's helpdesk at 1-877-784-1997.