Questions/Problems
Question 1: Formatting and charts (COs 1, 2, and 3; 30 points)
Enter your name and today’s date on the Documentation sheet
Save your work (CTRL+s).
Select the Q1&Q2 sheet, and adjust the widths of the columns in order to display all data.
Center the title “Music on Demand – Sales by Product” across columns A-D.
Increase the font size to 16, and change the font color to red.
Bold the column headings “Product”, “Unit Price”, “Quantity”, and “Total Sales”.
Add formulas to the Total Sales column to calculate the total sales for each product.
Add a grand total at the bottom of the Total Sales and Quantity columns.
Format grand totals so that they are easily read.
Format the numbers in the Unit Price and Total Sales columns as accounting or currency format with a dollar sign and two decimal places.Format appropriately.
Correct spelling by using the Spell Check feature.
Create a 3-D pie chart on a separate sheet (Chart1) that displays the total sales for each product. (Do not include the grand total in the pie chart!)
Move the chart to a new worksheet titled “Chart 1”.
Make the title of the chart “Total Sales by Product”.
Place each product name and its percentage of sales at or inside its pie slice, with product names off to the right as well.
Move the Chart1 pie chart worksheet immediately after the Quest 1&2 sheet.
Question 2: Formulas, applications and statistical functions (COs 1, 4 &9; 25 points)
Using the ‘Quest 1 & 2’ worksheet.
Draw a box around the table at A22-B25, with a light shading color for the column labels.
In cell E18, add a formula using a lookup function that will look up the grand total in cell D18 in the table of sales and assessments in A22:B25, and display the corresponding assessment. (For example, if the grand total of $505 is displayed in cell D18, “Good” should be displayed in cell E18.)
In cell E19 add the label Highest Sales. In F19 add the function formula for Maximum based on the Total Sales column.
In cell E20 add the label Lowest Sales. In F20 add the function formula for Minimum based on the Total Sales column.
In cell E21 add the label Average Sales. In F21 add the function formula for Average based on the Total Sales column.
Place a border around the statistics data and widen columns as needed for readability.
Add a brief comment to cell F19 to point to the highest in sales.
Save your work (CTRL+s).
Question 3: Lists, sorting, charts, and conditional formatting (Excel; COs 1, 4 and 6; 35 points)
Using the ‘Quest 1 & 2’ Worksheet:
Copy the list of products, unit prices, quantities, total sales, Ship Fromin A3:E17 into the Quest 3 – Lists & Sorting sheet starting in cell A1.
Column E contains each Ship From City and State. Insert two empty columns to the right of column E, and use an Excel feature to separate the city and state into the new columns. In the first row, label the new columns Ship From City and Ship From State. After doing this, delete the original Ship From column. Resize the columns.
Convert the data into a table.
Using the ‘Quest 3 – Lists & Sorting’ worksheet:
Sort the table into descending order (highest to lowest) by Total Sales.
Apply conditional formatting to the Total Sales column so that sales greater than $1,000 are highlighted in green.
Convert the table back into a range. (Important)
Create a pivot table using the table data from Quest 1 & 2 on a new sheet and name it MoD-3b.Then move it after ‘Quest 3 Lists & Sorting’.Create a pivot showing the list of products in alphabetical order, showing total sales by product with appropriate currency formatting.
Save your work (CTRL+s).
Question 4: Functions and Financials (CO’s 2, 6, and 7;35 points)
Using the Quest 4-Financials worksheet:
Complete the MP3 Player analysis section provided by adding formulas (referenced shaded cell locations) and using the product and Total Sales data from the Quest 1 & 2 worksheet.Note: Do not just re-key the data, but use references from the original data.
Format the Total Revenue, Expenses, and Net Income in bold, and choose a fill color for the Net Income.
Perform a one-way analysis (i.e., Week5, iLab 6) to see at what sales volume level MP3 player sales add at least $1,000 to net income.Apply conditional formatting to the options that produce at least $1,000 in net income in the above table.
Format all the numeric data and labels on the worksheet using consistent and business like formatting options.
Save your work (CTRL+s).
Music on Demand (MoD) wants to apply for a 20-year loan and they need to know how much the monthly payment will be with a $10,000 down payment or a $20,000 down payment on a loan of $160,000. The annual interest rate is 3% and payment is assumed to be made at the end of the period.
Complete the chart and calculate the monthly payment, using an Excel function.
Secure/Protect, without a password, the Quest 4-Financials worksheet tab.
Save your work (CTRL+s).
Continue using the ‘Quest 4 – Financials’ worksheet:
Question 5: Data Consolidation & Analysis Strategies (CO’s 5, 3, 7, & 8; 35 Points)
Consolidate the information on the Quest 5-Summary sheet from the three (3) worksheets (Q5 -DC Branch, Q5-Houston Branch, and Q5-LA Branch), using consolidation strategies that include:
Group sheets and use the auto-fill to complete the list of months in Column A and add totals for rows and columns.
Apply formatting to column headings and merge and center a title across row 1 for each sheet.
Make sure all Data Types are correctly formatted.
Ungroup sheets.(This is important.) On the Q5 Summary sheet, use 3D formulas to create a summary that displays the totals for each product and quarter over all 3 regions (DC, Houston, and LA).
Create an appropriate chart that shows total sales of each product. Each bar should represent a product, and each individual column should represent sales by Month. Place your chart on the Q6-Report & Summarize sheet. Give the chart an appropriate title.
Save your work (CTRL+s).
Question 6: Analysis and Recommendation (CO’s 8 and 9; 15 Points).
Analyze the results in the chart on Product Sales. On the Quest 6-Report & Summarize sheet, analyze and report on the information:
Write one paragraph identifying and explaining what you have found about the top two selling items and the two lowest selling items.
Write one paragraph offering management a recommendation to improve the business based upon your analysis.
Save and close your Excel file.
Question 7:Documentation Sheet and Summary (Cos 1 and 9); 25 points)
Complete the documentation sheet with the following information:
Purpose of workbook
Sheet titles and s
Class Conclusion:Your final conclusion about completing this course and what you learned (1 paragraph).
Format the Documentation Sheet so that all information is easily readable and styled.
