please show provide the formula to get answers
H K G 5 Rusty Spears, CEO of Rusty's Renovations, a custom building and repair company, is preparing 6 documentation for a line of credit request from his commercial banker. Among the required documents is a detailed sales forecast for parts for the next two years. Estimates obtained from the credit and collection department are as follows: collections within the month of sale, 15%; collections during the month following the sale, 65%; collections the second 10 month following the sale, 20%. Payments for labor and raw materials are typically made during the 11 month following the one in which these costs were incurred. Total costs for labor and raw materials are estimated for each month as shown in the table. 9 General and administrative salaries will amount to approximately $15,000 a month; lease payments under long-term lease contracts will be $5,000 a month; depreciation charges will be $7,500 a month; 15 miscellaneous expenses will be $2,000 a month; income tax payments of S25,000 will be due in both 16 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 17 be maintained throughout the cash budget period. IN 20 Input Data 1 Collections during month of sale 15% 22 Collections during month after sale 65% Note: When the percent collected 23 Collections during second month after sale 20% during the second month after sale is 24 Lease payments $5,000 changed, the percent for collections 25 Target cash balance $40,000 during month after sale is 26 General and administrative salaries $15,000 automatically changed so that 100% 22 Depreciation charges $7,500 of sales are collected during the 23 Income tax payments (Sep & Dec) $25,000 three-month period 29 Miscellaneous expenses $2,000 30 New office suite payment (Oct) $80,000 31 Cash on hand July 1 $60,000 32 33 Sales, labor, and RM adjustment factor 0% 35 a. Prepare a monthly cash budget for the last six months of the year, 36 May July August September October NovembeDecember January 38 Original sales estimates $60,000 $100,000 $130,000 $120,000 $100,000 $80,000 $60,000 $40,000 $30,000 39 Original labor and raw mat. estimate $75,000 $90,000 $95,000 $70,000 $60,000 $50,000 $20,000 $20,000 40 41 Forecasted Sales 12 Sales (gross) June Build a Model B E D F K G H $0 SO $0 $0 SO SO B 13 44 Collections 45 During month of sale 46 During 1st month after sale 47 During 2nd month after sale 48 Total collections 49 50 Purchases 51 Labor and raw materials 32 Payments for labor and raw materials 53 54 Payments 55 Payments for labor and raw materials 56 General and administrative salaries 57 Lease payments 58 Miscellaneous expenses 59 Income tax payments 60 Design studio payment 61 Total payments $0 $0 SO $0 $0 $0 8 $0 $0 SO SO SO 63 Not Cash Flows 64 Cash on hand at start of forecast period $60,000 65 Net cash flow (NCF): Total collections - Total payments 66 Cumulative NCF: Prior month cumulative + this month's N $60,000 $60,000 $60,000 $60,000 $60,000 $60,000 68 Cash Surplus (or Loan Requirement) 69 Target cash balance $40,000 $40,000 $40,000 $40,000 $40,000 $40,000 70 Surplus cash or loan needed: Cum NCF - Target cash $20,000 $20,000 $20,000 $20,000 $20,000 $20,000 71 Max. Loan ($20,000) 73 b. How much must Spears borrow each month to maintain the target cash balance? 76 77 78 C. Would the cash budget be accurate if inflows came in all during the month but outflows were bunched early in the month? 80 81 Answer: 83 84 A B D E G H 1 K 69 Target cash balance $40,000 $40,000 $40,000 $40,000 $40,000 $40,000 70 Surplus cash or loan needed: Cum NCF - Target cash $20,000 $20,000 $20,000 $20,000 $20,000 $20,000 71 72 Max. Loan ($20,000) 73 74 b. How much must Spears borrow each month to maintain the target cash balance? 75 76 77 78 c. Would the cash budget be accurate if inflows came in all during the month but outflows were bunched 79 early in the month? SD $1 Answer: 8 84 86 87 SS 89 85 d. If the company operates on a seasonal basis, how would this affect the current ratio and the debt ratio? Answer: 91 92 94 9 e. If its customers began to pay late, this would slow down collections and thus increase the required loan 96 amount. Also, if sales dropped off, this would have an effect on the required loan. Do a sensitivity analysis that 97 shows the effects of these two factors on the max loan requirement Assume the purchases of labor and raw 28 material also vary by the sales adjustment factor. 99 100 101 Answer: 103 104 105 106 107 103 109 110 Build Model H K G 5 Rusty Spears, CEO of Rusty's Renovations, a custom building and repair company, is preparing 6 documentation for a line of credit request from his commercial banker. Among the required documents is a detailed sales forecast for parts for the next two years. Estimates obtained from the credit and collection department are as follows: collections within the month of sale, 15%; collections during the month following the sale, 65%; collections the second 10 month following the sale, 20%. Payments for labor and raw materials are typically made during the 11 month following the one in which these costs were incurred. Total costs for labor and raw materials are estimated for each month as shown in the table. 9 General and administrative salaries will amount to approximately $15,000 a month; lease payments under long-term lease contracts will be $5,000 a month; depreciation charges will be $7,500 a month; 15 miscellaneous expenses will be $2,000 a month; income tax payments of S25,000 will be due in both 16 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 17 be maintained throughout the cash budget period. IN 20 Input Data 1 Collections during month of sale 15% 22 Collections during month after sale 65% Note: When the percent collected 23 Collections during second month after sale 20% during the second month after sale is 24 Lease payments $5,000 changed, the percent for collections 25 Target cash balance $40,000 during month after sale is 26 General and administrative salaries $15,000 automatically changed so that 100% 22 Depreciation charges $7,500 of sales are collected during the 23 Income tax payments (Sep & Dec) $25,000 three-month period 29 Miscellaneous expenses $2,000 30 New office suite payment (Oct) $80,000 31 Cash on hand July 1 $60,000 32 33 Sales, labor, and RM adjustment factor 0% 35 a. Prepare a monthly cash budget for the last six months of the year, 36 May July August September October NovembeDecember January 38 Original sales estimates $60,000 $100,000 $130,000 $120,000 $100,000 $80,000 $60,000 $40,000 $30,000 39 Original labor and raw mat. estimate $75,000 $90,000 $95,000 $70,000 $60,000 $50,000 $20,000 $20,000 40 41 Forecasted Sales 12 Sales (gross) June Build a Model B E D F K G H $0 SO $0 $0 SO SO B 13 44 Collections 45 During month of sale 46 During 1st month after sale 47 During 2nd month after sale 48 Total collections 49 50 Purchases 51 Labor and raw materials 32 Payments for labor and raw materials 53 54 Payments 55 Payments for labor and raw materials 56 General and administrative salaries 57 Lease payments 58 Miscellaneous expenses 59 Income tax payments 60 Design studio payment 61 Total payments $0 $0 SO $0 $0 $0 8 $0 $0 SO SO SO 63 Not Cash Flows 64 Cash on hand at start of forecast period $60,000 65 Net cash flow (NCF): Total collections - Total payments 66 Cumulative NCF: Prior month cumulative + this month's N $60,000 $60,000 $60,000 $60,000 $60,000 $60,000 68 Cash Surplus (or Loan Requirement) 69 Target cash balance $40,000 $40,000 $40,000 $40,000 $40,000 $40,000 70 Surplus cash or loan needed: Cum NCF - Target cash $20,000 $20,000 $20,000 $20,000 $20,000 $20,000 71 Max. Loan ($20,000) 73 b. How much must Spears borrow each month to maintain the target cash balance? 76 77 78 C. Would the cash budget be accurate if inflows came in all during the month but outflows were bunched early in the month? 80 81 Answer: 83 84 A B D E G H 1 K 69 Target cash balance $40,000 $40,000 $40,000 $40,000 $40,000 $40,000 70 Surplus cash or loan needed: Cum NCF - Target cash $20,000 $20,000 $20,000 $20,000 $20,000 $20,000 71 72 Max. Loan ($20,000) 73 74 b. How much must Spears borrow each month to maintain the target cash balance? 75 76 77 78 c. Would the cash budget be accurate if inflows came in all during the month but outflows were bunched 79 early in the month? SD $1 Answer: 8 84 86 87 SS 89 85 d. If the company operates on a seasonal basis, how would this affect the current ratio and the debt ratio? Answer: 91 92 94 9 e. If its customers began to pay late, this would slow down collections and thus increase the required loan 96 amount. Also, if sales dropped off, this would have an effect on the required loan. Do a sensitivity analysis that 97 shows the effects of these two factors on the max loan requirement Assume the purchases of labor and raw 28 material also vary by the sales adjustment factor. 99 100 101 Answer: 103 104 105 106 107 103 109 110 Build Model
