Could you provide me with a web-site or excel program that will assist
me with preparing a montly cash payment summary!!! The easier to
understand the better! Thank you!

---

Clarification of Question by baseball2-ga on 28 Apr 2005 20:14 PDT

I could really use some help with this if at all possible by tomorrow night!

---

Clarification of Question by baseball2-ga on 29 Apr 2005 17:21 PDT

This is the question I am trying to work with. I am not asking for an
answer, rather some good advice on how to calculate. Any guidance
would be greatly appreicated!

The Denver Corporation has forecast the following sales for the first
seven months of the year:

January		$10,000	May		$10,000
February		12,000	June		16,000
March		14,000
April		20,000
	July		18,000
Monthly material purchases are set equal to 30 percent of forecasted
sales for the next month. Of the total material costs, 40 percent are
paid in the month of purchase and 60 percent in the following month.
Labor costs will run $4,000 per month, and fixed overhead is $2,000
per month. Interest payments on the debt will be $3,000 for both March
and June. Finally, the Denver sales force will receive a 1.5 percent
commission on total sales for the first six months of the year, to be
paid on June 30.

Prepare a monthly summary of cash payments for the six-month period
from January through June. (Note: Compute prior December purchases to
help get total material payments for January.)

Baseball2 ?

When I?d originally seen your note, I thought about recommending cash
management software packages ? but it would take you longer to set
them up and learn them than is worth the effort.

Instead, I?ve set up a spreadsheet to show the Cash Budget.  It?s
reasonably simple ? you?re not interested in net income here (as in a
normal income statement) ? but rather in CASH IN and CASH OUT.  This
spreadsheet leaves some work for you to do ? though I?ve set up all of
the fixed cost items.

Open (and save on your computer) this spreadsheet:
http://www.mooneyevents.com/cashflow.xls

Once you?ve saved it on your computer, you?re set to work on it. 
Don?t be alarmed by the red numbers representing negative cash flow at
the bottom ? once you enter sales data, a lot of them will go away.

Here are the steps to completing the cash flow:
1.	enter your monthly sales data on line 6 ? it?s all set to calculate
everything.  Note that there?s no assumption that there are accounts
receivable (AR) that would normally delay payments.  This must be a
cash business.
2.	Account Payable ? for your materials ? are split into two lines:
the first for CURRENT MONTH and the second line for what gets paid in
the next month.  I could have labeled this NEXT MONTH but typically in
accounting these would be items paid NET 30 ? so I?ve followed that
convention on line 10
3.	note that in January you?re paying 60% of the materials costs for
items built in December.  That was based on the January sales forecast
? which we have right here ? so there?s no need to ask what was sold
in December.

If you have any questions, don?t hesitate to ask for a clarification.

Best regards,

Omnivorous-GA

---

Request for Answer Clarification by baseball2-ga on 30 Apr 2005 16:21 PDT

Wow! This looks great. It appears to me all I need to do is plug in
the cash numbers? Appreciate your help!

---

Clarification of Answer by omnivorous-ga on 30 Apr 2005 17:56 PDT

Baseball2 --

That should do it.  When I originally set it up, I thought that we
needed December sales -- but since all materials payments are based on
Jan. or later -- you don't.

Glad that we could help.

Best regards,

Omnivorous-GA

Thank you! You did more than I ever expected!