Please attached with necessary excel file.

Assume that you are going to start a residential home building company. The average sales price for the homes you build will be $400,000 and the gross margin per home is 25%. The firms Selling, General & Administrative costs are $500,000 when you sell ten homes or less, $1,000,000 when you sell 11 to 20 homes, and $1,500,000 when you sell more than 20 homes. In other words, the SG&A costs increase in a stepwise function. Your company maintains $50,000 cash in the bank, has A/R days in aging equal to 72 days (about 20% of sales) and A/P days in aging equal to 36 days (10%) of cost of goods sold. Inventory on hand equals 20% of sales.

Create a Microsoft Excel spreadsheet that can become a simple business plan model for the company. Use Long Term Debt as the plug figure to estimate your cash needs. Assume that paid in capital is $100,000. Make a reasonable assumption about Property, Plant, and Equipment. Assume that in the first year five houses are sold, in the second year 10 houses are sold, in the third year 20 houses are sold, and in the fourth year 40 houses are sold.

A. What outside cash financing needs does your company have at the Base Level? Use your spreadsheet and the long term debt plug figure to show the cash needs.

B. What if the company doubled its assumed sales each year? What outside financing needs does the company have? Call this the Rosey Scenario.

C. Four Parts to C:

c1. What outside cash financing needs would the company have if the average sales price per house declined by 5% using the same unit sales projections as in b above. Assume in each year that the original Cost of Goods Sold stays the same ($300,000) and only the Sales price declines. In other words, the actual Gross Margin percentage declines.

c2. Using the same logic at in 1) what if the average sales price per house declined by 15% using the same unit sales projections as in b above?

c3. What outside cash needs would the company have if sales prices increased by 5%, 10%?, 20% and the Cost of Goods Sold remained the same at $300,000?

D. How might the financing needs of the company change if you managed some of your balance sheet items differently? Test your spreadsheet under these assumptions:

d1. What if the inventory changed to 10% of sales or 30% of sales?

d2. What if A/R and A/P changed to half of what they are now?

d3. What if A/R and A/P doubled?

d4. What if PPE needs tripled?

Prepare a summary of your solutions to the various scenarios in as clear a visual format as possible so we can discuss them in class.