Problem:

You have been assgned the task of putting together a statement for the Deer Company that shows its expected inflows and outflows of cash over the months of July $2020$ through December $2020.$ The following data has been given to you so that a cash budget can be prepared.

Assumption$($s$)$

Expected gross sales for May through December are listed below as well as the miscellaneous expenditures during the months July through December:

Month Sales Miscellaneous Expenditures

May $$360,000$

June $$200,000$

July $$300,000$ $$15,000$

August $$455,000$ $$30,000$

September $$600,000$ $$25,000$

October $$550,000$ $$30,000$

November $$400,000$ $$35,000$

December $$700,000$ $$50,000$

$12\%$ of the sales in any given month are collected during that month. However, the firm has a credit policy so factor a $3\%$ discount into the current month's sales collection

$70\%$ of the sales in any given month are collected the following month after the sale

$20\%$ of the sales in any given month are collected during the second month following the sales

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

$100\%$ of its current month's raw materials purchases are paid in the following month

$5\%$ of the current month's expected sales are paid in wages and salaries on a monthly basis

$2\%$ of the current month's expected sales are made in monthly lease payments

$3\%$ of sales amounts to the monthly advertising expense

$10\%$ of sales are allocated in the months August, September, and October for R&D Expenditures

$$30,000$ will be paid in December as prepayment for insurance for the following year.

$$45,000$ will be paid in September for taxes.

$$40,000$ will be paid in December for taxes

$$25,000$ is the beginning cash balance in July

$$20,000$ is the target cash balance each month

Excel solut ion method: create cash budget for July $2020$ through December $2020,$ can you fully explain steps.