Shelly CashmanExcel 2013 Chapter 3: SAM Project 1a Flex Cab Company Format a revenue report with charts and graphs

Shelly CashmanExcel 2013

Chapter 3: SAM Project 1a

Flex Cab Company

Format a revenue report with charts and graphs

Project Goal

M Project Name

Project Goal

PROJECT DESCRIPTION

Simone Kisawaworks in the accounting department of Flex Cab Company, a taxi service in Toronto, Ontario. Simone has begun work on a revenue detail report for the first half of May 2015. She would like you to make some changes and additions to complete the report.

GETTING STARTED

·         Download the following file from the SAM website:

o    SC_Excel2013_C3_P1a_FirstLastName_1.xlsx

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

o    SC_Excel2013_C3_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.

·         With the file SC_Excel2013_C3_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.            Move the sheet 4 May 2015 Revenue Detail to the left of the sheet 11 May 2015 Revenue Detail.

2.            On the 4 May 2015 Revenue Detail sheet,freeze rows 1 through3. (Hint: Select cell A4 as the cell on which to freeze panes.)

3.            Delete column M.

4.            Create a LineSparkline in cellL5 based on the data in B5:J5. Copy the Line Sparkline you create in cell L5 into the range L6:L11.

5.            In the stacked column chartwith the title Revenue Per Dispatch Center, add a primary horizontal axis title of Dispatch Center and a primary vertical axis title of Revenue.

6.            Change the style of the stacked column chart to Chart Style 3 (3rdcolumn, 1strow of the Chart Styles palette).

7.            In the stacked column chart, change the number format of the vertical axis to Accounting style with 0 decimal places. 

8.            In the 3-D pie chart titled Share of Total Revenue, add data labels to this chart using the Outside End style.

9.            Switch to the 11 May 2015 Revenue Detail sheet. Select the range A4:K4 and rotate the text to 45 degrees.

10.         Change the Sparkline style for the range L5:L11 to Sparkline Style Accent 6, (no dark or light).

11.         Use the entry in cell A5 to fill the range A6:A11 with consecutive dates.

12.         Change the fill color of the range A12:K12 to Standard Yellow (4th column in the Standard Colors palette).

13.         Use the Recommended Chartstool to create a Clustered Columnchart based on the range A4:J11. Move the chart to its own chart sheet. Use 11 May 2015 Revenue Chart as the name of the new chart sheet.

14.         In the clustered column chart created in step 13, change the chart title to Revenue per Dispatch Center - Week of May 11, 2015. Adda primary horizontal axis title of Dispatch Center and a primary vertical axis title of Revenue.

15.         In the clustered column chart, change the number format of the vertical axis to the Accounting style with 0 decimal places. (Tip: The vertical axis will already be formatted with the Accounting style, so only change the number of decimal points displayed.)

16.         Switch to the 2016 Revenue Projection sheet. Edit the formula in cell C7 so that the reference to cell C4 is an absolute reference. Copy the formula in cell C7 to the range C8:C11.

17.         Perform a goal seek analysis to determine what value of cell C4 would result in the Citywide Total Annual Revenue (cell C12) equaling $60,000,000.(Tip: Cell C4 will be the changing cell.)Keep the result of the goal seek analysis as the new value for cell C4.

18.         Zoom the 2016 Revenue Projection sheet to 150%

19.         Switch to the Credit Card Transactions sheet. In cell B11, use an IF function to check whether the value of cell B9 is less than 70000.

a.    If this condition is true, the function should multiply cell B9 by 0.03. (Tip: For the value if true, use B9*0.03)

b.    If this condition is false, the function should multiply the value in cell B9 by 0.025 and then add 350. (Tip: For the value if false, use B9*0.025+350)

20.         Copy the formula you entered in cell B11 to the range C11:D11.

Your workbook should look like the Final Figures on the following pages. (The value in cell C4 generated by the goal seek analysis has intentionally been blurred out in Final Figure 4.) Save your changes, close the workbook, and exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: 4 May 2015 Revenue Detail

Microsoft product screenshots used with permission from Microsoft Corporation.

Copyright © 2014 Cengage Learning. All Rights Reserved.

Final Figure2: 11 May 2015 Revenue Chart

Copyright © 2014 Cengage Learning. All Rights Reserved.

Final Figure 3: 11 May 2015 Revenue Detail

Copyright © 2014 Cengage Learning. All Rights Reserved.

Final Figure 4: 2016 Revenue Projection

Copyright © 2014 Cengage Learning. All Rights Reserved.

Final Figure 5: Credit Card Transactions

Copyright © 2014 Cengage Learning. All Rights Reserved.