Please help, the work should be done with Excel so please show me what you did as well. Thank you!

Prepare a spreadsheet to provide:

A cash budget for Brightside Brewery for January through June of 2021 based on the following information:

Sales:

November 2020	$180,000
December	360,000
January 2021	190,000
February	290,000
March	210,000
April	210,000
May	180,000
June	230,000
July	350,000

The credit manager says that our collection pattern is as follows:

90% of sales are cash sales

80% of new Accounts Receivable are collected in 30 days

18% of new Accounts Receivable are collected in 60 days

2% of new Accounts Receivable are bad debts and will not be received.

Manufacturing costs are equal to 25% of the following months sales. Brightside takes pride in paying their suppliers in a timely fashion. 85% of Purchases are paid in the same month as the purchase and the balance is paid in the following month.

Monthly selling and administrative expense are as follows: (hintits a cash budget)

Salaries	$57,000
Wages	20% of the current months sales
Communications	900
Amortization	2,300
Rent	6,200
Utilities	800
Office Supplies Expense	250
Interest	2,800

Other cash outlays:

Taxes $18,000 in January and March

Purchase of new equipment $28,900 in February and $18,400 in May

The cash balance on January 1 is $30,000. There is a loan outstanding on January 1 of $31,000. The minimum cash balance is $30,000. Any cash balance exceeding this minimum would be used to pay down the outstanding loan. Any cash balance of over $60,000 will be invested in GICs. A balance between the minimum and the maximum must be maintained.

Dont expect to automate the worksheet 100%. This would require some significant programming in excel (nested =if functions). But do use cell references rather than embedding numbers in formulas (i.e. B5*A2 rather B5*0.10) and use absolute cell addressing when appropriate ($B$5 rather than B5).