M
Matt
Hi, does anyone have an idea that could help me automate the process I
describe below?
I have a spreadsheet with a couple worksheets. On the first worksheet, I
have a chart set up where I enter cash movements into or out of the
account(using either a negative or positive number). In the first column I
enter the date of the movement, and in the adjacent column I enter the amount
of the movement. I have a "total" formula at the top that sums all the
movements to give me the balance of the account. Somedays a cash movement
will occur and somedays there won't be any.
On the 2nd worksheet, I set up another chart that calculates the balance of
the account out on a daily basis which I fill out at the end of every month.
In the first column, I enter the dates for the present month end. i.e.
February 1st -28th would occupy the first column on the worksheet. On the
adjacent column I enter what the total balance was on that day. This involves
me manually going back to the 1st worksheet and finding the balance for that
particular day by summing up all of the cash movements up to and including
that particular day and entering it onto the 2nd work sheet...When the 2nd
worksheet is completed I now have a running daily balance for that particular
month.
The third worksheet I use to calculate interest. It is basically another
chart set up in a similar way to the 2nd spreadsheet, with each day for the
particular month running down the first column. In in the adjacent column I
enter what the interest rate was for that particular day. Then in the third
column, I enter a formula that pulls the balance for the particular day from
the 2nd worksheet, and multiplies it by the interest rate for that particular
day and then divides that by 100. I drag this formula down for each day of
the month, giving me the interest earned for each day during that month. I
total it at the bottom, giving me the total interest earned on the account
during the month.
My question is- is there a way to automate this process? Where I could enter
the cash movements on the first worksheet as usual, but the 2nd and third
steps I take to find the interest earned could be an automatic process? Any
help, ideas, or comments would be greatly appreciated. Please let me know if
any further explanation is needed.
describe below?
I have a spreadsheet with a couple worksheets. On the first worksheet, I
have a chart set up where I enter cash movements into or out of the
account(using either a negative or positive number). In the first column I
enter the date of the movement, and in the adjacent column I enter the amount
of the movement. I have a "total" formula at the top that sums all the
movements to give me the balance of the account. Somedays a cash movement
will occur and somedays there won't be any.
On the 2nd worksheet, I set up another chart that calculates the balance of
the account out on a daily basis which I fill out at the end of every month.
In the first column, I enter the dates for the present month end. i.e.
February 1st -28th would occupy the first column on the worksheet. On the
adjacent column I enter what the total balance was on that day. This involves
me manually going back to the 1st worksheet and finding the balance for that
particular day by summing up all of the cash movements up to and including
that particular day and entering it onto the 2nd work sheet...When the 2nd
worksheet is completed I now have a running daily balance for that particular
month.
The third worksheet I use to calculate interest. It is basically another
chart set up in a similar way to the 2nd spreadsheet, with each day for the
particular month running down the first column. In in the adjacent column I
enter what the interest rate was for that particular day. Then in the third
column, I enter a formula that pulls the balance for the particular day from
the 2nd worksheet, and multiplies it by the interest rate for that particular
day and then divides that by 100. I drag this formula down for each day of
the month, giving me the interest earned for each day during that month. I
total it at the bottom, giving me the total interest earned on the account
during the month.
My question is- is there a way to automate this process? Where I could enter
the cash movements on the first worksheet as usual, but the 2nd and third
steps I take to find the interest earned could be an automatic process? Any
help, ideas, or comments would be greatly appreciated. Please let me know if
any further explanation is needed.