Rusty Spears, CEO of Rusty's Renovations, a custom building and repar comppany, is reparig 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 Jauary 2015 Sales $30,000 L&RM none. Estimates obtined from the credit ad collection department are : collections within the month of sales, 15%; collections during the month following sales, 65%; collections the scond 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. Tootal costs for labor and row materials are estimated for eaxh 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 througout the cash budget period.
1. Prepare a monthly cash budget for the last 6 month of 2014
2. Prepare an esimate 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 eaach month during that period.
3. Assume that receipts from sales come in informly 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, juust ahead of sales. ithout making any calculations, discuss how the company's current raio nd dbt 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 increaase the required loan amount. Also, if sales dropped off, this would have an eefect 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 Exel with calculations shown.