You have been assignedthe task of putting together a statement for the ACME Com- panythat shows its expected inflows and outflows of cash over themonths of July 2016 through December 2016.

You have been giventhe following data for ACME Company:

(1)Expected grosssales 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 salesin any given month are collected during that month. However, thefirm has a credit policy of 3/10 net 30, so factor a 3% discountinto the current month’s sales collection.

(3) 75% of the salesin any given month are collected during the following month afterthe sale.

(4)13% of the sales inany given month are collected during the second month following thesale.

(5)The expectedpurchases of raw materials in any given month are based on 60% ofthe expected sales during the following month.

(6)The firm pays 100%of its current month’s raw materials purchases in the followingmonth.

(7)Wages and salariesare paid on a monthly basis and are based on 6% of the currentmonth’s expected sales.

(8)Monthly leasepayments are 2% of the current month’s expected sales.

(9)The monthlyadvertising expense amounts to 3% of sales.

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

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

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

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

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

TO DO

a. Prepare a cashbudget for July 2016 through December 2016 by creating a com- binedspreadsheet that incorporates spreadsheets similar to those inTables 4.8, 4.9, and 4.10. Divide your spreadsheet into threesections:

(1) Total cashreceipts (2) Total cash disbursements (3) Cash budget covering theperiod of July through December

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

b. Based on youranalysis, briefly describe the outlook for this company over thenext 6 months. Discuss its specific obligations and the fundsavailable to meet them. What could the firm do in the case of acash deficit? (Where could it get the money?) What should the firmdo if it has a cash surplus?