Question

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 $80,000

June, 2020 $115,000 $75,000

July, 2020 $145,000 $105,000

August, 2020 $125,000 $85,000

September, 2020 $120,000 $65,000

October, 2020 $95,000 $70,000

November, 2020 $75,000 $30,000

December, 2020 $55,000 $35,000

January, 2021 $45,000 N/A

c. 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 that shows the effects of these two factors on the maximum loan requirement.

Answer 1

Hi Sir,

There are actually few confusions in your question. I haven't understood the exact answer you require out here. In the beginning it is mentioned about a detailed sales forecast that is required but the sales values are already given.

The answer that I have provided is for the maximum loan amount that would be required to be taken by the company. For this as well, there is no proper information as to the collections that the company will receive every month. I have assumed that the sales revenue is received on 1 month credit period in 1 st table and with no credit period in 2nd table. But when we see, in both scenarios, there are no deficiencies in fund for the company as the company will be able to cover the costs given in the question with the sales revenue.

In case you need any further clarifications, kindly put it as a comment to my answer.