Bill payment project

rameshppc

Board Regular
Joined
Jun 10, 2017
Messages
108
Hi experts, i think today i came with a challenging excel formula requirement.

I am going to announce a scheme for my customers, and i need to administrate the scheme data in my excel with your help by getting amazing formulas.

The concept of my scheme is, to collect 50rs from my customers in daily basis and pay their mobile bill (recharge), Eb bill, Dth recharge, Lic premium, Bike insurance & health insurance etc on time based on the customers recurring amount. I just remove the tension of my customers for remembering the payment dates also reduce the burden of paying a big amount on payment day.

Hope you will understand the concept.

Here i have attached a excel screen shot for your kind reference to understand what i am expecting.

First Table

Colum A is S.no, Colum B is the type of payments returned as “description”, Colum C is the number of mobile phones available with customer, Colum D is how much we need to pay the bill amount for the customer, Colum E is the lead time of payment that how many days once we need to pay, Colum F is the possible date of bill payment, calculated from accumulation of customer payment, Colum G is the accumulation amount received from the customer on the possible bill date(explained in detail in actual cells). Colum H is actual due date of bill payment advised by customer (explained in detail in actual cells), Colum I is , if the actual due date of bill payment fell before the possible bill date, the customer need to pay the difference amount from what we accumulated from the customer on daily basis (explained in detail in actual cells). Colum J is Total customer paid amount. Colum K is the cumulative amount of how much we paid for customer bill.

Here we need formula for where we highlighted on yellow..

J5 (ending date)

The ending date will be calculate as the accumulation of customer amount reach the total bill value here we complete one cycle of all bill payments to the customer.

k5 is the balance amount

If a customer not willing to continue this scheme, we will pay the balance amount to customer and close the account.

Also pls consider the below while making the formulas..

Need provision for increasing the number of rows, (additional payment bills (loan etc..) as per wish of customer)

Need provision for decreasing the number of rows or accept to put zero for some rows

Table2

This table is continuity of table 1 to pay the next bill date of payments.

I hope the final result will come Once all the formulas created. If any changes required, i will come back for your great answers.

Thank you so much for reading my content and ready to help me for this project.
 

Attachments

  • bill payment project.JPG
    bill payment project.JPG
    160 KB · Views: 30

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

rameshppc

Board Regular
Joined
Jun 10, 2017
Messages
108
Hi everyone, Thanks for watching my post and big thanks for who tried to resolve this excel for me.
Almost i completed the excel formulas ss far as I know, but the result is not reached till the requirement. Anybody advise their own creation for this project and it will be more helpful for me. I think my previous post is sufficient for understand about my project. Hope someone will respond for my post. Many thanks in advance..
 

rameshppc

Board Regular
Joined
Jun 10, 2017
Messages
108
Hi everyone, I am almost completing the bill payment project by guidance of my friends. But a formula not giving the expected result, kindly help to solve. I am using below formula in a column for lookup the values. =INDEX($B$8:$K$8,MAX(INDEX((L2=$B$11:$K$35)*(COLUMN($B$8:$K$8)-COLUMN($A$8)),))) here the table b11 to k35 having similar dates and so the lookup value in b8:k8 showing wrong. Pls help me solve
 

Watch MrExcel Video

Forum statistics

Threads
1,128,165
Messages
5,629,068
Members
416,363
Latest member
zaveedd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top