Cash Budget Problem Two

You have been assigned the task of putting together a statement for the ACME Company

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:

Expected gross sales for May through December, respectively, are $$300,000,$ $$350,000,$ $$450,000,$ $$500,000,$ $$600,000,$ $$625,000,$ $$650,000,$ and $$700,000.$

$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 month$$s sales collection.

$75\%$ of the sales in any given month are collected during the following month after the sale.

$13\%$ of the sales in any given month are collected during the second month following the sale.

The expected purchases of raw materials in any given month are based on $50\%$ of the expected sales during the following month.

The firm pays $100\%$ of its current month$$s raw materials purchases in the following month.

Wages and salaries are paid on a monthly basis and are based on $10\%$ of the current month$$s expected sales.

The monthly advertising expense amounts to $5\%$ of sales.

R&D expenditures are expected to be allocated to August, September, and October at the rate of $15\%$ of sales in those months.

During December a prepayment of insurance for the following year will be made in the amount of $$20,000.$

During the months of July through December, the firm expects to have miscellaneous expenditures of $$5,000,$ $$15,000,$ $$20,000,$ $$25,000,$ $$30,000,$ and $$20,000,$ respectively.

Taxes will be paid in September in the amount of $$30,000$ and in December in the amount of $$35,000.$

The beginning cash balance in July is $$15,000.$

The target cash balance is $$15,000.$

TO DO

a$.$ Prepare a cash budget for July $2016$ through December $2016$ by creating a combined spreadsheet

that incorporates spreadsheets similar to those in Tables $4\mathrm{.}8,4\mathrm{.}9,$ and $4\mathrm{.}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

b$.$ Based on your analysis, briefly describe the outlook for this company over the next $6$ months.

Discuss its specific obligations and the funds available to meet them.

What could the firm do in the case of a cash deficit? $($Where could it get the money?$)$ What should the firm do if it has a cash surplus?

ACME Company

Cash Budget July through December $2016$

$\%\text{'}$s May June July August September October November December

Sales Forcasts $(000\text{'}$s$)300350450500$

Cash Sales $12\%36425460$

Less: Discount $3\%1\mathrm{.}081\mathrm{.}261\mathrm{.}621\mathrm{.}8$

Collections of A$/$R

Lagged one month $75\%225262\mathrm{.}5337\mathrm{.}5$

Lagged two months $13\%3945\mathrm{.}5$

Total Cash Receipts $34\mathrm{.}92265\mathrm{.}74353\mathrm{.}88441\mathrm{.}2$

Purchases $(\%$ of Sales$)50\%1752252500$

Cash Purchases

Payment of A$/$P

Lagged one month $100\%175225250$

Wages $10\%30354550$

Advertising Expense $5\%1517\mathrm{.}522\mathrm{.}525$

R&D Expenditures $15\%75$

Prepaid Insurance

Other expenses $515$

Taxes

Total Cash Disbursement $45227\mathrm{.}5297\mathrm{.}5415$

Cash Bu

Cash Budget Problem Two

You have been assigned the task of putting together a statement for the ACME Company

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:

Expected gross sales for May through December, respectively, are $$300,000,$ $$350,000,$ $$450,000,$ $$500,000,$ $$600,000,$ $$625,000,$ $$650,000,$ and $$700,000.$

$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 month$$s sales collection.

$75\%$ of the sales in any given month are collected during the fol

dget:

Total Cash Receipts $353\mathrm{.}88441\mathrm{.}2$

Less: Total Cash Disbursements $297\mathrm{.}5415$

New Cash Flow $56\mathrm{.}3826\mathrm{.}2$

Add: Beginning Balance $1571\mathrm{.}38$

Ending Balance $71\mathrm{.}3897\mathrm{.}58$

Less: Minimum Cash Balance $1515$

Required total financing $($notes payable$)00$

Excess Cash balance $($marketable securities$)56\mathrm{.}3882\mathrm{.}58$