Create an excel spreadsheet for this problem. Rusty Hardware, a small business in Maine needs your help. They need you to create a cash budget. Historical sales figures are given above. Question 1-...

Create an excel spreadsheet for this problem. Rusty Hardware, a small business in Maine needs your help. They need you to create a cash budget. Historical sales figures are given above. Question 1- use the data below to build a simple cash budget. What would be the cash balances for each month (Jun-Dec), if the firm does not borrow to maintain the minimum desired cash balance of $20,000? Format your worksheet appropriately, including headings with date functions so model is automated. Question 2 - After solving part A, make a copy (new tab). Now assume that the firm can borrow from the bank at a rate of 9% per annum to maintain its liquidity and meet its required minimum cash balance. In addition, if the firm has funds in excess of the minimum, it will use the excess to pay off any previous balance. Format your worksheet appropriately.
Assumptions: Individuals account for 40% of the firm's sales and they pay in cash. The other 60% of sales are to contractors with credit accounts and they have up to 60 days to pay. As a result about 20% of sales to contractors are paid one month after the sale and the other 80% is paid two months after the sale. Each month the firm purchases inventory equal to about 50% of the following month's sales. About 30% of this inventory is paid for in the month of delivery, while the remaining 70% is paid one month later. Total monthly wages are $16,000, including benefits. Salaried employees are paid $22,000, also including benefits. Due to prior borrowing, the firm has monthly interest payment of $6,000 and montly principal payment of $450. Monthly maintenance of the stores is $1,200, except in October to December when snow removal costs will add $400 per month. Sales taxes are 7% of quarterly sales and must be paid in June, September and December. Other taxes alre also paid during those months and are expected to be about 4% of quarterly sales in each of those months. Onwers wish to maintain a minimum cash balance of $20,000. The cash balance at the end of May is expected to be $15,000 (before any borrowing or investing).