Since you are an Excel expert, your friend Dineo requested your assistance with regard to the accounting for his shuttle service business venture. Dizzy D Shuttle Services (Pty) Ltd, herein referred to as Dizzy D.
Dizzy D is a shuttle service that transports passengers to locations as requested by passengers using very sophisticated application technology. Dineo specifically needed you to create a spreadsheet to do his profit calculations for June 2019.
He employs 6 drivers for two separate times of travel (Peak Times 07:00 – 18:59 and Off-Peak Times 19:00 – 06:59).
You created the following spreadsheet:
Dineo provided you with the following information:
1. Dizzy D is a VAT vendor and VAT output is charged on shuttle services rendered
2. The Value Added Tax (VAT) percentage is 15% (refer to cell B7)
3. The gross profit margin is 25% on the cost of transport (refer to cell B8)
4. The driver’s names (refer to cell range B10:G10) and their applicable times of travel (B12:G12)
5. Each driver only drives during a specific time, either during Peak (P) or Off-Peak (OP) times
6. The number of kilometer’s (km) per week traveled by each driver (refer to cell range A12:G16)
7. Shuttle rates are charged at a rate per km traveled
- a. Peak R18.50 per km (excluding VAT) (refer to cell C4)
- b. Off-Peak R14.50 per km (excluding VAT) (refer to cell C5)
8. The income per month is calculated by multiplying the total number of kilometer’s traveled by the applicable rate
9. Cost per month for kms traveled includes fuel, maintenance as well as driver salaries