Question

V had actual sales and purchases for July and August 2020 as given in the Excel spreadsheet, along with its forecast sales and purchases for the period September 2020 through February 2021.

The firm makes 30% of all sales for cash and collects 35% of its sales in each of the two months following the sale. Other cash inflows are expected to be $22,000 in September and February, $25,000 in November and January, and $37,000 in December. The firm pays cash for 20% of its purchases. It pays for 40% of its purchases in the following month and for 40% of its purchases two months later.

Wages and salaries amount to 15% of the preceding month’s sales. The firm must pay lease expenses of $30,000 per month. Interest payments of $20,000 are due in November and February. A principal payment of $50,000 is also due in February. The firm expects to pay a cash dividend of $30,000 in November and February. Taxes of $120,000 are due in April. The firm also intends to make a $55,000 cash purchase of fixed assets in October.

Assuming that the firm has a cash balance of $42,000 at the beginning of September and that its desired minimum cash balance is $25,000, prepare a cash budget for September through February using the Excel template.

Answer the following questions by filling in the blanks. Give your answers rounded to the nearest whole number in each case.

(a) What is the Total Cash In for the month of September?

(b) How much is paid in October for purchases made in August?

(c) What is the Total Cash Out for the month of November?

(d) How much are the Net Cash Flows in the month of December?

(e) What is the Beginning Cash balance in January?

(f) What is the Ending Cash balance in February?

(g) What is the Cash Surplus in the month of September?

(h) If the firm requests a line of credit from a bank to cover its largest borrowing need over the six months, how large should the line be?

(i) What will be the value of the firm’s Accounts Receivable forecast for the end of the month of February?

Please give a step-by-step answer and clearly explain the problem solving process.

The excel spreadsheet provides the following information :

Year	Month	Sales	Purchases
2020	July	$310,000	$220,000
2020	August	$350,000	$250,000
2020	September	$270,000	$240,000
2020	October	$260,000	$200,000
2020	November	$240,000	$180,000
2020	December	$280,000	$210,000
2021	January	$300,000	$200,000
2021	February	$350,000	$190,000

Percentage of sales for cash	30%
Percentage of sales collected in 1 month	35%
Percentage of sales collected in 2 months	35%
Other cash receipts (Sep/Feb)	$22,000
Other cash receipts (Nov/Jan)	$25,000
Other cash receipts (Dec)	$37,000
Percentage of purchases for cash	20%
Percentage of purchases paid in 1 month	40%
Percentage of purchases paid in 2 months	40%
Wages and salaries (% of sales)	15%
Lease expenses to be paid per month	$30,000
Interest payments due (Nov/Feb)	$20,000
Principal payment due (Feb)	$50,000
Cash dividend to be paid (Nov/Feb)	$30,000
Cash purchase of fixed assets in October	$55,000
Tax payment due (Apr)	$120,000
Cash balance at beginning of September	$42,000
Minimum cash balance level	$25,000

Answer 1

Steps in solving que.

1) use actual sales/ purchases figures of July and august to get amount of cash collected and cash paid in month of september.

2) refer image .

Answers :-

A) Total Cash In for september - $334000 (excluding opening balance )

B) $100000 is paid in October for purchases made in August.

C) Total cash out for November - $331000

D) Net cash flows in December - $36000(excluding opening balance)

E) Beginning cash balance January - $61000

F) Ending cash balance February - $43000

G) Cash surplus in September - $15500 (excluding beginning balance)

H) Line of credit, credit required from bank would be $48500 in November

I) Account Receivable forecast February - $350000