Questions/ProblemsFormatting and charts (TCOs 1, 2, and 3; 30 points)Adjust the widths of the columns

so all data is visible.

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.

• Save your work (CTRL+s).

Formulas, applications and statistical functions (TCOs 2, 3, 6, and 10; 30 points)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. Save your work (CTRL+s).Lists, sorting, charts, and conditional formatting (Excel; TCOs 4 and 6; 40 points)Convert the list of products, unit prices, quantities, and total sales in A3:D17 into a table on the Quest 3 – Lists & Sorting sheet starting in cell A1.

Using the ‘Quest 3 – Lists & Sorting’ worksheet:

1. Sort the table into descending order (highest to lowest) by Total Sales.
2. Apply conditional formatting to the Total Sales column so that sales greater than $1,000 are highlighted in green.
3. 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. 
4. Create, on this same sheet, a line graph using the sales data from the pivot table.  Add chart title and labels as appropriate.
5. Save your work (CTRL+s).

• Functions and Financials  (TCO’s 2, 6, and 7;  30 points)

Using the Quest 4-Financials worksheet:

• Complete the Profit and Loss Statement provided by adding formulas (referenced shaded cell locations) and using the product and Total Sales data already included in Quest 1 & 2.  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).

Continue using the ‘Quest 4 – Financials’ worksheet: 

• 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).

