Cash Manager - Excel

Document Sample
Cash Manager - Excel Powered By Docstoc
					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.


PUT YOUR CELL FORMULAS IN THE ROWS BELOW. ANSWER THE QUESTIONS
 STARTING IN ROW 73.
Answer:
        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
           Add: Beginning cash                                5000
           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
Description: Cash Manager document sample