Let us worry about your assignment instead!

We Helped With This MATLAB Programming Homework: Have A Similar One?

SOLVED
CategoryProgramming
SubjectMATLAB
DifficultyCollege
StatusSolved
More InfoDo My Matlab Homework
496011

Assignment Description

samlogo_blue_pc.tifShelly Cashman Excel 2013
Chapter 7: SAM Project 1a
Flex Cab Company
GETTING DATA FROM EXTERNAL FILES

Project Goal
M Project Name
Project Goal

           

 

                                                                                                                            

 

PROJECT DESCRIPTION

Sasha Rivera works in the Operations department of the Flex Cab Company, a taxi service in Toronto, Ontario. Flex Cab is exploring the possibility of acquiring other taxi and livery companies in the area. Sasha has collected financial information from the acquisition candidates, but each company has provided data in a different file format. Sasha has asked you to create a comprehensive workbook combining all of the company data. You will need to import the individual data files into an Excel workbook, apply formatting changes, and create graphics to help Sasha better analyze the data.

 

GETTING STARTED

·         Download the following file from the SAM website:

o    SC_Excel2013_C7_P1a_FirstLastName_1.xlsx

·         Open the file you just downloaded and save it with the name:

o    SC_Excel2013_C7_P1a_FirstLastName_2.xlsx

o    Hint: If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.

·         To complete this Project, you will also need to download and save the following support files from the SAM website:

o    support_SC_E13_C7_P1a_Bryant.jpg

o    support_SC_E13_C7_P1a_Harrington.jpg

o    support_SC_E13_C7_P1a_East_York.jpg

o    support_SC_E13_C7_P1a_FastTrak.jpg

o    support_SC_E13_C7_P1a_Bryant_Taxi.docx

o    support_SC_E13_C7_P1a_Harrington.txt

o    support_SC_E13_C7_P1a_FastTrak.accdb

·         With the file SC_Excel2013_C7_P1a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

1.            Go to the Analysis worksheet. Insert a SmartArt graphic using the Vertical Picture Accent List layout (in the Pictures section of the SmartArt Graphic dialog box).

2.            Insert a picture into the picture placeholders in each shape using the support files below:

a.    Insert support_SC_E13_C7_P1a_Bryant.jpg into the first shape.

b.    Insert support_SC_E13_C7_P1a_Harrington.jpg into the second shape.

c.    Insert support_SC_E13_C7_P1a_East_York.jpg into the third shape.

3.            Add text to the SmartArt graphic as described below:

a.    Type Bryant into the first text placeholder.

b.    Type Harrington into the second text placeholder.

c.    Type East York into the third text placeholder.

4.            Make formatting changes to the SmartArt graphic as follows:

a.    Change the colors to Colorful Range - Accent Colors 5 to 6.

b.    Apply the Intense Effect SmartArt style.

5.            Add a new shape to the bottom of the SmartArt graphic and update it as described below:

a.    Insert the support_SC_E13_C7_P1a_FastTrak.jpg into the new shape.

b.    Type FastTrak into the new text placeholder.

6.            Position the SmartArt graphic so the upper-left border is in cell A4. Drag the lower-right corner of the graphic’s border to cell K21.

7.            Remove the worksheet gridlines from view.

8.            In the Bryant Bros worksheet, copy cells H14:L19 to the Office Clipboard, and then paste the data from cells H14:L19 into cell B14, transposing the data when you paste. (Hint: To transpose the data, in the Paste Gallery, click Paste Special, then in the Paste Special dialog box, click the Transpose check box to select it, and click OK). Delete the contents of cells H14:L19.

9.            Open the Word document support_SC_E13_C7_P1a_Bryant_Taxi.docx, and select the table rows and columns as shown in Figure 1 on the following page. Copy the data to the Office Clipboard, then close the Word file.


 

Microsoft product screenshot reprinted with permission from Microsoft Incorporated. ...<table cellpadding=0 cellspacing=0><tr><td width=40 height=0></td></tr><tr><td></td><td><img width=581 height=256 src=
Figure 1: Selection of Bryant Brothers Taxi Table


 

In your Excel workbook, go to the Bryant Bros worksheet. Paste the Word data from the Office Clipboard as text into cell B4. (Hint: To paste the Word data as text, click the Paste arrow and click Paste Special, then choose Text in the Paste As dialog box and click OK.)

