Automated Prepaid Expenses JV in Excel

Hello All,

Attached is a file which contains the automated prepaid calculation. No Hassles of working out how much is accrued, how much is pending, etc. Just Update the table in the first sheet. And rest will happen automatically. Of course, you are free to open the hood and check out how it works.

Step:1- Go the last row of the blue table and add a serial number 5, which will automatically extend the table. Fill up other fields as well. All fields are mandatory. Once you complete your entry, make sure that the Checkpoint column (H column is ZERO). If it’s not, there is some error in the input.

Step:2- Go the next tab – “Journal Entry”

Change the date in D2 cell. Make sure it’s the first day of the month only. Refresh the Pivot and you will have the JV ready. Classified by Type of Expenses and by Cost Center.

Under the hood:-

Unhide the columns from “I” onwards and you will see a range of formulas- date, sumifs, min, max, etc. You are free to meddle with it.