Scable Amortization schedule based on mileage.

please respond to each section of the problem and provide the steps to the answers by using Excel.

Overview:
The company carries insurance for accidents by Lyft drivers. There are multiple carriers. The expense is calc
Mileage based policies follow a similar concept as usage based prepaid amortization – they are amortized ba
Policy Terms: Mileage Based Policies Terms – March to February; the Straight Line Based Policies – Februar
Some policies require an initial prepaid balance with subsequent monthly invoices based on mileage reports
The company closes the books each month, with AP closed 5 days before month end. Periods are not re-op
Tasks:
1 Prepare a scalable prepaid amortization schedule and an accrual schedule using the information provided be
2 Please show journal entry(ies) that need to be recorded as part of Sept’20 month end close
3 At the end of each policy identify if the company is entitled to a refund or owes the carrier and show the amo
4 Please show your work and list out any assumptions. No hardcoding
Prepaid Balances Expense based on :
Ins #1
Ins #2
Ins #3
Mileage
Mileage
Straight Line
Accrual Balances Expense based on :
Ins #1
Ins #2
Ins #3
Mileage
Mileage
Straight Line
Premium Rates per Mile
Ins #1
$
0.035
Ins #2
$
0.019
Ins #3
SL
Mileage driven during the year:
Period
Ins #1
Mar’20
Apr’20
May’20
Jun’20
Jul’20
520,780
546,819
523,549
506,290
541,730
Aug’20
Sep’20
Oct’20
Nov’20
Dec’20
Jan’21
Feb’21
Relevant GLs:
12000 Prepaid Insurance
20000 Accounts Payable
21000 Accrued Liabilities
65000 Insurance Expense
557,982
574,722
591,963
609,722
628,014
646,854
666,260
ents by Lyft drivers. There are multiple carriers. The expense is calculated by mileage driven or straight line, depending on the
oncept as usage based prepaid amortization – they are amortized based on monthly actual mileage driven. Straight line policies
rms – March to February; the Straight Line Based Policies – February to January
alance with subsequent monthly invoices based on mileage reports provided by the company; others require installments base
nth, with AP closed 5 days before month end. Periods are not re-opened once closed
schedule and an accrual schedule using the information provided below
to be recorded as part of Sept’20 month end close
ompany is entitled to a refund or owes the carrier and show the amount
ssumptions. No hardcoding
2/29/2020
$
$
$
20,000
237,500
2/29/2020
Installment Terms
This is a usage based policy and expense is calculated using mileage driven. Estimated
annual premium of $175,000 invoiced in equal quarterly installments March, June, September
& December. The initial invoice was not received and paid until April 30, 2020.
This is a usage based policy and expense is calculated using mileage driven. $20,000 paid
in the month before policy is in effect, with monthly invoices based on prior month mileage;
however the September invoice was not paid until October 2020
This is a straight line policy – expense is recognized over the policy period. Fixed Annual
Premium of $950,000 paid in equal installments in February, June & December, however the
last invoice was not paid until January 2021.
Ins #2
656,999
689,849
632,777
618,255
661,533
681,379
701,820
722,875
744,561
766,898
789,905
813,602
others require installments based on an estimated annual premium. All policies have annual audits.