please do it in an excel sheet

Top Plubming, a small business in Maine needs your help. They need you to create a simple and complex cash budget. Historical sales figures are given above. Assumptions: Indlviduals account for 35% of the firm's sales and they pay in cash. The other 65% of sales are to contractors with credit a ccounts and they have up to 60 days to pay. As a result about 15% of sales to contractors are paid one month after the sale and the other 85% is paid two months after the sale. Each month the firm purchases inventory equal to about 55% of the following month's sales. About 25% of this inventory is paid for in the month of delivery, while the remaining 75% is paid one month later. Total monthly wages are $17,000, including benefits. Salaried employees are paid $20,000, also including benefits. Due to prior borrowing, the firm has monthly interest payment of $5,500 and montly principal payment of $475. Monthly maintenance of the stores is $1,150, except in October to December when snow removal costs will add $500 per month. Sales taxes are 8% 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 3% of quarterly sales in each of those months. Orwers wish to maintain a minimum cash balance of $25,000. the cash balance at the end of May is expected to be $12,000 (before any borrowing of investing). Part A (Simple cash budget) - use the data below to build a simple cash budget. What would be the cash balances for each month (JunDecl. If the firm does not borrow to maintain the minimum desied cash balance? formot your worksheet oppropriately, including heodings with date functions so model is outomoted. Part B (Complex cash budget) - After solving part A (smple cash budget), make a comy (new tab). Now assume that the firm can borrow from the bank at a rate of 10% per annam to maintain its liquidity and meet its required minimum cash balance. In addition, if the firm has fundsin excess of the minimuen, it will use the excess to pay off any previous balance, Create a Complex cash budget that includes borrowing. The assumptions are the same as Part A. Format your worksheet appropnotely. Top Plubming, a small business in Maine needs your help. They need you to create a simple and complex cash budget. Historical sales figures are given above. Assumptions: Indlviduals account for 35% of the firm's sales and they pay in cash. The other 65% of sales are to contractors with credit a ccounts and they have up to 60 days to pay. As a result about 15% of sales to contractors are paid one month after the sale and the other 85% is paid two months after the sale. Each month the firm purchases inventory equal to about 55% of the following month's sales. About 25% of this inventory is paid for in the month of delivery, while the remaining 75% is paid one month later. Total monthly wages are $17,000, including benefits. Salaried employees are paid $20,000, also including benefits. Due to prior borrowing, the firm has monthly interest payment of $5,500 and montly principal payment of $475. Monthly maintenance of the stores is $1,150, except in October to December when snow removal costs will add $500 per month. Sales taxes are 8% 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 3% of quarterly sales in each of those months. Orwers wish to maintain a minimum cash balance of $25,000. the cash balance at the end of May is expected to be $12,000 (before any borrowing of investing). Part A (Simple cash budget) - use the data below to build a simple cash budget. What would be the cash balances for each month (JunDecl. If the firm does not borrow to maintain the minimum desied cash balance? formot your worksheet oppropriately, including heodings with date functions so model is outomoted. Part B (Complex cash budget) - After solving part A (smple cash budget), make a comy (new tab). Now assume that the firm can borrow from the bank at a rate of 10% per annam to maintain its liquidity and meet its required minimum cash balance. In addition, if the firm has fundsin excess of the minimuen, it will use the excess to pay off any previous balance, Create a Complex cash budget that includes borrowing. The assumptions are the same as Part A. Format your worksheet appropnotely