Complete the tasks below using Microsoft Excel. You may refer to your notes, the textbook, or other resources such as the WebYou may not get interactive help from any other person (either inperson or via email, text messaging, instant messaging, or other communications channels).I strongly recommend 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.

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 spreadsheet you downloaded from the Exam page in eCollege. 

Save the spreadsheet as Lastname_FirstInitial Week8 FinalExam.xlsx. (i.e., if your name were Jane Doe, your file name would be: DoeJ Week8 FinalExam.xlsx)

Questions/Problems

1.      Formatting and charts (TCOs 1 and 3; 40 points).

a.      Enter your name and today's date on the Documentation sheet.

b.      Select the Q1&Q2 sheet, and adjust the widths of the columns as needed.

c.       Center the title "Super Shoes, Inc. Sales by Product"across columns A-D, increase the font size to 16 and change the font color to blue.

d.      Bold the column headings "Product", "Unit Price", "Quantity", and "Total Sales".

e.      Add formulas to the Total Sales column to calculate the total sales for each product.

f.        Add a grand total at the bottom of the Total Sales and Quantity columns.  Format totals and column headings so that they are easily read.

g.      Format the numbers in the Unit Price and Total Sales columns as accounting or currency format 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 for each product. (Do not include the grand total in the chart!) Make the title of the chart "Total Sales by Product".Put the sheet with the column chart immediately after the Q1&Q2 sheet.

i.        Remember to save your work often.

2.      Formulas and applications (TCOs 2 and 7; 40 points).

1. On the Q1&Q2 sheet, in cell E8, add a formula using a lookup function that will look up the grand total in cell D8 in the table of sales and assessments in A11:B13, and display the corresponding bonus percentage. Use a range lookup. (For example, if the grand total of $12,500 is displayed in cell D8, 5% should be displayed in cell E8.)
2. In cell E9 enter the label Highest Sales. In F9 enter a formula using a function to display the highest total sales value for any product.
3. In cell E10 enter the label Lowest Sales. In F10 enter a formula using a function to display the lowest total sales value for any product.
4. In cell E11 enter the label Average Sales. In F11 enter a formula using a function to display the average total sales value for all products.
5. Format the labels and statistics (Highest Sales, Lowest Sales, and Average Sales) to display an outline (exterior border) around these cells for readability.
6. Add comments to cells F9, F10, and F11. In your comments, explain briefly in your own words what is displayed in each cell.
7. Secure the worksheet, without a password,so the contents cannot be accidentally changed by a user.
8. Remember to save your work often.

3.      Data cleansing, lists, sorting, conditional formatting, and pivot tables (TCOs 4and8; 40 points)

1. On the Q3 sheet, adjust the widths of the columns as needed.
2. Column H contains each customer’s complete phone number. In column I, use an Excel feature or function to extract and display just the area code. Enter the heading “Area Code”at the top of this column.
3. Convert the list of customersinto an Excel table.
4. Sort the table into ascending alphabetical order by Last Name.
5. Apply conditional formatting to the State column so that cells containing “Texas” are highlighted in red and cells containing “California” are highlighted in blue.
6. Convert the table back into a range.
7. Create a pivot table that uses State as the row field, no column field, and the count of Customer ID as the values. Put your pivot table on a new sheet and place this sheet immediately after the Q3 sheet, labeled Q3-Pivot.
8. Remember to save your work often.

