Task 2:
Based on the given worksheet (i.e. Expense), you need to further improve the worksheet to make it appear more consistent, professional and well-organized. For example, apply proper formatting to the cells like Ringgit Malaysia sign with two decimal places.
Task 3:
When a car has 60,000 miles, the sales representative can request a new car. Use conditional formatting to highlight ending mileage readings that are greater than 55,000 miles. If a sales representative has a car that is approaching its end-of-service date, Kevin will remind him that it is time to request a new car.
Task 4:
Insert four new columns into the Expense worksheet namely Actual mileage, Total expenses, Overage and Overage charge.
Task 5:
Calculate the Actual mileage which is the difference between the week's ending mileage and the week's beginning mileage.
Task 6:
Calculate the Total expenses which is a summation of the expenses for the week.
Task 7:
Calculate the Overage that requires a reference to the mileage lookup table and then determine how many miles over the limit, if any, the employee has driven (Hint: use nesting IF and VLOOKUP functions).
Task 8:
Calculate the Overage charge which multiplies the overage by the rate specified in the mileage table (Hint: use the VLOOKUP function as well).
## Deliverables
Task 2:
Based on the given worksheet (i.e. Expense), you need to further improve the worksheet to make it appear more consistent, professional and well-organized. For example, apply proper formatting to the cells like Ringgit Malaysia sign with two decimal places.
Task 3:
When a car has 60,000 miles, the sales representative can request a new car. Use conditional formatting to highlight ending mileage readings that are greater than 55,000 miles. If a sales representative has a car that is approaching its end-of-service date, Kevin will remind him that it is time to request a new car.
Task 4:
Insert four new columns into the Expense worksheet namely Actual mileage, Total expenses, Overage and Overage charge.
Task 5:
Calculate the Actual mileage which is the difference between the week's ending mileage and the week's beginning mileage.
Task 6:
Calculate the Total expenses which is a summation of the expenses for the week.
Task 7:
Calculate the Overage that requires a reference to the mileage lookup table and then determine how many miles over the limit, if any, the employee has driven (Hint: use nesting IF and VLOOKUP functions).
Task 8:
Calculate the Overage charge which multiplies the overage by the rate specified in the mileage table (Hint: use the VLOOKUP function as well).