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 ithas a cash surplus? Please fill out the chart i have attached b