211|EXCEL – Car Rental Expense reporting

[Dallin_Gardner]

BYU Student Author: @Dallin_Gardner
Reviewers: @Jimmy_Han, @Jacob_Dutton
Estimated Time to Solve: 40 Minutes

We provide the solution to this challenge using:

• Excel
• VBA

Need a program? Click here.

Overview
You are part of a small accounting firm, and you start talking to a potential client who has started a car rental company. He currently has a fleet of 10 vehicles that he either owns or manages for other owners. He’s starting to get to the point where it is hard to keep track of all his expenses. He tells you he wants to eventually move to an ERP system like Quickbooks, but would rather continue using Excel for the time being. He asks you to help him make his excel spreadsheet more robust and request a few specific features.

For this project you will be building an expense reporting function that will automatically organize expenses by vehicle and category of expense. That way whenever new expenses are recorded, it will flow directly to the "Summary by Car” report worksheet.

Instructions

1. Create a new worksheet, and construct dropdown menu on the “Fleet Expense” worksheet for all Vehicle Names. The list of cars can be found on the “Summary by Car” sheet.
   a. Add a “General” at the bottom of the list
   b. This dropdown menu must be able to change if there are any additions or deletions.
   c. This will require data validation and VBA (Working code in hints, not required to code)
   d. You will need to save as Macro enabled workbook “.xlsm”
2. Create a new worksheet. Construct dropdown menus for Banks and cards
   a. VBA can be used here, but not necessary for this challenge
3. Construct dropdown menu for expense category.
   a. Use the expense list on the “Summary by Car” sheet.
4. Reorganize the fleet expense sheet so that date goes in descending order (Client wants to see the newest transactions first.
   a. Then add the vehicle category in between expense and category.
   b. Then fill in the vehicle category, it should be mostly obvious where to assign the expense
5. Next add another VBA function that adds a new row at the top with the same formatting and keeps all the dropdowns for all new expense transactions.
   a. Again, VBA code is included in the hints.
6. Finally we need to have all the data flow into the necessary cells in the “Summary by car”
   a. Use the =SumIfs() function to capture the correct expenses by Vehicle and expense category
   b. Add a functionality that will filter the expenses cells based on the period the user wants to analyze. (Start date and end date)
   c. Sum the totals and include the general expenses in cell M14.
   d. As a check figure add the total expenses from Fleet expenses and compare with the total expenses in Summary by car.
7. Now that the spreadsheet is built, test it by adding these 3 expense transactions
   a. Confirm that your date filter functions correctly
   
   

Data Files

• Challenge211Car_Rental_Expense_Reporting.xlsx

Suggestions and Hints

1. Hints for step 1

• Make a blank column between Expense and category labeled “Vehicle”.

• Create another sheet with the vehicles listed.

• Select the necessary cells in the column to have the drop down

• Data > Data Tools > Data Validation > Settings > Allow > Lists. Use the list you created as the source.

• Here’s what it should look like:
  

  

• VBA for adding vehicle:
  Sub InsertRowAndIncrement()
  Dim ws As Worksheet
  Dim lastRow As Long
  Dim nextNumber As Long
  Set ws = ActiveSheet

  'Finding the last row in column A with data
  lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row

  'Determine the next number to insert into Column A
  nextNumber = ws.Cells(lastRow, 1).Value + 1

  'Insert a new row below the last row
  ws.Rows(lastRow + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

  'Insert the next number in the new row’s Column A
  ws.Cells(lastRow + 1, 1).Value = nextNumber
  End Sub

2. Hints for step 2

• Create another sheet with the bank accounts listed.
• Select the necessary cells in the column to have the drop down
• Data > Data Tools > Data Validation > Settings > Allow > Lists. Use the list you created as the source.
  
  

3. Hints for step 3

• Select the necessary cells in the column to have the drop down
• Data > Data Tools > Data Validation > Settings > Allow > Lists. Use the expense categories listed on Summary by car as your source.

4. Hints for step 4

• Click on “Date” (E1) on the “Fleet expense.” Click Data > Sort > Z to A.
• Most of the time a car is noted in the expense it is in quotations. Mos of the categories are filled, but will need to assign the one that is not.

5. Hints for step 5

• VBA code for fleet expense sheet:
  Sub InsertRowAboveRow2()
  Dim ws As Worksheet
  Set ws = ActiveSheet

  'Copy the format and validation from row 2
  ws.Rows(“2:2”).Copy

  'Insert the copied row above row 2
  ws.Rows(“2:2”).Insert Shift:=xlDown

  'Clear the contents of the new row but keep the format and validation
  ws.Rows(“2:2”).ClearContents

  Application.CutCopyMode = False
  End Sub

6. Hints for Step 6

• Here is the formula outline to use:
  =SumIfs(Costs Column , Vehicle column , Vehicle name , Category Column , Name of category, date column , “>=”&StartDate , date column, “<=”&EndDate
• To maintain the dynamic functionality as we add more expenses, make sure that you use the column headers in your ranges of columns.

7. Hints for step 7
   -If your date functionality isn’t working, make sure that the number type is set as dates
   -Double check that you’re using real dates (for example 2/30/2024 isn’t a real day as February in 2024 only has 29 days)

Solution

Check Figure

Total Expenses assigned to cars in January is $676.86

Challenge211Solution.Xlsm
Solution Video: Challenge 211|EXCEL – Car Rental Expense Reporting