Hello, I am trying to create a prepaid expense schedule to reconcile with out register monthly.
This prepaid schedule should reconcile both expenses that are recognized monthly over a certain number of periods, or expenses that are recognized on a certain date. I can't seem to get my IF formula to work.
Basically, If the expense is recognized per period, "Y" or "N", I know how to have it recognize per period... But when I have expenses that are not recognized over periods, but the entire amount is recognized at a later date, the I don't know how to get the formula to put the balance in the sheet, but show it being recognized at that expense date.
Please help me.
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Here is the formula I have for the expenses recognized per period... I just recently added the Y and N fields with the expense date...
=IF($B5<=I$1,IF(SUM($H5:H5)>=$C5,"",$C5/$F5),"")
So, basically, if it is recognized over the period, then Y, otherwise it would use the expense date, rather than the Invoice date to insert the amount in the period in which it is recognized. But the invoice amount increases the register amount for the month, then decreases in the period of the expense date.
This prepaid schedule should reconcile both expenses that are recognized monthly over a certain number of periods, or expenses that are recognized on a certain date. I can't seem to get my IF formula to work.
Basically, If the expense is recognized per period, "Y" or "N", I know how to have it recognize per period... But when I have expenses that are not recognized over periods, but the entire amount is recognized at a later date, the I don't know how to get the formula to put the balance in the sheet, but show it being recognized at that expense date.
Please help me.
| Vendor | Invoice Date | Invoice Amount | Recognized Per Period | Expense Date | Periods | Balance Deferred |
| Salesforce.com | 1/31/2015 | $ 11,718.75 | Y | 5 | $ - | |
| Zuora | 2/1/2015 | $ 32,076.00 | Y | 11 | $ 20,412.00 | |
| Xactly | 3/7/2015 | $ 2,506.90 | Y | 10 | $ 1,754.83 | |
| G2E | 3/6/2015 | $ 6,811.70 | N | 9/28/2015 | 1 | $ - |
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Here is the formula I have for the expenses recognized per period... I just recently added the Y and N fields with the expense date...
=IF($B5<=I$1,IF(SUM($H5:H5)>=$C5,"",$C5/$F5),"")
So, basically, if it is recognized over the period, then Y, otherwise it would use the expense date, rather than the Invoice date to insert the amount in the period in which it is recognized. But the invoice amount increases the register amount for the month, then decreases in the period of the expense date.
