Need Help with this assignment.. file is attached...easy fast one..please contact me..

Shelly Cashman Excel 2013 Chapter 3: SAM Project 1a Flex Cab Company FORMAT A REVENUE REPORT WITH CHARTS AND GRAPHS PROJECT DESCRIPTION Simone Kisawa works 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 through 3. (Hint: Select cell A4 as the cell on which to freeze panes.) 3. Delete column M. 4. Create a Line Sparkline in cell L5 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 chart with 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 (3rd column, 1st row 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. Shelly Cashman Excel 2013| Chapter 3: SAM Project 1a 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 Charts tool to create a Clustered Column chart 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. Add a 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. Shelly Cashman Excel 2013| Chapter 3: SAM Project 1a Final Figure 1: 4 May 2015 Revenue Detail Microsoft product screenshots used with permission from Microsoft Corporation. Copyright 2014 Cengage Learning. All Rights Reserved. Shelly Cashman Excel 2013| Chapter 3: SAM Project 1a Final Figure 2: 11 May 2015 Revenue Chart Copyright 2014 Cengage Learning. All Rights Reserved. Shelly Cashman Excel 2013| Chapter 3: SAM Project 1a Final Figure 3: 11 May 2015 Revenue Detail Copyright 2014 Cengage Learning. All Rights Reserved. Shelly Cashman Excel 2013| Chapter 3: SAM Project 1a Final Figure 4: 2016 Revenue Projection Copyright 2014 Cengage Learning. All Rights Reserved. Shelly Cashman Excel 2013| Chapter 3: SAM Project 1a Final Figure 5: Credit Card Transactions Copyright 2014 Cengage Learning. All Rights Reserved. Shelly Cashman Excel 2013 Chapter 3: SAM Project 1a Flex Cab Company Format a Revenue Report with Charts and Graphs Author: Malcolm Gaillard Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. Flex Cab Company Revenue Detail Date 5/11/2015 $ Total $ Percent of total Zone 1 12,690.65 $ 12,047.90 13,418.35 18,907.46 23,865.94 15,841.15 11,801.30 108,572.75 Zone 2 16,322.10 $ 16,702.25 14,694.90 13,074.95 17,894.20 21,254.55 14,314.60 Err:502 10% Zone 3 13,533.75 $ 10,779.85 12,092.10 17,834.35 21,485.02 14,733.00 12,696.85 Err:502 10% Week of May 11, 2015 Zone 4 Zone 5 19,503.00 $ 17,206.45 18,819.20 18,579.35 18,557.70 16,378.30 19,620.35 15,806.75 19,848.40 18,985.90 22,983.40 20,131.40 20,235.25 17,762.90 Err:502 Err:502 9% 12% 11% Share of Total Revenue 10% 10% 10% Charts 12% 9% 13% 12% 13% Zone 1 Zone 2 Zone 3 11% Zone 4 Zone 5 Zone 6 Zone 7 Zone 8 Zone 9 b Company nue Detail May 11, 2015 $ Zone 6 20,502.20 $ 21,793.50 19,402.95 18,634.65 22,629.75 20,738.00 21,932.45 Err:502 13% % Zone 9 Zone 7 19,419.05 $ 18,639.05 19,683.30 20,539.65 22,933.15 23,234.40 21,680.35 Err:502 13% Zone 8 19,171.10 $ 17,774.80 18,149.25 19,730.15 19,646.10 21,513.45 20,111.85 Err:502 12% Zone 9 16,524.60 13,625.65 15,536.40 15,285.55 18,397.20 19,384.00 18,079.40 Err:502 10% Total $154,872.90 $148,761.55 $147,913.25 $159,433.86 $185,685.66 $179,813.35 $158,614.95 Err:502 Flex Cab Company Revenue Detail Total 5/4/2015 $ 5/5/2015 5/6/2015 5/7/2015 5/8/2015 5/9/2015 5/10/2015 $ Percent of total 12,172.65 $ 11,946.90 13,300.35 12,104.40 14,650.65 15,249.15 11,829.30 91,253.40 16,718.10 $ 16,742.25 14,466.90 12,892.95 18,106.20 20,927.55 14,397.60 Err:502 8% 13,392.75 $ 11,063.85 12,014.10 12,574.80 14,182.35 14,763.00 12,449.85 Err:502 10% 13% 6 Zo n e 5 Zo n 19,215.00 $ 18,526.20 18,158.70 19,516.35 19,895.40 22,793.40 20,333.25 Err:502 8% e 4 Zo n e 3 Zo n e 2 e Zo n e Zo n Da te 1 Week of May 4, 2015 17,439.45 18,361.35 16,344.30 15,450.75 18,834.90 19,916.40 17,994.90 Err:502 11% Share of Total Revenue 160,000.00 140,000.00 Charts 120,000.00 100,000.00 80,000.00 60,000.00 40,000.00 20,000.00 0.00 Zone 1 Zone 6 Zone 2 Zone 7 Zone 3 Zone 8 Zone 4 Zone 9 Zone 5 Zone 1 5/4/2015 Z 5/5/2 b Company nue Detail $ 20,143.20 $ 21,325.50 18,919.95 14,222.86 22,506.75 20,685.00 21,723.45 Err:502 19,825.05 $ 18,628.05 19,746.30 20,698.65 23,292.15 23,528.40 21,784.35 Err:502 13% 9 Zo n 19,364.10 $ 17,719.80 18,569.25 19,428.15 19,637.10 21,450.45 19,988.85 Err:502 13% To ta l e 8 Zo n e 7 Zo n e 6 e Zo n Zo n e 5 f May 4, 2015 15,888.60 13,327.65 15,422.40 14,879.55 18,232.20 18,816.00 17,417.40 Err:502 12% $154,158.90 $147,641.55 $146,942.25 $141,768.46 $169,337.70 $178,129.35 $157,918.95 Err:502 10% Revenue Per Dispatch Center 160,000.00 140,000.00 120,000.00 100,000.00 80,000.00 60,000.00 40,000.00 20,000.00 0.00 Zone 1 5/4/2015 Zone 2 5/5/2015 Zone 3 5/6/2015 Zone 4 Zone 5 5/7/2015 Zone 6 5/8/2015 Zone 7 5/9/2015 Zone 8 Zone 9 5/10/2015 154,158.90 147,641.55 146,942.25 141,768.46 169,337.70 178,129.35 157,918.95 Flex Cab Company 2016 Revenue Projection Weekly Citywide Revenue (Estimated) Area Downtown North of Downtown South of Downtown East of Downtown West of Downtown Citywide Total $1,100,598 Share of Revenue (Estimated) 23% 18% 20% 22% 17% 100% Annual Revenue (Estimated) $13,163,152 $13,163,152 Flex Cab Company Projected Number of Credit Card Transactions Area Downtown North of Downtown South of Downtown East of Downtown West of Downtown Total Flat Transaction Cost 2016 2017 30,405 15,676 9,512 6,329 7,300 69,222 2018 33,446 17,244 10,463 6,962 8,030 76,144 36,790 18,968 11,509 7,659 8,833 83,758