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

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.

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

 Final Figure 2: Bryant Bros Worksheet

Final Figure 3: Harrington Worksheet

Final Figure 4: East York Worksheet

Final Figure 5: East York Revenue Chart Sheet

Final Figure 6: 2016 FastTrak Revenues by Area Chart Sheet

Final Figure 7: FastTrak Worksheet