10.         Use the Format Painter to copy the formatting of range B12:G19 to range B2:G8 in the Bryant Bros worksheet.

11.         In the Harrington worksheet, import data from the text file support_SC_E13_C7_P1a_harrington.txt into the worksheet at cell B4. The data in the text file has headers and is delimited with a comma.

12.         Change the width of columns C:G to 11 characters.

13.         Use the Convert Text to Columns Wizard to convert the text in cells B15:B18 to columns. Use a space as the column delimiter. (Tip: You may need to deselect the comma delimiter you used when importing data in step 11.)

14.         Select the range C15:F18 and use the Quick Analysis Tool to create formulas that will total the values in each column of that range. The formulas should use the SUM function and should appear in the range C19:F19.

15.         Go to the East York worksheet and, using the Find and Replace command, replace all instances of the word Quarter with the letter Q. (Hint: You should make 4 replacements.)

16.         Select the range C5:G8 and use the Quick Analysis tool to add a conditional formatting rule to the range. The conditional formatting rule should identify the East York Taxi’s Top 10% revenues over the period of 2012-2016.

17.         Go to the East York Revenue Chart chart sheet. Select the column chart’s data and change the column shape to the Full Cone option.

 

18.         Insert a WordArt object using the Fill - Blue, Accent 1, Shadow style and then modify the WordArt as described:

a.    Using the mouse, drag the WordArt object to the top of the chart.

b.    Enter the text East York Taxi Revenues in the WordArt.

19.         Go to the FastTrak worksheet. In cell B4, import data from the Revenues table in the Access database file support_SC_E13_C7_P1a_FastTrak.accdb.

20.         Make the following formatting changes to the FastTrak worksheet:

a.    Convert the table to a range.

b.    Use the Format Painter to copy the formatting of cell B10 to range B4:G8.

c.    Change the width of columns C:G to 11.00 characters.

d.    For cells B4:G4 and B5:B8, apply Bold formatting and center-align cell contents.

(Hint: If you receive an error message indicating the numbers in the range C4:G4 are stored as text, select the range C4:G4, click the Error Message arrow, and then click the Convert to Number option.)

21.         Apply the Accounting Number format with no decimal places to the range C5:G8. (Hint: The format listed in the Number Format box may appear as Custom rather than Accounting after you decrease the decimal places.)

22.         Select the range B14:F18 and use the Quick Analysis tool to insert a Clustered Column chart. Move the Clustered Column chart to a new chart sheet named 2016 FastTrak Revenues by Area. Make the following formatting changes to the chart:

a.    Change the chart style to Style 6.

b.    Update the Chart title to 2016 FastTrak Revenues by Area.

c.    Add a Primary Vertical Axis Title with the text Dollars.

d.    Add a Primary Horizontal Axis Title with the text Quarter.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Analysis Worksheet

...
style='position:relative;z-index:251689984;left:-18px;top:0px;width:674px;
height:487px'><img width=674 height=487 src=



 

 

 Final Figure 2: Bryant Bros Worksheet


...
width=639 height=542 id=

 


 

 

...<table cellpadding=0 cellspacing=0><tr><td width=1 height=0></td></tr><tr><td></td><td><img width=638 height=561 src=
Final Figure 3: Harrington Worksheet


 


 

...<table cellpadding=0 cellspacing=0><tr><td width=7 height=0></td></tr><tr><td></td><td><img width=621 height=563 src=
Final Figure 4: East York Worksheet


 


 

...
style='position:relative;z-index:251686912;left:-13px;top:0px;width:675px;
height:506px'><img width=675 height=506 src=
Final Figure 5: East York Revenue Chart Sheet



 

...
style='position:relative;z-index:251687936;left:-16px;top:0px;width:674px;
height:503px'><img width=674 height=503 src=
Final Figure 6: 2016 FastTrak Revenues by Area Chart Sheet



 

...
style='position:relative;z-index:251688960;left:-19px;top:0px;width:675px;
height:530px'><img width=675 height=530 src=
Final Figure 7: FastTrak Worksheet


 

 

Customer Feedback

"Thanks for explanations after the assignment was already completed... Emily is such a nice tutor! "

Order #13073

Find Us On

soc fb soc insta


Paypal supported