How do I project monthly income in Excel?
 

If I want to project my income or cost for Nov. or Dec. and have values in
the other months, how can I project what my income or cost would be for
November or December based on the other months?

A lot of this depends on the nature of your income and / or expenses,
which may change from month to month. For instance, your heating
expense bill will increase in winter and decrease in summer. Your
summer vacation expenses go up in summer (duh) and decrease in winter.

When I have to do this work I try to base it on the key drivers for the
particular expense. For instance, in a previous life I had to develop
an operating expense budget for areas such as "coffee" and "office
supplies". In this scenario I figured that the number of people
employed in our department is the key driver, and made the assumption
that each person consumed office supplies at the same rate.

I had the previous year's total office supplies expense, and the total
department "population" by month. I then calculated the number of
"people-months". This is a dimensionless metric similar to a man-hour:
8 man-hours equals 4 guys working for 2 hours, or 8 guys working for 1
hour, etc. So if the population of the department started at 25 in
January and increased by 3 new hires each month, the total number of
people-months for the year is 25+28+31+34+37+40+43+46+49+52+55+58, or
498. Divide last year's total office supplies expense by 473 to derive
the "office supplies per person per month" figure. Suppose office
supplies expense for last year was $18,675. Divide by 498 for the per
person per month figure of $37.50.

Next, multiply your per person per month figure by the monthly
population you expect in each month. If the population increases by 3
each month again, multiply $37.50 by
61+64+67+70+73+76+79+82+85+88+91+94 = 930 to get $34,875. You can get
the individual monthly figures by multiplying individual monthly
poplutions by the $37.50.

This method works well for increasing, decreasing, and static
populations. Predicting the future is a guessing game at best, but
this at least applies some logic to the process.

Dave,

Thank you for taking the time to reply. This was a question on an Excel test
I took and I have not been able to find the answer. Most people I know don't
know Excel well enough to help, but I had values in all the cells of the
table for a year with the exception of 2 months. The question is "how would
you project the value that would go into these two cells?" I just came across
this site and I love it, because I read other peoples postings and replies
and then try it myself. If you know the answer to my question, would you
plese let me know? ThANK YOU.

Gabriele

"Dave O" wrote:

A lot of this depends on the nature of your income and / or expenses,
which may change from month to month. For instance, your heating
expense bill will increase in winter and decrease in summer. Your
summer vacation expenses go up in summer (duh) and decrease in winter.

When I have to do this work I try to base it on the key drivers for the
particular expense. For instance, in a previous life I had to develop
an operating expense budget for areas such as "coffee" and "office
supplies". In this scenario I figured that the number of people
employed in our department is the key driver, and made the assumption
that each person consumed office supplies at the same rate.

I had the previous year's total office supplies expense, and the total
department "population" by month. I then calculated the number of
"people-months". This is a dimensionless metric similar to a man-hour:
8 man-hours equals 4 guys working for 2 hours, or 8 guys working for 1
hour, etc. So if the population of the department started at 25 in
January and increased by 3 new hires each month, the total number of
people-months for the year is 25+28+31+34+37+40+43+46+49+52+55+58, or
498. Divide last year's total office supplies expense by 473 to derive
the "office supplies per person per month" figure. Suppose office
supplies expense for last year was $18,675. Divide by 498 for the per
person per month figure of $37.50.

Next, multiply your per person per month figure by the monthly
population you expect in each month. If the population increases by 3
each month again, multiply $37.50 by
61+64+67+70+73+76+79+82+85+88+91+94 = 930 to get $34,875. You can get
the individual monthly figures by multiplying individual monthly
poplutions by the $37.50.

This method works well for increasing, decreasing, and static
populations. Predicting the future is a guessing game at best, but
this at least applies some logic to the process.

You may want to look at =forecast() or =trend() in excel's help.

Gabriele wrote:

Dave,

Thank you for taking the time to reply. This was a question on an Excel test
I took and I have not been able to find the answer. Most people I know don't
know Excel well enough to help, but I had values in all the cells of the
table for a year with the exception of 2 months. The question is "how would
you project the value that would go into these two cells?" I just came across
this site and I love it, because I read other peoples postings and replies
and then try it myself. If you know the answer to my question, would you
plese let me know? ThANK YOU.

Gabriele

"Dave O" wrote:

A lot of this depends on the nature of your income and / or expenses,
which may change from month to month. For instance, your heating
expense bill will increase in winter and decrease in summer. Your
summer vacation expenses go up in summer (duh) and decrease in winter.

When I have to do this work I try to base it on the key drivers for the
particular expense. For instance, in a previous life I had to develop
an operating expense budget for areas such as "coffee" and "office
supplies". In this scenario I figured that the number of people
employed in our department is the key driver, and made the assumption
that each person consumed office supplies at the same rate.

I had the previous year's total office supplies expense, and the total
department "population" by month. I then calculated the number of
"people-months". This is a dimensionless metric similar to a man-hour:
8 man-hours equals 4 guys working for 2 hours, or 8 guys working for 1
hour, etc. So if the population of the department started at 25 in
January and increased by 3 new hires each month, the total number of
people-months for the year is 25+28+31+34+37+40+43+46+49+52+55+58, or
498. Divide last year's total office supplies expense by 473 to derive
the "office supplies per person per month" figure. Suppose office
supplies expense for last year was $18,675. Divide by 498 for the per
person per month figure of $37.50.

Next, multiply your per person per month figure by the monthly
population you expect in each month. If the population increases by 3
each month again, multiply $37.50 by
61+64+67+70+73+76+79+82+85+88+91+94 = 930 to get $34,875. You can get
the individual monthly figures by multiplying individual monthly
poplutions by the $37.50.

This method works well for increasing, decreasing, and static
populations. Predicting the future is a guessing game at best, but
this at least applies some logic to the process.



--

Dave Peterson