Need help with this exam due in 3 hours need help stuck at work cant figure it out in time
Need help with this exam due in 3 hours need help stuck at work cant figure it out in time
ATTACHMENT PREVIEW Download attachmentBIS155 Practical Final Exam Instructions-3Complete 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 Exam page ineCollege.Save the spreadsheet asLastname_FirstIniTal 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 and 3; 40 points).a.Enter your name and today’s date on the DocumentaTon sheet.b.Select the Q1&Q2 sheet, and adjust the widths oF the columns as needed.c.Center the Ttle “Super Shoes, Inc. Sales by Product” across columns A-D, increase theFont size to 16 and change the Font color to blue.d.Bold the column headings “Product”, “Unit Price”, “QuanTty”, and “´otal Sales”.e.Add Formulas to the ´otal Sales column to calculate the total sales For each product.F.Add a grand total at the boµom oF the ´otal Sales and QuanTty columns.³ormat totalsand column headings so that they are easily read.g.³ormat the numbers in the Unit Price and ´otal Sales columns as accounTng or currencyFormat with a dollar sign and two decimal places.h.Create a 3-D column chart on a separate sheet (Chart1) that displays the total sales Foreach product. (Do not include the grand total in the chart!) Make the Ttle oF the chart”´otal Sales by Product”. Put the sheet with the column chart immediately afer theQ1&Q2 sheet.i.Remember to save your work ofen.2.³ormulas and applicaTons (´COs 2 and 7; 40 points).a.On the Q1&Q2 sheet, in cell E8, add a Formula using a lookup FuncTon that will look upthe grand total in cell D8 in the table oF sales and assessments in A11:B13, and displaythe corresponding bonus percentage. Use a range lookup. (³or example, iF the grandtotal oF $12,500 is displayed in cell D8, 5% should be displayed in cell E8.)b.In cell E9 enter the label Highest Sales. In ³9 enter a Formula using a FuncTon to displaythe highest total sales value For any product.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-3c.In cell E10 enter the label Lowest Sales. In F10 enter a formula using a funcTon to displaythe lowest total sales value for any product.d.In cell E11 enter the label Average Sales. In F11 enter a formula using a funcTon todisplay the average total sales value for all products.e.Format the labels and staTsTcs (Highest Sales, Lowest Sales, and Average Sales) todisplay an outline (exterior border) around these cells for readability.f.Add comments to cells F9, F10, and F11. In your comments, explain brie±y in your ownwords what is displayed in each cell.g.Secure the worksheet, without a password, so the contents cannot be accidentallychanged by a user.h.Remember to save your work o²en.3.Data cleansing, lists, sorTng, condiTonal forma³ng, and pivot tables (´COs 4 and 8; 40points)a.On the Q3 sheet, adjust the widths of the columns as needed.b.Column H contains each customer’s complete phone number. In column I, use an Excelfeature or funcTon to extract and display just the area code. Enter the heading “AreaCode” at the top of this column.c.Convert the list of customers into an Excel table.d.Sort the table into ascending alphabeTcal order by Last Name.e.Apply condiTonal forma³ng to the State column so that cells containing “´exas” arehighlighted in red and cells containing “California” are highlighted in blue.f.Convert the table back into a range.g.Create a pivot table that uses State as the row µeld, no column µeld, and the count ofCustomer ID as the values. Put your pivot table on a new sheet and place this sheetimmediately a²er the Q3 sheet, labeled Q3-Pivot.h.Remember to save your work o²en.4.Data consolidaTon, analysis, and reporTng (´COs 5 and 9; 40 points)a.Group the four sheets Q4 New York, Q4 Chicago, Q4 Los Angeles, and Q4 Summary.Note: You may choose to useConsolidatoninstead ofGroup Sums, but the instrucTonsbelow are not for theConsolidatonfuncTon, and you will need to create totals.b.While the sheets are grouped, bold the labels in row 1 and column A, and format thevalues in cells B2:E5 as currency with no decimal places.c.Ungroup the sheets (very important).d.On the Q4 Summary sheet, create a summary table that displays the totals for eachproduct and quarter over all three ciTes (New York, Chicago, and Los Angeles).Noteagain, that you can use either the consolidate or group sum funcTon for this task.e.Create a clustered column chart that shows total sales of each product in each quarter.Each cluster should represent a quarter, and each individual column should representsales of a product within that quarter. Place your chart on the Q4 Summary sheet to theright of the data. Give the chart an appropriate Ttle.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
Looking for a Similar Assignment? Order now and Get 10% Discount! Use Coupon Code "Newclient"
