Create An Excel File With Four Worksheets That Provides Exte
Create an Excel file with four worksheets that provides extensive use of Excel capabilities for charting
Create a Microsoft Excel file with four worksheets that provides extensive use of Excel capabilities for charting. The charts will be copied into a Microsoft PowerPoint file and the student will develop appropriate findings and recommendations based on analysis of the data. A large rental car company has two metropolitan locations, one at the airport and another centrally located in downtown. It has been operating since 2015 and each location summarizes its car rental revenue quarterly. Both locations rent four classes of cars: economy, premium, hybrid, SUV. Rental revenue is maintained separately for the four classes of rental vehicles. The data resides in the file fall2018rentalcars.txt, a text file with .txt extension. Use only the provided data. All formatting should be Arial 10, with monetary values including dollar signs and commas, and numbers formatted with commas and no decimal places unless specified. The project involves importing data, creating pivot tables, generating charts, and developing a PowerPoint presentation summarizing analysis, findings, and recommendations.
Paper For Above instruction
The task begins with constructing a detailed Excel workbook designed to analyze car rental revenue data over multiple years for a company with two locations: an airport and a downtown facility. This process involves importing, cleaning, and formatting data, then utilizing Excel's advanced features such as tables, pivot tables, and charting functions to visualize trends and insights. Subsequently, these visualizations are transferred into a PowerPoint presentation to communicate findings and suggest strategic recommendations.
First, the user must create a well-organized Excel file with additional worksheets titled Data, Slide 2, Slide 3, and Filter Analysis, alongside the initial Analysis by worksheet. The initial step involves importing the provided .txt data into the Data worksheet, ensuring data is in appropriate formats, with revenue figures formatted as currency and vehicle counts as number data with comma separators. The data must be sorted by year, with 2016 and 2017 data removed, focusing solely on 2015 for detailed analysis.
Next, the user creates pivot tables based on the 2015 data: one set in Slide 2 for analyzing rental counts by car class and quarter, and by location and quarter; and another set in Slide 3 for revenue analysis by similar categories. These pivot tables are formatted according to specifications, with currency formats, right-aligned labels, and no decimal places. The pivot tables are used to create bar or column charts that visually demonstrate rental patterns and revenue across quarters, which are then positioned and formatted precisely within the worksheets.
The analysis extends into the Filter Analysis worksheet, where data is filtered to isolate hybrid vehicles only in the first quarter, with summarized totals calculated directly beneath the filtered data to facilitate comparison with pivot table summaries. This helps verify data accuracy and supports the findings.
Finally, the user transitions to developing a PowerPoint presentation. The first slide introduces the presentation with project details. The second slide presents the number of cars rented in 2015, featuring the charts from Slide 2 worksheets, with clear labeling and explanations. The third slide focuses on rental revenue in 2015, including relevant charts from Slide 3. The concluding slide synthesizes key findings, trends, and strategic recommendations derived from the analysis, emphasizing actionable insights to optimize future rental operations. Visual elements, such as relevant graphics or icons, are included to enhance comprehension and engagement.
Throughout the entire process, emphasis is placed on maintaining consistent formatting, professional presentation standards, and clarity. Footer information with the student’s name and slide numbering is added for professionalism. Transitions and animations are applied to slides to enhance the delivery of the presentation.
This comprehensive project demonstrates mastery of Microsoft Excel’s data handling, visualization capabilities, and integration with PowerPoint for effective communication of analytical insights, focusing exclusively on the data provided without any alterations or external inputs.