Data File needed for this case Problem: NP_eX_4-3.xlsx

Certus Car Rental John Tretow is an account manager for the Certus Car Rental, an industry-leading car rental firm that serves customers across the United States and overseas. John is developing a market report for an upcoming sales conference and needs your assistance in summarizes market information into a collection of Excel charts and graphics.

Complete the following.

  1. Open the NP_eX_4-3.xlsx workbook located in the Excel4 > Case1 folder included with your Data Files. Save the workbook as NP_eX_4_certus in the location specified by your instructor.
  2. In the Documentation sheet, enter your name and the date in the range B3:B4.
  3. John wants the report to include pie charts that break down the current year’s revenue in terms of market (Airport vs. Off-Airport), car type (Leisure vs. Commercial), and location (Americas vs. International). In the Rentals by Type worksheet, do the following: a. Create a pie chart of the data in the range A6:B7. Move the chart cover the range D5:F9 in the Analysis worksheet.
    b. Remove the chart title from the pie chart. c. Add data labels to the outside end of the two slices showing the percentage of the Airport vs. Off-Airport sales.
  4. Repeat Step 3 for the data in the range A11:B12 of the Rentals by Type worksheet, placing the pie chart comparing Leisure and Commercial sales in the range H5:H9 of the Analysis worksheet.
  5. Repeat Step 3 for the data in the range A16:B17 of the Rentals by Type worksheet, placing the pie chart comparing revenue between the Americas and International sales in the range J5:J9 of the Analysis worksheet.
  6. John wants to present the company revenue broken down by car type. In the Car Models work-sheet, create a clustered bar chart of the data in the range A4:B9. Move the bar chart to the range B11:F21 of the Analysis worksheet. Remove the chart legend if it exists. Add data labels to the end of the data markers showing the revenue for each car model.
  7. John also wants to track revenue for each car model over the years to determine whether certain car models have increased or decreased in popularity. In the Revenue by Year worksheet, create a line chart of the data in the range A4:F15. Move the chart to the range H11:J21 in the Analysis worksheet.
  8. Apply the following formats to the line chart you created in Step 7: a. Remove the chart title. b. Add major gridlines for the primary vertical and horizontal axes. c. Move the chart legend to the right side of the chart area. d. Add axis titles to the chart. Set the vertical axis title to the text Revenue ($bil) and the horizontal axis title to Year.
    e. Set the interval between tick marks and between the labels on the category (horizontal) axis to 2 units so that the category labels are Y2011, Y2013, Y2015, Y2017, Y2019, and Y2021.
  9. John wants to compare the Certus brand to competing car rental companies. In the range F25:F29, insert line sparklines showing the trend in market share percentages using the data from the range B19:F29 on the Market Share worksheet.
  10. Add green data bars with a gradient fill to the data values in the range E25:E29.
  11. In the range F32:F36, insert line sparklines showing the trend in revenue using the data from the range B5:F15 on the Market Share worksheet.
  12. Add orange data bars with a gradient fill to the data values in the range E32:E36.
  13. John wants to present a more detailed chart of the revenue values from the five competing rental car agencies over the past several years. In the Market Share worksheet, create a Stacked Column chart from the data in the range A4:F15. Move the chart over the range H24:J36 in the Analysis worksheet.
  14. Apply the following formatting to the column chart you created in Step 13: a. Remove the chart title b. Add axis titles to the chart. Set the vertical axis title to the text Revenue ($bil) and the horizontal axis title to Year.
    c. Move the legend to the right side of the chart area. d. Set the interval between tick marks and between the labels on the category (horizontal) axis to 2 units to display the category values Y2011, Y2013, Y2015, Y2017, Y2019, and Y2021. e. Set the gap width between the bars in the chart to 30%.
  15. The company revenue decreased in the past year. John wants you to highlight this fact by add-ing a down-arrow shape from the Shape gallery to the right side of the merged cell B5 on the Analysis worksheet. Set the height of the down arrow to 1″ and the width to 0.5″.
  16. While the company revenue has decreased in the last year, its market share has increased. Add an up-arrow shape to the right side of the merged cell B25. Set the arrow to be 1″ high and 0.5″ wide.
  17. Save the workbook, and then close it.
$25.00 – Buy Solution

Click on Buy Solution and make payment. All prices shown above are in USD. Payment supported in all currencies. Price shown above includes the solution of all questions mentioned on this page. Please note that our prices are fixed (do not bargain).

After making payment, solution is available instantly.Solution is available either in Word or Excel format unless otherwise specified.

If your question is slightly different from the above question, please contact us at info@myassignmentguru.com with your version of question.