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.
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.