• Organization, Planning and Consolidation Strategies (TCO’s 5, 7, & 8; 40 Points)
• Consolidate the information on the Quest 5-Organizing Data sheet from the three (3) worksheets (DC Branch, Houston Branch, and 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.
  • Open the blank worksheet, Quest 5-Organizing Data, and on the data tab click consolidate.
  • Choose the sum function to sum the data.
  • Add each worksheet to the consolidated Summary.
  • As an alternative to steps 3-5 above, use 3-D referencing to summarize the data.
  • Add row and column summary totals on the Quest 5-Organizing Data Worksheet.
• Summarize your results using an appropriate chart type and place it below the summarized data.
• Spell check the worksheet.
• Save your work (CTRL+s).

• Analysis, Summary and Recommendation (TCO’s 8, and 9; 30 Points).

Analyze the results from Quest 5-Organizing Data worksheet and create a report/memo with the following:

• Identify the top two selling items and the two lowest selling items.
  • Add a Chart/Table (s) to reflect your results – or use the chart created in step 5
  • Offer management a recommendation to improve the business based upon your analysis.
  • List at least two Lessons Learned from your course experience.
  • Save and close your Excel file.

• Attachment 1
• Attachment 2

 ATTACHMENT PREVIEW Download attachmentBIS155 Practical Final Exam Instructions-2 V3Complete the tasks below using Microsof Excel.You may reFer to your notes, the textbook, or otherresources such as the Web You maynotget interacTve help From any other person, either in person orvia email, text messaging, instant messaging, or other communicaTons channels. Istronglyrecommend that you save your work Frequently.You have 4 hours to complete this exam.Note: Be sure to spell check on every worksheet and correct all spelling errors.Ge±ng starteda.Open Excel (either on your desktop or in Citrix)b.IF you are using Citrix, you need to upload the spreadsheet to your virtual drivebeFore you can open it in Excelc.Open the PracTcal Exam spreadsheet you downloaded From the course Exam page.Save the spreadsheet asLastnameFirstIniTal Week8 FinalExam.xlsx. (i.e., iF yourname were Jane Doe, your ²le name would be:DoeJ Week8 FinalExam.xlsx)Questons/Problems1.³orma±ng and charts (´COs 1, 2, and 3; 30 points)Using the DocumentaTon sheet:a. Enter your name and today’s date.Using the ‘Quest 1 & 2’ worksheet:b.Adjust the widths oF the columns so all data is visible.c.Center the Ttle “Music on Demand – Sales by Product” across columns A-D.d.Increase the Font size to 16, and change the Font color to red.e.Bold the column headings “Product”, “Unit Price”, “QuanTty”, and “´otal Sales”.F.Add Formulas to the ´otal Sales column to calculate the total sales For each product.g.Add a grand total at the boµom oF the ´otal Sales and QuanTty columns.h.³ormat grand totals so that they are easily read.i.³ormat the numbers in the Unit Price and ´otal Sales columns as accounTng or currencyFormat with a dollar sign and two decimal places.³ormat appropriately.j.Correct spelling by using the Spell Check Feature.k.Create a 3-D pie chart on a separate sheet (Chart1) that displays the total sales For eachproduct. (DonoTinclude the grand total in the pie chart!)l.Move the chart to a new worksheet Ttled “Chart 1”.m. Make the Ttle oF the chart “´otal Sales by Product”.n.Place each product name and its percentage oF sales at or inside its pie slice, withproduct names o¶ to the right as well.Copyright © 2016 by DeVry Educational Development Corporation.All rights reserved.No part of this work may be reproduced or used in any form or by any means – graphic, electronic, ormechanical, including photocopying, recording, Web distribution or information storage and retrieval systems – without the priorconsent of DeVry Educational Development Corporation.Page1of4v-3

View the AnswerBIS155 Practical Final Exam Instructions-2 V3o.Move the Chart1 pie chart worksheet immediately afer the Quest 1&2 sheet.p.Save your work (CTRL+s).2.Formulas, applica±ons and sta±s±cal ²unc±ons (TCOs 2, 3, 6, and 10; 30 points)Using the ‘Quest 1 & 2’ worksheet.a.Draw a box around the table at A22-B25, with a light shading color ²or the column labels.b.In cell E18, add a ²ormula using a lookup ²unc±on that will look up the grand total in cellD18 in the table o² sales and assessments in A22:B25, and display the correspondingassessment. (For example, i² the grand total o² $505 is displayed in cell D18, “Good”should be displayed in cell E18.)c.In cell E19 add the label Highest Sales. In F19 add the ²unc±on ²ormula ²or Maximumbased on the Total Sales column.d.In cell E20 add the label Lowest Sales. In F20 add the ²unc±on ²ormula ²or Minimumbased on the Total Sales column.e.In cell E21 add the label Average Sales. In F21 add the ²unc±on ²ormula ²or Averagebased on the Total Sales column.².Place a border around the sta±s±cs data and widen columns as needed ²or readability.g.Save your work (CTRL+s).3.Lists, sor±ng, charts, and condi±onal ²orma³ng (Excel; TCOs 4 and 6; 40 points)Using the ‘Quest 1 & 2’ Worksheet:a.Convert the list o² products, unit prices, quan±±es, and total sales in A3:D17 into a tableon the Quest 3 – Lists & Sor±ng sheet star±ng in cell A1.Using the ‘Quest 3 – Lists & Sor±ng’ worksheet:b.Sort the table into descending order (highest to lowest) by Total Sales.c.Apply condi±onal ²orma³ng to the Total Sales column so that sales greater than $1,000are highlighted in green.d.Create a pivot table using the table data ²rom Quest 1 & 2 on a new sheet and name itMoD-3b.Then move it afer ‘Quest 3 Lists & Sor±ng’.Create a pivot showing the list o²products in alphabe±cal order, showing total sales by product with appropriate currency²orma³ng.e.Create, on this same sheet, a line graph using the sales data ²rom the pivot table.Addchart ±tle and labels as appropriate.².Save your work (CTRL+s).Copyright © 2016 by DeVry Educational Development Corporation.All rights reserved.No part of this work may be reproduced or used in any form or by any means – graphic, electronic, ormechanical, including photocopying, recording, Web distribution or information storage and retrieval systems – without the priorconsent of DeVry Educational Development Corporation.Page2of4v-3

Show entire document

 

Looking for a Similar Assignment? Order now and Get 10% Discount! Use Coupon Code "Newclient"