CHAPTER 4 Cash Flow and Financial Planning 151

You have been assigned the task of putting together a statement for the ACME Com- pany that shows its expected inflows and outflows of cash over the months of July 2016 through December 2016.

You have been given the following data for ACME Company: (1)Expected gross sales for May through December, respectively, are $300,000,

$290,000, $425,000, $500,000, $600,000, $625,000, $650,000, and $700,000. (2)12% of the sales in any given month are collected during that month. However,

the firm has a credit policy of 3/10 net 30, so factor a 3% discount into the

current months sales collection. (3)75% of the sales in any given month are collected during the following month

after the sale. (4)13% of the sales in any given month are collected during the second month

following the sale. (5)The expected purchases of raw materials in any given month are based on 60%

of the expected sales during the following month. (6)The firm pays 100% of its current months raw materials purchases in the

following month. (7)Wages and salaries are paid on a monthly basis and are based on 6% of the

current months expected sales. (8)Monthly lease payments are 2% of the current months expected sales. (9)The monthly advertising expense amounts to 3% of sales.

(10) R&D expenditures are expected to be allocated to August, September, and October at the rate of 12% of sales in those months.

(11) During December a prepayment of insurance for the following year will be made in the amount of $24,000.

(12) During the months of July through December, the firm expects to have miscella- neous expenditures of $15,000, $20,000, $25,000, $30,000, $35,000, and $40,000, respectively.

(13) Taxes will be paid in September in the amount of $40,000 and in December in the amount of $45,000.

(14) The beginning cash balance in July is $15,000. (15)The target cash balance is $15,000.

TO DO

a. Prepare a cash budget for July 2016 through December 2016 by creating a com- bined spreadsheet that incorporates spreadsheets similar to those in Tables 4.8, 4.9, and 4.10. Divide your spreadsheet into three sections: (1) Total cash receipts

(2) Total cash disbursements (3) Cash budget covering the period of July through December

The cash budget should reflect the following: (1) Beginning and ending monthly cash balances (2) The required total financing in each month required (3) The excess cash balance in each month with excess

Spreadsheet Exercise

nullnullnullnull