• Background

Lariat is a national rental car company who wants to make better decisions about the vehicles that they purchase for their national fleet. You're consulting as a data analyst for Lariat. They've hired you to make suggestions on how to make smarter business decisions. Your job is to analyze the costs and revenue generated by their rental car fleet. 

• Goal

Maximizing revenue and minimizing costs to increase profitability and promote business growth

• Maximizing revenue by increasing rental price

• Maximizing revenue by increasing rental price

• Dataset

Four worksheets

1. Car_id_mapping

• car_id : Car identifier

• car_make : The name of the car maker company

• car_model : The model of the car

• car_model_year : The model year of the car

2. Car_costs

• car_id : Matches the identifier in the "car_id_mapping"

• car_cost_monthly : Monthly cost of the company's car expenses

• car_insurance : Monthly insurance cost for each car 

        3. Car_revenue_fixed

• car_id : Matches the identifier in the "car_id_mapping"

• rented_date : The date on which the car was rented

• rented_length : The duration (in days) for which the car was rented

• branch_id : Matches the identifier in the "branch_lacation"

• price_per_day : The rental price per day for the car

• driver_age : The age of the driver who rented the car

• driver_gender : The gender of the driver who rented the car

• accident_ind : Indicates whether the car was involved in an accident (1 = Yes, 0 = No)

4. Branch_Location

• branch_id : Branch identifier

• city : City where each branch is located

• state : State where each branch is located

• airport_ind : The branch is locate at an airport (True/False)

• Methodology 

  • • Data Analytics workflow

1. Collect the data

• The dataset was collected by Lariat car rental company follow by four worksheets, Car_id_mapping, Car_costs, Car_revenue_fixed, Branch_Location . The dataset was raw data which included leading and trailing whitespace and Inconsistent data formatting in several fields 

• Exploring raw data by Assessing the quality of each data field using formulas SUM, 

2. Clean the data

To preparing the data for analysis by eliminating errors and inconsistencies. Cleaning the data in Excel by using 

• Remove Duplicate function to remove duplicates 

• TRIM formula to remove any leading or trailing white space. 

• Combining data from two worksheets can be accomplished using numaric functions, INDEX_MATCH, SUMIF, COUNTIF, AVERAGEIF, and IF condition formulas. 

• Cleaned data related to car business costs and benefits, which are stored in two separate tables named 

  Clean_carcost : Table of business cost

Clean_carRent : Table of business benefit

3. Analyze the data

• Create a baseline which is 2018 Profit report for Lariat company

• Compare outcomes from different possible scenarios. by using what-if analysis  to try out different inputs and see their effects on the outcomes which come up with 3 strategies

Strategy 1 : Increase rental prices by 10% while assuming a 5% reduction in the number of rentals.

Strategy 2 : Remove cars that generate less than a 0% margin.

Strategy 3 : Remove cars that generate less than a 0% margin and increase prices on the remaining fleet

4. Visualize

• Create formatting 2019 Revenue Growth Strategy compare each strategies and the user can change the input to see the difference.

• Visualizing the data by created the pivot table

• Analysis 

Strategy 1 : Increase rental prices by 10% while assuming a 5% reduction in the number of rentals.

Strategy 3 : Remove cars that generate less than a 0% margin and increase prices on the remaining fleet

• Finding and result

• Conclusion and recommendation

Conclusions

1. The most profitable is strategy 3, increasing the price and remove cars that have sale margin less than 0%

2. Estimated Impacts 

• Estimate Cost : -1.8 M

Estimate Revenue : 56 M

Estimate Profit : +25 M

• Increasing price might reduce the rentals 0 - 8%

• Removing cars could impact rental availability, replacing new cars could offset any potential loss.

Recommendation

1. Selling off cars that generate less revenue can increase profits.

2. The company needs to invest in research, real estate, construction, and labor to open new branches. However, the current dataset is insufficient to determine whether opening more branches would result in increased revenue for 2019, as each car does not belong to a specific branch.

3. Replacing old cars with popular models that generate a margin of more than 30% is a good idea. It is better to replace cars that generate less revenue due to accidents, increased insurance costs, or unpopular car brands with fewer reservations.

• Analyze in Excel

• Presentation