This is to calculate a Monthly Benefit in Kind for a company car.
The percentage (%) rates to use are held on a Table. The TblBIKmthly table is access by Month and the total business kilometres (Km) for that month will determine what Band the Km fits into. Once the Band is found the % Rate for that Band is used in the calculations.
Calculations are only for Kilometres (Km) ignore Miles in tables
The BIK Monthly Spreadsheet and the Monthly BIK Revenue Guidelines are examples of how the Notional Pay is calculated.
Only the Business Mileage from the Form is used to calculate Notional pay.
Create Monthly Form and probably a subform for all the records (a bit like (not exactly the same as) the Benefit-in-Kind form where the details are at the top and weekly amounts below). There is a Car BIK Monthly Table that will probably have 90% of the fields required for the sub form.
- See example BIK spreadsheet for layout, The Month fields will probably have to be a subform as we do not know how many months there will be as an employee could get 1 or 2 additional cars a year
- Select by Employee and show all records for Employee in Year / Month order.
- allow user to select by Year and display only records for the year.
- Calculate Notional Pay for each month.
- Show totals at end
Possible, Query to extract all Employee BIK by year/by month range
(e.g. Year 2013 – Months 1 to 3 or months 1 to 12 (could be duplicate months if a new car that month)
Create Excel export extract file location can be My Documents but should have the ability to change the location (this will be based on the above query)
– Employee Number and Monthly Date range and Registration and Notional pay
Create report (from the form details) with Selection by Registration or Employee Number or Date Range to show the Monthly CAR BIK. Should be possible to Select Month range 1 to 3 or 2 to 8 etc. Could be like the Benefit-in-kind report with no car details at the top.
Front end and Back end database
Users not to have access to modules ChkPsw and ChkPswSw
- BIK Monthly spreadsheet. (has comments in relevant fields).xls
- Bik-monthly-ready-reckoner .pdf (which are the values in tblBIKmthly) table
- Monthly BIK Revenue Guidelines .doc