Start with the partial model in the file attached. Marvel Pence, CEO of Marvel's Renovations, a...

 

Transcribed Image Text:

Start with the partial model in the file attached. Marvel Pence, CEO of Marvel's Renovations, a custom building and repair company, is preparing documentation for a line of credit request from his commercial banker. Among the required documents is a detailed sales forecast for parts of 2020 and 2021: Sales Labor and Raw Materials May. 2020 $75.000 S80,000 June, 2020 $115.000 S75.000 July, 2020 $145.000 S105,000 August, 2020 $125,000 $85.000 September. 2020 $120.000 $65,000 October, 2020 $95,000 S70,000 November, 2020 $75,000 S30,000 December, 2020 $55,000 $35.000 Jашагy, 2021 $45.000 N/A Estimates obtained from the credit and collection department are as follows: collections within the month of sale, 20%; collections during the month following the sale, 60%, collections the second month following the sale. 25%. Payments for labor and raw materials are typically made during the 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. General and administrative salaries will amount to approximately $25.000 a month lease payments under long-tem lease contracts will be $7.000 a month; depreciation charges will be $8,000 a month miscellaneous expenses will be $5,000 a mouthr income tax payments of $30,000 will be due in both August and December; and a progress payment of $95,000 on a new office suite must be paid in October. Cash on hand on July 1 will amount to $70.000, and a minimun cash balance of S30,000 will be maintained throughout the cash budget period. Input Data Collections during month of sale Collections during month after sale Collections during second month after sale Lease payments 20% 60% 25% - Note: When the percent collected during the second month after sale is changed, the percent for collections during month after sale is automatically changed so that 100% of sales are collected during the three- month period. Target cash balance General and administrative salaries S7,000 $30,000 $25,000 Depreciation charges Income tax payments (Sep & Dec) Miscellaneous expenses New office suite payment (Oct) Cash on hand July 1 $8,000 $30,000 $5,000 $95,000 $70,000 Sales, labor, and RM adjustment factor 0% Cash Surplus or Loan Requirement Minimurr Cash Balance $30,000 s30,000 $30,000 $30,000 $30,000 $30,000 Surplus Cash Requirement (Cum NCF $41,000 $4,000 $10,000 $71,000 $82,000 $104,000 Minimurn cash balance) If its customers began to pay late, this would slow down collections and thus increase the required oan amount. Also, if sales dropped off, this would have an effect on the required loan amount. Perform a sensitivity analysis that shows the effects of these two factors on the maximunn loan requirement. Start with the partial model in the file attached. Marvel Pence, CEO of Marvel's Renovations, a custom building and repair company, is preparing documentation for a line of credit request from his commercial banker. Among the required documents is a detailed sales forecast for parts of 2020 and 2021: Sales Labor and Raw Materials May. 2020 $75.000 S80,000 June, 2020 $115.000 S75.000 July, 2020 $145.000 S105,000 August, 2020 $125,000 $85.000 September. 2020 $120.000 $65,000 October, 2020 $95,000 S70,000 November, 2020 $75,000 S30,000 December, 2020 $55,000 $35.000 Jашагy, 2021 $45.000 N/A Estimates obtained from the credit and collection department are as follows: collections within the month of sale, 20%; collections during the month following the sale, 60%, collections the second month following the sale. 25%. Payments for labor and raw materials are typically made during the 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. General and administrative salaries will amount to approximately $25.000 a month lease payments under long-tem lease contracts will be $7.000 a month; depreciation charges will be $8,000 a month miscellaneous expenses will be $5,000 a mouthr income tax payments of $30,000 will be due in both August and December; and a progress payment of $95,000 on a new office suite must be paid in October. Cash on hand on July 1 will amount to $70.000, and a minimun cash balance of S30,000 will be maintained throughout the cash budget period. Input Data Collections during month of sale Collections during month after sale Collections during second month after sale Lease payments 20% 60% 25% - Note: When the percent collected during the second month after sale is changed, the percent for collections during month after sale is automatically changed so that 100% of sales are collected during the three- month period. Target cash balance General and administrative salaries S7,000 $30,000 $25,000 Depreciation charges Income tax payments (Sep & Dec) Miscellaneous expenses New office suite payment (Oct) Cash on hand July 1 $8,000 $30,000 $5,000 $95,000 $70,000 Sales, labor, and RM adjustment factor 0% Cash Surplus or Loan Requirement Minimurr Cash Balance $30,000 s30,000 $30,000 $30,000 $30,000 $30,000 Surplus Cash Requirement (Cum NCF $41,000 $4,000 $10,000 $71,000 $82,000 $104,000 Minimurn cash balance) If its customers began to pay late, this would slow down collections and thus increase the required oan amount. Also, if sales dropped off, this would have an effect on the required loan amount. Perform a sensitivity analysis that shows the effects of these two factors on the maximunn loan requirement.

Answer rating: 100% (QA)

FORECASTED SALES May June July August September October November December January Sales 75000 115000 ... View the full answer