Reference no: EM132266937

Financial statements Analysis Assignment -

The assignment pertains to the construction and the analysis of Renovatech Inc.'s pro forma financial statements.

Here is what you have to do:

1. Build Renovatech Inc.'s pro forma financial statements based on the assumptions you are provided with for the next 5 years (2019 to 2023 inclusively) for the 3 scenarios (pessimistic, realistic and optimistic).

• Once the work is completed, make sure that the balance check is equal to zero for all projected years.
• The assumptions for the realistic scenario can be found under the "Assumptions" tab.

2. Here are the adjustments to do for the optimistic and pessimistic scenarios when compared to the realistic scenario:

Take sales growth for example. Based on the realistic scenario, we have:

Therefore, for the optimistic scenario we would have:

The other assumptions remain the same no matter the scenario.

3. Assume that the following items will be identical to their 2018 respective value for the years 2019 to 2023 inclusively:

• Minority Interest
• Prepayment & Advances
• Other Current Assets
• Goodwill & Intangibles
• CPLTD [Old Debt]
• Taxes Payable
• Deferred Tax
• Other Current Liabilities
• Other Long Term Liabilities
• Common Stock/Shares
• Total Long Term Interest Bearing Debt [Old Debt]
• Moreover, the "Exchange Rate Effect" item found on the cash flow statement will be assumed to be zero for all projected years.

4. Suppose that Renovatech decides to invest in a new project at the end of fiscal year 2018, being on December 1^st 2018 precisely. You must consider this new project in the pro forma financial statements that you will build.

• This project involves the purchase of new assets for an amount of $250M. These assets belong to the category of "Other Long Term Assets" and they are neither depreciable nor amortizable.
• The financing of the project comes in part from a common stock issuance for an amount of $70M. The issuance price of those new shares is $25 per share.
• The remaining $180M come from the issuance of a long term debt. Suppose that this new debt will be reimbursable through equal monthly installments (capital and interests) over a period of 5 years. The annual effective interest rate is 4.4% and it is a fixed rate that doesn't change depending on the scenario.
• In order to consider this new project, fill in the required cells from the tab "New Project".
• In the "New Project" tab, only the cells filled with the colour grey require manual data entry.
• Report your results in the appropriate section of the financial statements.
• The items "CPLTD [New Debt]" as well as "Long Term Interest Bearing Debt (excluding CPLTD) [New Debt]" must be reported from the "New Project" tab to the balance sheet situated in the "Financial Statement" tab using the "horizontal lookup" function (HLOOKUP).

5. Create a drop down list and link it to each of the scenarios. The data from your pro forma financial statements should adjust automatically when a specific scenario is selected.

6. Using a data table, determine the sensitivity of Renovatech's diluted earnings per share (EPS) for fiscal year 2019. The two independent variables with which you must test the sensitivity of EPS are 2019 sales growth as well as 2019 cost of goods sold.

For sales growth: Use -12 %, -10 %, -8 %... until 12 % inclusively.

For COGS / Sales: Start at 64 %, 65 %, 66 % ...., until 81 % inclusively.

Insert this data table in the "Financial Statement" tab to the right of the financial statements.

Your dependent variable must be located in cell K7.

The title of your table must be centred over the width of the table.

No cell must be merged.

7. Using the 5 C model, analyze "Capacity" for Renovatech according to the pessimistic scenario:

Answer this question by first filling the lines of the table under "Items" from the "Capacity" tab. This has to be done in order to calculate the following elements for each year:

Interest coverage ratio (Repayment of interest)

Financial burden ratio (Repayment of interest and capital)

• On line "Coverage Ratios Analysis", using an Excel function, indicate whether the coverage ratios calculated with EBIT are sufficient (Sufficient) or insufficient (Insufficient) for each of the years, from 2017 to 2023 inclusively. In order to be sufficient, both ratios must equal or greater than 1. Your numbers and the word (Sufficient or Insufficient) must adjust automatically according to the scenario selected.

Current ratio

Quick ratio

• On lines "Current Ratio Analysis" et "Quick Ratio Analysis", using an Excel function, indicate whether the current and quick ratios are sufficient Sufficient) or insufficient (Insufficient) for each of the years, from 2017 to 2023 inclusively. In order to be sufficient, these ratios must equal or greater than industry's ratios. Your numbers and the word (Sufficient or Insufficient) must adjust automatically according to the scenario selected.

Interest bearing debt to EBITDA ratio

• On line "Debt / EBITDA Analysis", using an Excel function, indicate whether the ratio is (Acceptable) or high (High) for each of the years, from 2017 to 2023 inclusively. In order to be considered as acceptable, this ratio must be lower or equal to the "Threshold of Acceptability" established at 5. A negative ratio caused by a negative EBIT is considered to be high. Report this threshold on the appropriate line in the Excel file. Your numbers and the word (Acceptable or High) must adjust automatically according to the scenario selected.

Subsequently, analyze "Capacity" for Renovatech based on the results obtained previously. Answer this question in text format in the "Capacity" tab. Answer in point form for this part of the question.

8. Using the 5 C model, analyze short term "Collateral" for Renovatech.

Suppose that you assign a value of 75 % to accounts receivable and 50 % to inventories.

Record these percentages in the indicated location underneath cell "Percentages".

Calculate the guarantees coverage ratio (Coverage ratio). In short, we want to know if short term guarantees calculated with the percentages used are sufficient to cover the revolver at the end of each year.

On line "Coverage Analysis", indicate whether the guarantees are good (Good) or insufficient (Insufficient) for each of the years, from 2017 to 2023 inclusively.

In order to do so, use an Excel function that will result in showing the word "Good" if the guarantees are sufficient or if the revolver is equal to zero, whereas showing the word "Insufficient" if the guarantees are insufficient.

• Your numbers must adjust automatically according to the scenario selected and according to the percentages used.

No qualitative analysis is required for this question.

Rules to observe for the construction of the pro forma financial statements:-

Your data must be entered in Calibri, size 11 font.

Observe the following color code:

• Cell with manually entered data: Blue
• Cell linked to another cell from the same tab: Black
• Cell linked to another cell from another tab: Green

The company name must be entered manually in the file only once, being in cell C3 of the "Financial Statement" tab. If the name shows anywhere else, it must change automatically when the content of cell C3 is modified.

The date must be entered manually in only 2 places in the Excel file, being in cells C4 and C5 of the "Financial Statement" tab. All other dates must adjust automatically as a function of C4.

The items that increase earnings must show as positive in the financial statements and those that decrease earnings must show as negative.

Show only one decimal for numbers in dollars or in percentage, except for EPS and the value of one share where you should keep two decimals.

Attachment:- Assignment Files.rar