Complete the tasks below using Microsoft Excel. You may refer to your notes, the textbook, or other resources such as the Web You may not get interactive help from any other person, either in person or via email, text messaging, instant messaging, or other communications channels. I strongly recommend that you save your work frequently.\tYou have 4 hours to complete this exam.\tNote: Be sure to spell check on every worksheet and correct all spelling errors.\tGetting started\tOpen Excel (either on your desktop or in Citrix)\tIf you are using Citrix, you need to upload the spreadsheet to your virtual drive before you can open it in Excel\tOpen the Practical Exam spreadsheet you downloaded from the course Exam page.Save the spreadsheet as LastnameFirstInitial Week8 FinalExam.xlsx. (i.e., if your name were Jane Doe, your file name would be: DoeJ Week8 FinalExam.xlsx)\tQuestions\/Problems\tFormatting and charts (TCOs 1, 2, and 3; 30 points) Using the Documentation sheet:\ta. Enter your name and today’s date.\t\u00a0\tUsing the “\ufffdQuest 1 & 2′ worksheet:\t\u00a0\tAdjust the widths of the columns so all data is visible.\tCenter the title “Music on Demand – Sales by Product” across columns A-D.\tIncrease the font size to 16, and change the font color to red.\tBold the column headings “Product”, “Unit Price”, “Quantity”, and “Total Sales”.\tAdd formulas to the Total Sales column to calculate the total sales for each product.\tAdd a grand total at the bottom of the Total Sales and Quantity columns.\tFormat grand totals so that they are easily read.\tFormat 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.\tCorrect spelling by using the Spell Check feature.\tCreate 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!)\tMove the chart to a new worksheet titled “Chart 1″\ufffd.\tMake the title of the chart “Total Sales by Product”.\tPlace each product name and its percentage of sales at or inside its pie slice, with product names off to the right as well.\tMove the Chart1 pie chart worksheet immediately after the Quest 1&2 sheet.\tSave your work (CTRL+s).\tFormulas, applications and statistical functions (TCOs 2, 3, 6, and 10; 30 points) Using the “\ufffdQuest 1 & 2′ worksheet.\tDraw a box around the table at A22-B25, with a light shading color for the column labels.\tIn 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.)\tIn cell E19 add the label Highest Sales. In F19 add the function formula for Maximum based on the Total Sales column.\tIn cell E20 add the label Lowest Sales. In F20 add the function formula for Minimum based on the Total Sales column.\tIn cell E21 add the label Average Sales. In F21 add the function formula for Average based on the Total Sales column.\tPlace a border around the statistics data and widen columns as needed for readability.\tSave your work (CTRL+s).\tLists, sorting, charts, and conditional formatting (Excel; TCOs 4 and 6; 40 points) Using the “\ufffdQuest 1 & 2′ Worksheet:\tConvert the list of products, unit prices, quantities, and total sales in A3:D17 into a table on the Quest 3 “\ufffd Lists & Sorting sheet starting in cell A1.\tUsing the “\ufffdQuest 3 “\ufffd Lists & Sorting’ worksheet:\tSort the table into descending order (highest to lowest) by Total Sales.\tApply conditional formatting to the Total Sales column so that sales greater than $1,000 are highlighted in green.\tCreate a pivot table using the table data from Quest 1 & 2 on a new sheet and name it MoD-3b. Then move it after “\ufffdQuest 3 Lists & Sorting’. Create a pivot showing the list of products in alphabetical order, showing total sales by product with appropriate currency formatting.\tCreate, on this same sheet, a line graph using the sales data from the pivot table. Add chart title and labels as appropriate.\tSave your work (CTRL+s).\tFunctions and Financials (TCO’s 2, 6, and 7; 30 points)\tUsing the Quest 4-Financials worksheet:\tComplete 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.\t\u00a0\tFormat the Total Revenue, Expenses, and Net Income in bold, and choose a fill color for the Net Income.\tPerform 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.\tFormat all the numeric data and labels on the worksheet using consistent and business like formatting options.\tSave your work (CTRL+s).\tContinue using the “\ufffdQuest 4 “\ufffd Financials’ worksheet:\tMusic 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.\tComplete the chart and calculate the monthly payment, using an Excel function.\tSecure\/Protect, without a password, the Quest 4-Financials worksheet tab.\tSave your work (CTRL+s).\tOrganization, Planning and Consolidation Strategies (TCO’s 5, 7, & 8; 40 Points) Using the “\ufffdQuest 5 “\ufffd Organizing Data’ worksheet:\tConsolidate 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:\tGroup sheets and use the auto-fill to complete the list of months in Column A and add totals for rows and columns.\tApply formatting to column headings and merge and center a title across row 1 for each sheet.\tOpen the blank worksheet, Quest 5-Organizing Data, and on the data tab click consolidate.\tChoose the sum function to sum the data.\tAdd each worksheet to the consolidated Summary.\tAs an alternative to steps 3-5 above, use 3-D referencing to summarize the data.\tAdd row and column summary totals on the Quest 5-Organizing Data Worksheet.\tSummarize your results using an appropriate chart type and place it below the summarized data.\tSpell check the worksheet.\tSave your work (CTRL+s).\tAnalysis, Summary and Recommendation (TCO’s 8, and 9; 30 Points).\tAnalyze the results from Quest 5-Organizing Data worksheet and create a report\/memo with the following:\tIdentify the top two selling items and the two lowest selling items.\tAdd a Chart\/Table (s) to reflect your results “\ufffd or use the chart created in step 5\tOffer management a recommendation to improve the business based upon your analysis.\tList at least two Lessons Learned from your course experience.\tSave and close your Excel file.<\/p>\n","protected":false},"excerpt":{"rendered":"
Complete the tasks below using Microsoft Excel. You may refer to your notes, the textbook, or other resources such as the Web You may not get interactive help from any other person, either in person or via email, text messaging, Read More …<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-202964","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/202964","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/comments?post=202964"}],"version-history":[{"count":0,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/202964\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/media?parent=202964"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/categories?post=202964"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/tags?post=202964"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}