Balance tracking sheet

Hey everyone,

I just wanted to post a question for those Excel user with more experience than me. I work at a small gym where everything is still recorded with pen and paper. I am trying to automate a lot of these task by making user friendly sheets for our staff who aren't too computer savvy. With that said, here is what I am trying to do. Hopefully someone here will be able to help me figure this out.

At the gym, we have a physical balance sheet that we used to keep track of "store/member credit", which is what I am trying to automate with the least amount of user input.

The fields are as followed:
A -Members
B- Charge amount
C- Previous Bal
D- New balance.

The only input I want the other users to modify is the "Charge Amount" which will either be a credit or debit to the member's balance. I would like the spreadsheet to calculate the "New balance" , which is D= B+C, and also keep record of the prev/old balance. The formula for "New balance" is easy, the problem I am running into is automating the previous balance.

For example, lets say a member has a previous balance of -$5.00 and they add another DBT for -$2.00. The new balance will be -$7.00, but I need it to become the "previous Bal" also for future transactions. dont reference the orignal -$5. As it stands, the user inputs the "Charge (-$2)" which is added to the "previous Bal (-$5)" to calculate the "new Bal (-$7"). They also have update the "previous Bal (-$5)" with the "new balance (-$7)" so future transactions are calculated on the new Prev balance(-$7), and not the old previous Bal(-$5). I hope that makes sense to someone out there. I'm not sure if its something as simple as a formula or if VBA needs to be used. Hoping someone can point me in the right direction

Thank you
AlonzoMember Balance.png