Prepaid Excel Tracker

[manishm]

I want to prepare a prepaid expense tracker in excel. But get stuck at certain places.

I have an Invoice for goods/ services for $120000/- for the period 15 April 2017 to 14 April 2018. My financial year is from 1st April 17 to 31 March 2018

I have received this invoice on 25 Jun 2017 and booked the same on 25th June 2017, hence i would like to expense out value upto the period 25 Jun 2017 and the remaining period i.e. from 26 Jun 2017 to 14 Apr 2018 to be prepaid every month on the basis of number of days in that month.

If i do manual working without any automization, the values for the above example would be as given below -

a) Invoice value - $120000/-
b) Expensed out till 25 Jun 2017 = (25 Jun 17 - 15 Apr 2017 = 72 days of expense i.e. $120000/365*72 = $23,671/23. Balance amount to be prepaid is $96,328/77
c) Prepaid after 26 Jun 2017 = (26 Jun 2017 to 14 Apr 2018 = 293 days for prepaid). So in this case i will prepaid - $96,328/77 upto 14 Apr 2018. For remaining period of June it will be $1643.83, July17 - $10191.78, Aug -$10191.78, Sep - 9863.01...... so on upto 14 Apr 2018.

I have prepared an excel, can you please help me to get it to completion?.

Your quick action will be highly appreciated.

 

[Mahenderlalwani]

Link to Excel File

Type the Inputs as follows

In Cell A5 - Amount (120000)
In Cell B5 - Date of Invoice (25 Jun 2017)
In Cell C5 - Period Start Date (15 Apr 2017)
In Cell D5 - Period End Date (14 Apr 2018)

Formula as Follows

In Cell B9 - "=CONCATENATE("To Be recognized as expense Upto "&TEXT(B5,"dd/MM/YYYY"))"
In Cell B10 - "=ROUND($A$5/($D$5-$C$5+1)*($B$5-$C$5+1),2)"
In Cell C9 - "=CONCATENATE("Prepaid Expense Upto "&TEXT(EOMONTH($B$5,ROW()-10),"DD/MM/YYYY"))"
In Cell C10 - "=MAX(ROUND(IF(RIGHT(B10,10)-$D$5>0,($D$5-RIGHT(B9,10))*$A$5/($D$5-$C$5+1),ROUND($A$5/($D$5-$C$5+1)*(RIGHT(B10,10)-RIGHT(B9,10)),2)),2),0)"
In Cell D9 - "=CONCATENATE("Prepaid Expense Upto "&TEXT(EOMONTH($B$5,ROW()-10),"DD/MM/YYYY"))"
In Cell D10 - "=MAX(ROUND(IF(RIGHT(B11,10)-$D$5>0,($D$5-RIGHT(B10,10))*$A$5/($D$5-$C$5+1),ROUND($A$5/($D$5-$C$5+1)*(RIGHT(B11,10)-RIGHT(B10,10)),2)),2),0)"


HOPE THE EXCEL IN LINK HELPS YOU.

 

[manishm]

Thank you for your help Sir. But it is not only one expense. I have multiple line items of such expenses, so for each line item there is a single row.

So I need to get all relevant details in a single row for that single transaction.

Once again thank you for your help.

I am trying to use the below formula, but is not helping me for some reason -

IF(OR(AL$6>$K8,AL$6<$G8),0,IF((OR($K8=AJ$6,$G8=AL$6),((AJ$6-$J8-1)*($T8/$Q8)),if(or($G8<>AL$6,$K8<>AL$6),($T8/$Q8*AL$5),0)

It shows me error stating there is some problem with the formula.
If i use the below formula, i get weird numbers.
=IF($G8=X$6,((X$5-$H8-1)*($T8/$Q8)),IF($K8=X$6,((X$5-$J8-1)*($T8/$Q8)),IF(OR($G8<>X$6,$K8<>X$6),($T8/$Q8*X$5),0)))

Please help

 

[manishm]

Thank you for your help Sir. But it is not only one expense. I have multiple line items of such expenses, so for each line item there is a single row.

So I need to get all relevant details in a single row for that single transaction.

Once again thank you for your help.

I am trying to use the below formula, but is not helping me for some reason -

IF(OR(AL$6>$K8,AL$6<$G8),0,IF((OR($K8=AJ$6,$G8=AL$6),((AJ$6-$J8-1)*($T8/$Q8)),if(or($G8<>AL$6,$K8<>AL$6),($T8/$Q8*AL$5),0)

It shows me error stating there is some problem with the formula.
If i use the below formula, i get weird numbers.
=IF($G8=X$6,((X$5-$H8-1)*($T8/$Q8)),IF($K8=X$6,((X$5-$J8-1)*($T8/$Q8)),IF(OR($G8<>X$6,$K8<>X$6),($T8/$Q8*X$5),0)))

Please help

Is there anyone who can help me?