Google Answers Logo
View Question
Q: Finance ( Answered 5 out of 5 stars,   0 Comments )
Subject: Finance
Category: Business and Money > Finance
Asked by: baseball2-ga
List Price: $15.00
Posted: 25 Apr 2005 12:02 PDT
Expires: 25 May 2005 12:02 PDT
Question ID: 514005
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.)
Subject: Re: Finance
Answered By: omnivorous-ga on 30 Apr 2005 09:57 PDT
Rated:5 out of 5 stars
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:

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,


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,

baseball2-ga rated this answer:5 out of 5 stars
Thank you! You did more than I ever expected!

There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy