Rental Cost Analysis Tool Development
Budget: $10 – $30 USD
Step 1: Key Variables
Include the following inputs and calculations:
Dates: Check-Out and Check-In
Pricing: Daily, Weekly, Monthly, 3-Month Rates
Charges: Fuel Surcharge, Damages, Cancellation Fee
Mileage: Allowance, Actual Mileage, Excess Mileage Charge
Payment: Deposit, Payment Amount, Card Fee
Outputs: Total Cost, Refund/Outstanding Amount
Step 2: Excel Layout
Design an Excel sheet with the following structure:
Description Input Formula/Explanation
Check-Out Date 01/10/2025 User input
Check-In Date 01/25/2025 User input
Rental Duration (Days) =B3-B2
Rental Type Daily/Weekly/Monthly/3-Month Dropdown using Data Validation
Daily Rate $50 User input
Weekly Rate $300 User input
Monthly Rate $1,000 User input
3-Month Rate $2,800 User input
Rental Cost Formula depends on rental type (see Step 3 below)
Fuel Surcharge $30 User input
Damages $100 User input (if applicable)
Mileage Allowance (miles) 500 User input
Actual Miles Driven 600 User input
Excess Mileage Charge (per mile) $0.25 User input
Excess Mileage Cost =MAX(0,(B12-B11)*B13)
Cancellation Fee $50 User input (if applicable)
Deposit Amount $500 User input
Payment Amount $600 User input
Card Fee (%) 2 User input
Card Fee Amount =B16*B17/100
Total Cost =SUM(B9,B10,B11,B14,B18,B19)
Refund/Outstanding Amount =B20-B21 (positive = refund; negative = outstanding)
Step 3: Add Formulas
1. Rental Duration (Days):
Formula:
=B3-B2
Ensure cells B2 and B3 are formatted as Date.
2. Rental Cost Based on Type:
Formula (in B9):
=IF(B4="Daily", B5*B6,
IF(B4="Weekly", ROUNDUP(B6/7,0)*B7,
IF(B4="Monthly", ROUNDUP(B6/30,0)*B8,
IF(B4="3-Month", ROUNDUP(B6/90,0)*B9, 0))))
Explanation:
Use the daily, weekly, monthly, or 3-month rate depending on the rental type.
For weekly, monthly, and 3-month rentals, round up to the nearest full unit of time.
3. Excess Mileage Cost:
Formula:
=MAX(0,(B12-B11)*B13)
Explanation:
Subtract mileage allowance from actual miles driven.
Multiply any excess by the per-mile charge.
4. Card Fee Amount:
Formula:
=B16*B17/100
Explanation:
Calculate a percentage fee on the payment amount.
5. Total Cost:
Formula:
=SUM(B9,B10,B11,B14,B18,B19)
Explanation:
Add Rental Cost, Fuel Surcharge, Damages, Excess Mileage Cost, Card Fee, and Cancellation Fee.
6. Refund/Outstanding Amount:
Formula:
=B20-B21
Explanation:
Subtract the payment amount from the total cost.
Positive value means a refund is owed to the customer; negative value means the customer owes more.
Step 4: Add Dropdown for Rental Type
Select the Rental Type cell (e.g., B4).
Go to Data → Data Validation.
Allow: List.
Source: Daily, Weekly, Monthly, 3-Month.
Step 5: Formatting
Date Formatting: Set Check-Out and Check-In to Date format.
Currency Formatting: Format cells for monetary values as Currency.
Number Formatting: Format mileage values as numbers without decimals.
Step 6: Test the Calculator
Input various combinations of rental types, dates, and charges.
Test scenarios, such as:
Refund due when total cost is less than payment.
Outstanding balance when total cost exceeds payment.
No damages or cancellation fee.
Customer exceeds mileage allowance.
Include the following inputs and calculations:
Dates: Check-Out and Check-In
Pricing: Daily, Weekly, Monthly, 3-Month Rates
Charges: Fuel Surcharge, Damages, Cancellation Fee
Mileage: Allowance, Actual Mileage, Excess Mileage Charge
Payment: Deposit, Payment Amount, Card Fee
Outputs: Total Cost, Refund/Outstanding Amount
Step 2: Excel Layout
Design an Excel sheet with the following structure:
Description Input Formula/Explanation
Check-Out Date 01/10/2025 User input
Check-In Date 01/25/2025 User input
Rental Duration (Days) =B3-B2
Rental Type Daily/Weekly/Monthly/3-Month Dropdown using Data Validation
Daily Rate $50 User input
Weekly Rate $300 User input
Monthly Rate $1,000 User input
3-Month Rate $2,800 User input
Rental Cost Formula depends on rental type (see Step 3 below)
Fuel Surcharge $30 User input
Damages $100 User input (if applicable)
Mileage Allowance (miles) 500 User input
Actual Miles Driven 600 User input
Excess Mileage Charge (per mile) $0.25 User input
Excess Mileage Cost =MAX(0,(B12-B11)*B13)
Cancellation Fee $50 User input (if applicable)
Deposit Amount $500 User input
Payment Amount $600 User input
Card Fee (%) 2 User input
Card Fee Amount =B16*B17/100
Total Cost =SUM(B9,B10,B11,B14,B18,B19)
Refund/Outstanding Amount =B20-B21 (positive = refund; negative = outstanding)
Step 3: Add Formulas
1. Rental Duration (Days):
Formula:
=B3-B2
Ensure cells B2 and B3 are formatted as Date.
2. Rental Cost Based on Type:
Formula (in B9):
=IF(B4="Daily", B5*B6,
IF(B4="Weekly", ROUNDUP(B6/7,0)*B7,
IF(B4="Monthly", ROUNDUP(B6/30,0)*B8,
IF(B4="3-Month", ROUNDUP(B6/90,0)*B9, 0))))
Explanation:
Use the daily, weekly, monthly, or 3-month rate depending on the rental type.
For weekly, monthly, and 3-month rentals, round up to the nearest full unit of time.
3. Excess Mileage Cost:
Formula:
=MAX(0,(B12-B11)*B13)
Explanation:
Subtract mileage allowance from actual miles driven.
Multiply any excess by the per-mile charge.
4. Card Fee Amount:
Formula:
=B16*B17/100
Explanation:
Calculate a percentage fee on the payment amount.
5. Total Cost:
Formula:
=SUM(B9,B10,B11,B14,B18,B19)
Explanation:
Add Rental Cost, Fuel Surcharge, Damages, Excess Mileage Cost, Card Fee, and Cancellation Fee.
6. Refund/Outstanding Amount:
Formula:
=B20-B21
Explanation:
Subtract the payment amount from the total cost.
Positive value means a refund is owed to the customer; negative value means the customer owes more.
Step 4: Add Dropdown for Rental Type
Select the Rental Type cell (e.g., B4).
Go to Data → Data Validation.
Allow: List.
Source: Daily, Weekly, Monthly, 3-Month.
Step 5: Formatting
Date Formatting: Set Check-Out and Check-In to Date format.
Currency Formatting: Format cells for monetary values as Currency.
Number Formatting: Format mileage values as numbers without decimals.
Step 6: Test the Calculator
Input various combinations of rental types, dates, and charges.
Test scenarios, such as:
Refund due when total cost is less than payment.
Outstanding balance when total cost exceeds payment.
No damages or cancellation fee.
Customer exceeds mileage allowance.