4.      Data consolidation, analysis, and reporting (TCOs 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 use Consolidation instead of Group Sums, but the instructions below are not for the Consolidation function, and you will need to create totals.

b.      While the sheets are grouped, bold the labels in row 1 and column A, and format the values 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 tablethat displays the totals for each product and quarter over all threecities (New York, Chicago, and Los Angeles).  Note again, that you can use either the consolidate or group sum function 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 represent sales of a product within that quarter. Place your chart on the Q4 Summary sheet to the right of the data. Give the chart an appropriate title.

f.        Add a trend line to the chart to show how sales of cross trainers are changing over time.

g.      In the space provided on the Q4 Summary sheet, write a brief (2-3 paragraph) analysis report to the manager of the Super Shoes business. In your report, explain your findings on the trend in sales of cross trainers, and any other patterns you observed in sales of the product categories; and recommend at least one specific action that Super Shoes should take regarding the cross trainers product line.

8. Remember to save your work often.

5.      What-if analysis (TCO 6; 20 points)

a.      On the Q5 sheet, enter formulas in cells B6, B7, and B8 to calculate the total sales, total cost, and net income for a new product line, based on the projected unit sales, unit price, and unit cost provided.  Format the Totals and Net Income appropriately.

b.      Adjust column widths as needed, and format all values except unit sales as currency with no decimal places.  Apply formatting features to display in a professional fashion.

c.       Use the Scenario Manager to create three financial scenarios for this product: Most Likely, Best Case, and Worst Case. For the Most Likely scenario, unit sales are 50 units, unit price is $75, and unit cost is $45. For the Best Case scenario, unit sales are 100 units, unit price is $99, and unit cost is $40. For the Worst Case scenario, unit sales are 30 units, unit price is $60, and unit cost is $50. For all scenarios, results to calculate are Total sales, Total cost, and Net income.

d.      Create a ‘Scenario Summary’ report sheet showing results for all three scenarios (Week 5, iLab 6). Place the Scenario Summary report sheet immediately after the Q5 sheet.  Format all output appropriately.

5. Remember to save your work often.

6.      Statistical analysis and regression (TCO 10; 20 points)

a.      On the Q6 sheet, you will find a data set showing the number of units sold in each month of the previous year and the corresponding shipping costs (dollars) in the month. Using the Regression tool in the Analysis Toolpak add-in, perform a linear regression of shipping costs versus unit sales. Place the output of the regression analysis on the Q6 sheet, starting in cell E1.

b.      Is there a significant relationship(Goodness of Fit)between units sold and shipping costs? State and explain your answer in the space provided on the Q6 sheet. Use specific results from the regression to support your answer.

c.       The sales manager projects sales of 500 units next month. In cell B17, enter a formula to calculate the sales you project will result from this level of advertising.(Hint: Y = A + bX)Use results from the regression analysis in your formula.

d.      Save and close your Excel file.

To submit your work:

Upload your completed LastnameFirstInitial Week8 FinalExam.xlsx. file to the Week 8 Final Exam dropbox on our course site. Make sure that your submission is showing in the Outbox with the file icon.

Go back to the eCollege Exam page, answer the exam question with TRUE and click “Submit for Grading”.

You may also wish to send an email to the instructor as confirmation of your submission.

• 1513559482-BIS155_Practical_Exam_Spreadsheet-3 1.xlsx

SmartExpert

(118)

Status NEW Posted 17 Dec 2017 11:12 PM My Price 25.00

BIS----------- 15-----------5 F-----------ina-----------l E-----------xam----------- Pr-----------act-----------ica-----------l P-----------rob-----------lem-----------s A-----------nsw-----------er-----------

Attachments

file 1513559488-BIS 155 Final Exam Practical Problems Answer.xlsx preview (195 words )

BI-----------S-1-----------55 -----------Fin-----------al -----------Exa-----------m P-----------rac-----------tic-----------al -----------Pro-----------ble-----------msN-----------ame-----------:Da-----------te:-----------Pro-----------duc-----------tUn-----------it -----------Pri-----------ceQ-----------uan-----------tit-----------yTo-----------tal----------- Sa-----------les-----------Sal-----------esA-----------sse-----------ssm-----------ent-----------Poo-----------rGo-----------odO-----------uts-----------tan-----------din-----------g!M-----------usi-----------c o-----------n D-----------ema-----------nd------------Sal-----------es -----------by -----------Pro-----------duc-----------tVi-----------nal----------- Al-----------bum-----------sCa-----------sse-----------tts-----------Dig-----------ita-----------lSi-----------ngl-----------e S-----------ong-----------s ----------- Sa-----------les-----------:To-----------tal----------- Re-----------ven-----------ueV-----------ari-----------abl-----------e E-----------xpe-----------nse-----------sPo-----------sta-----------geH-----------and-----------lin-----------gSu-----------b T-----------ota-----------l V-----------ari-----------abl-----------e E-----------xpe-----------nse-----------sFi-----------xed----------- Ex-----------pen-----------ses-----------Ins-----------ura-----------nce-----------Mai-----------nte-----------nan-----------ceA-----------dmi-----------nis-----------tra-----------tiv-----------e a-----------nd -----------Mar-----------kei-----------ngT-----------axe-----------sSu-----------bTo-----------tal----------- Fi-----------xed----------- Ex-----------pen-----------ses-----------Tot-----------al -----------Exp-----------ens-----------esS-----------umm-----------ary-----------:Ne-----------t I-----------nco-----------meA-----------nnu-----------al -----------Rev-----------enu-----------e:A-----------nnu-----------al -----------Exp-----------ens-----------es:-----------Hea-----------dse-----------tsS-----------pea-----------ker-----------sWi-----------rin-----------gOu-----------tdo-----------or

Not Rated(0)

• Buy Answer