Terms of return on equity ratio

compare two similar sized firms in terms of return on equity ratio. Each firm needs $1 million capital to operate. Firm A runs on 100% equity funds (i.e. zero debt) supplied by owners. Firm B which is highly leveraged operates on 60% debt and 40% equity funds. Under good market conditions, the sales for each firm are expected to be $1.2 million and under poor market conditions, the sales are expected to be $500,000.
Cost of goods sold is expected to be 50% of the sales. Operating expenses are set at 60% of the cost of goods sold. Corporate income taxes are paid at 30% rate on taxable income. The current interest rate on borrowed money is 10%.
A. Prepare an Excel sheet to calculate the net profit (or net income) for each company using the data above. The Excel sheet should also calculate the return on equity ratio. Paste the Excel sheet below and also submit a copy of your Excel spreadsheet with your assignment.
B. Using the current interest rate and the Excel work above, which company will perform better in terms of return on equity ratio under:
o Good market conditions?
o Poor market conditions?
C. Using an interest rate of 15% and the Excel work above, which company will perform better in terms of return on equity ratio under:
o Good market conditions?
o Poor market conditions?