Cash Manager - Excel

Document Sample

```					ENTER YOUR NAME HERE =>                                              c40fe84f-6fc7-44ea-8b27-88bd69d010af.xls

3-8 Cash Budget - Basic BUT MODIFIED FOR EXCEL ASSIGNMENT
This is Grenoble again (see Problem 3-8, pg. 126, in textbook), but one year later.
It buys and sells on the same credit terms as last year.
You will do the cash receipts, disbursements, and budget in Excel and
report on the cash manager's actions. Turn in printout and cell formulas.
Use cell formulas to add up (SUM) the cash receipts and disbursements and calculate net
cash flow, ending cash, future months' beginning cash, and required total financing or
excess cash balance. Yellow shading indicates that you need to enter a number or formula.
Grenoble Enterprises had sales of \$40,000 in March and \$45,000 in April. Forecast sales for May, June
and July are \$65,000, \$75,000, and \$90,000, respectively. The firm has a cash balance of \$5,000 on
May 1 and wishes to maintain a minimum cash balance of \$5000. Given the following data, prepare and
interpret a cash budget for the months of May, June, and July.

1 The firm makes 10% of sales for cash; 30% are collected in the next month,
and the remaining 60% are collected in the second month following the sale.

2 The firm receives other income of \$2,000 per month.

3 The firm's actual or expected purchases, all made for cash, are \$50,000,
\$70,000, and \$80,000 for the months of May through July, respectively.

4 Rent is \$3,000 per month.

5 Wages and salaries are 18% of the previous month's sales.

6 Cash dividends of \$3,000 will be paid in June.

7 Payment of principal and interest of \$4,000 is due in June.

8 A cash purchase of equipment costing \$6,000 is scheduled in July.

9 Taxes of \$6,000 are due in June.

STARTING IN ROW 73.
CASH RECEIPTS SCHEDULE:
March    April  May  June  July
Sales receipts          \$40,000 \$45,000
Percentages:                             ---  ---   ---
Cash Sales                  10%
Lag 1 mo.
Lag 2 mo.
Other income
Total cash receipts

CASH DISBURSEMENTS SCHEDULE:                     May         June       July
Disbursements:
Purchases
Rent
Wages & salaries
Dividends
Principal & Interest
Purchase of new equipment
Taxes due
Total cash disbursements

CASH BUDGET:                                     May         June       July
(for cash receipts and disbs, reference cells above)
Total cash receipts
Total cash disbursements
Net cash flow
Ending cash
Minimum cash
Required total financing
or
Excess cash balance

a. How much of a credit line should the firm establish? ________________________
(use rule of thumb from class and handout)
b. In the box in row 78, explain exactly what the cash manager will do at the end of May,
from the end of May to the end of June, and
from the end of June until the end of July (with credit line and money fund).

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 26 posted: 12/16/2010 language: English pages: 1
Description: Cash Manager document sample