Question Details

[answered] BIS-155 Final Exam Practical Problems Super Shoes, Inc (Exa


Complete the tasks below using Microsoft Excel.?? You may refer to your notes, the textbook, or other resources (for example, you may search the Web for information).? You may not get interactive help from any other person (either in person, via email, text messaging, IM, or other communications channels).

Save your work frequently using CTL+s.?? You have 4 hours to complete this exam.

NOTE:? Be sure you SPELL CHECK on every worksheet and correct all spelling errors.? If you encounter technical issues of any type during the Final Exam, please notify your instructor and contact the appropriate helpdesk (877-784-1997).

Getting started

  • Open Excel (either on your desktop or in Citrix).
  • If you are using Citrix, you need to upload the spreadsheet to your virtual drive before you can open it in Excel.
  • Open the Practical Exam spreadsheet you downloaded from the Exam page in eCollege.?
  • Save the spreadsheet as Lastname_FirstInitial Week8 FinalExam.xlsx. Example: If your name was Jane Doe, the file name would be:? DoeJ Week8 FinalExam.xlsx.

Formatting and charts (TCOs 1 & 3; 40 points).Enter your name and today's date on the Documentation sheet.

Using the ?Q1&Q2? worksheet:

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

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

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 column, in cell D8.

Format the numbers in the Unit Price and Total Sales columns as accounting or currency format with a dollar sign and two decimal places.

Create a 3-D column chart on a separate sheet 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".

Move the sheet with the column chart immediately after the Q1&Q2 sheet.

Rename the chart worksheet ?Total Sales Chart?.

Save your work (CTRL+s) - (but leave it open to continue).

Formulas and applications (TCOs 2 & 7; 40 points).Draw a border around the table at A10-B13, with a light shading color for the column labels.Format the sales in the table as Accounting with 2 decimal places and format the Bonus as a Percentage with no decimal places.? 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 Bonus? in A10:B13 and display the Bonus percentage. (For example, if the grand total in cell D8 was $15,500, then 5% should be displayed in cell E8.)Format cell E8 as a percentage with zero decimal places.? In cell E10 enter the label Highest Sales. In F10 enter a formula using a function to display the highest total sales value for any product.In cell E11 enter the label Lowest Sales. In F11 enter a formula using a function to display the lowest total sales value for any product.In cell E12 enter the label Average Sales. In F12 enter a formula using a function to display the average total sales value for all products. Format the labels and statistics (Highest Sales, Lowest Sales, and Average Sales) to display an outline (exterior border) around these cells for readability.Add a brief comment to cell F10 to point to the highest in sales. Save your work (CTRL+s) - (but leave it open to continue).Data cleansing, lists, sorting, conditional formatting, and pivot tables (TCOs 4 & 8; 40 points).Adjust the widths of the columns so that the contents of each column are visible.? Add a new heading ?Area Code? in cell I1. Column H contains each customer?s complete phone number. In column I, use the () function to extract and display just the area code including the parentheses. Convert the list of customers into an Excel table.Sort the table into ascending alphabetical order by Last Name.Apply conditional formatting to the State column so that cells containing ?Texas? are highlighted in red and cells containing ?California? are highlighted in yellow.Convert the table back into a range.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 labeled Q3-Pivot and place this sheet immediately after the Q3 sheet.
  • Save your work (CTRL+s) - (but leave it open to continue).
  • Data consolidation, analysis, and reporting (TCOs 5 & 9; 40 points)
  • Group the four sheets Q4 New York, Q4 Chicago, Q4 Los Angeles, and Q4 Summary.
  • 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.
  • Select the Q4 Summary worksheet and use either data consolidate or 3D formulas to summarize the three locations sales.?
  • The summary sheet should display the totals for each product and quarter over all 3 cities (New York, Chicago, and Los Angeles).?
  • Format the Q4 Summary totals with a single top and double bottom border across row 6 to frame the totals.?
  • Create a clustered column chart that shows total sales of each product for 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.
  • Add a trend line to the chart to show how sales of cross trainers are changing over time.
  • 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. ?Recommend at least one specific action that Super Shoes should take.
  • Save your work (CTRL+s) - (but leave it open to continue).

  • What-if analysis (TCO 6; 40 points)

  • Using the ?Q5? worksheet:

  • Enter formulas in cells B4, B5, and B6 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.
  • Adjust column widths as needed, and format all values except unit sales as currency with no decimal places.
  • Assign the labels in column A as names for the corresponding cells in column B (that is, B1 should be assigned the name Unit_sales, B2 should be assigned the name Unit_price, and so on).
  • 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, changing (resulting) cells are Total sales, Total cost, and Net income.
  • Create a Scenario Summary report sheet showing results for all three scenarios. Name the sheet ?Scenario Summary?.? Place the ?Scenario Summary? sheet immediately after the Q5 sheet.
  • Save your work (CTRL+s) and close your Excel workbook.?

To submit your work:

Upload your completed Excel workbook to the Week 8 Final Exam dropbox.? 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?.


Need back ASAP within 2 hours thank you


BIS-155 Final Exam Practical Problems

 

Super Shoes, Inc (Exam 3v2)

 

Name:

 

Date: Super Shoes, Inc. Sales by Product

 

Product Unit Price Quantity Total Sales

 

Running Sh

 

99.99

 

57

 

Walking Sh

 

89.99

 

119

 

Cross Traine

 

69.99

 

115

 

Hiking Shoe

 

74.99

 

148 Sales Bonus

 

0 None

 

15000

 

5%

 

30000

 

10% Customer ILast Name

 

1 Butler

 

2 Williamson

 

3 Adams

 

4 King

 

5 Wagner

 

6 Hawkins

 

7 Moreno

 

8 Cook

 

9 Ross

 

10 Greene

 

11 Weaver

 

12 Cook

 

13 Parker

 

14 Howard

 

15 George

 

16 Walker

 

17 Fowler

 

18 Parker

 

19 Romero

 

20 Rogers First Name

 

Daniel

 

Lois

 

Martha

 

Dorothy

 

Sharon

 

Andrew

 

Andrea

 

Larry

 

Kenneth

 

Eric

 

Phillip

 

Charles

 

Kenneth

 

Dennis

 

Diane

 

Carlos

 

Keith

 

Bobby

 

Mark

 

Dorothy Street AddrCity

 

State

 

Postal Cod Phone Number

 

7513 Main Albany

 

New York

 

12242 (336)572-1779

 

3 Artisan TrHouston Texas

 

77218 (074)235-1480

 

7612 Somme

 

El Paso

 

Texas

 

88558 (514)075-8644

 

08799 WestTuscaloosaAlabama

 

35405 (352)656-9412

 

53228 Killde

 

MinneapoliMinnesota

 

55407 (425)436-3366

 

648 ArkansNew OrleanLouisiana

 

70124 (474)032-3827

 

12 OnsgardTucson

 

Arizona

 

85725 (862)349-3426

 

53 6th Par Memphis Tennessee

 

38126 (104)748-9720

 

60366 KennLafayette Louisiana

 

70593 (962)670-8039

 

80281 GolfBiloxi

 

Mississippi

 

39534 (148)654-0304

 

29 Green RLexington Kentucky

 

40510 (432)393-4362

 

88754 Bric Des MoineIowa

 

50335 (142)690-9166

 

223 WaubeNorwalk Connecticu

 

6859 (301)000-8630

 

8352 DenniDes MoineIowa

 

50330 (985)899-2445

 

7096 Baysi Boynton BeFlorida

 

33436 (760)556-7300

 

20 Boyd CrWashingto District of

 

20046 (876)618-8692

 

0472 MilwaFresno

 

California

 

93709 (893)437-0022

 

4661 ChiveNew York CNew York

 

10004 (991)141-0734

 

52 Hagan CErie

 

Pennsylvan

 

16505 (477)496-9649

 

467 ShopkoSan Diego California

 

92170 (259)398-9977 1st Quarter2nd Quarte3rd Quarte4th Quarter

 

###

 

Running Sh

 

3672

 

2544

 

4229

 

###

 

Walking Sh

 

3326

 

2231

 

3045

 

###

 

Cross Train

 

3259

 

4370

 

2225

 

###

 

Hiking Sho

 

3862

 

4512

 

3669 1st Quarter2nd Quarte3rd Quarte4th Quarter

 

###

 

Running Sh

 

1188

 

3994

 

4219

 

###

 

Walking Sh

 

4558

 

2074

 

4226

 

###

 

Cross Train

 

3633

 

1150

 

2293

 

###

 

Hiking Sho

 

2851

 

2023

 

3338 1st Quarter2nd Quarte3rd Quarte4th Quarter

 

###

 

Running Sh

 

2593

 

1976

 

2137

 

###

 

Walking Sh

 

4233

 

3981

 

2939

 

###

 

Cross Train

 

2410

 

4301

 

2471

 

###

 

Hiking Sho

 

1285

 

3751

 

2523 1st Quarter2nd Quarte3rd Quarte4th Quarter

 

Running Shoes

 

Walking Shoes

 

Cross Trainers

 

Hiking Shoes

 

Analysis and recommended action on cross trainer sales Unit sales

 

Unit price

 

Unit cost

 

Total sales

 

Total cost

 

Net income 50

 

75

 

45

 


Solution details:
STATUS
Answered
QUALITY
Approved
ANSWER RATING

This question was answered on: Sep 18, 2020

PRICE: $15

Solution~0001005680.zip (25.37 KB)

Buy this answer for only: $15

This attachment is locked

We have a ready expert answer for this paper which you can use for in-depth understanding, research editing or paraphrasing. You can buy it or order for a fresh, original and plagiarism-free copy from our tutoring website www.aceyourhomework.com (Deadline assured. Flexible pricing. TurnItIn Report provided)

Pay using PayPal (No PayPal account Required) or your credit card . All your purchases are securely protected by .
SiteLock

About this Question

STATUS

Answered

QUALITY

Approved

DATE ANSWERED

Sep 18, 2020

EXPERT

Tutor

ANSWER RATING

GET INSTANT HELP/h4>

We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.

You can also use these solutions:

  • As a reference for in-depth understanding of the subject.
  • As a source of ideas / reasoning for your own research (if properly referenced)
  • For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).
This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.

NEW ASSIGNMENT HELP?

Order New Solution. Quick Turnaround

Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN YOUR SET DEADLINE.

Order Now