Rusty Spears, CEO of Rusty's Renovations, a custom building and repair company, is repair documentation for a line of credit request from his commercial bankr. Among the required documents is a detailed sales forecast for parts of 2014 and 2015 May 2014 Sales $60,000 Labor and Raw Materials $75,000 June 2014 Sales $100,000 L&RM $90,000 July 2014 Sales $130,000 L&RM $95,000 August Sales $120,000 L&RM $70,000 September $100,000 L&RM $60,000 October Sales $80,000 L&RM $50,000 November Sales $60,000 L&RM $20,000 December Sales $40,000 L&RM $20,000 January 2015 Sales $30,000 L&RM none. Estimates obtained from the credit ad collection department are: collections within the month of sales, 15%; collections during the month following sales, 65%; collections the second month following the sale, 20%. Payments for labor and row materials are typically made during the month following the one in which these costs were incurred. Total costs for labor and row materials are estimated for each month as shown before. General and administrative salaries will amount to approximately $15,000 a month; lase payments under long term lease contracts will be $5,000 a month; depreciation charges will be $7,500 a month; miscellanious expenses will be $2,000 a month; income tax payment of $25,000 will e due in both September and December; and a progress payment of $80,000 on a new office suite must be paid in October. Cash on hand on July 1 will amount to $60,000 and a minimum cash balance of $40,000 will be maintained throughout the cash budget period.

1. Prepare a monthly cash budget for the last 6 month of 2014

2. Prepare an estimate of the required financing (or Excess funds)- that is, the amount of money Rusty's Renovations will need to borrow (or will have to invest)- for each month during that period.

3. Assume that receipts from sales come in infirmly during the month (i.e., cash receipts come in at the rate of 1/30 each day) but that all outflows are paid on the 5th of the month. Will this have an effect on the cash budget- inn other words, would the cash budget you have prepared be valid under these assumptions? If not, what can be done to mke a valid estimate of peak financing requirements?

4. Rusty Renovations produces on a seasonal basis, just ahead of sales. Without making any calculations, discuss how the company's current raionddbt ratio would vary during the year assuming all financial requirements were met by short-term bank loans. Could changes in these ratios affect the firm's ability to obtain bank credit? Why or why not?

5. If its customers began to pay late, this would slow down collections and thus increase the required loan amount. Also, if sales dropped off, this would have an effect on the required loan amount. Perform a sensitivity analysis thar shows the effects of these two factors on the maximum loan requirement.

Include word and Excel with calculations shown