Reference no: EM133069065

Problem Statement

The Managing Director of an investment bank is responsible for the successful pitch to its valued clients. In this project we shall explore the entire process of how the Managing Director produce a Pro Forma model and tune it according to his pitch. You are the Junior Analyst supporting the Managing Director.

Part 1: Building the Excel Model

First the Managing Director needs to find a good stock to pitch. He shortlists a few good stocks and builds the basic Pro Forma for each of these stocks for further analysis. Your Managing Director chose the company Sea Ltd. This is an interesting company started in Singapore but listed in NYSE.

1) Basic Model

Download or copy the latest financial statement of Sea Ltd (SE).  The file SeaAR2020.pdf can be found in[SJ1]  the LumiNUS Worksheet folder. Type in the Consolidated Balance Sheets and Consolidated Statements of Operations into the excel.  Do not rename the items in the financial statement. This is because the firm will continue to announce financial results based on their format and not the standardized format.  The standardized format is useful only if you cannot figure out items are grouped in the Sea report.

Modify the original financial statements in this way:

1) Re-categorise the revenue into 4 categories: Digital Entertainment, E-commerce, Airpay, and Others.  Equivalently re-categorize the cost into these 4 categories as well. We separate Airpay out from Shopee because our pitch will involve Shopee but not Airpay[SJ2] .

2) Extract out Depreciation of PPE and Depreciation of Intangible assets from General and Admin expense. In the Balance Sheet, track the Gross Fixed Asset, Cumulative Depreciation and Net Fixed Asset for PPE and Intangible Assets. We separate out the depreciation to keep track of it, needed in the FCFF calculations[SJ3] .

3) Collapse the current liabilities to the categories of Accounts Payable and Accruals, Deferred revenue, and Other current liabilities. We simplify the items that we have no insight views on.

4) Collapse the non-current liabilities to the categories of Convertible notes, Operating Lease Liabilities, Deferred revenue, and Others. We simplify the items that we have no insight views on.

5) Collapse the equity to the categories of Equity, and Accumulated Retained Earnings. We simplify the items that we have no insight views on.

Model 10 years ahead. We model 10 years not because we think we have perfect forecasting. It is because the FCFF model requires the figures to reach equilibrium state before assuming terminal value.  Most importantly we require the FCFF to reach equilibrium state. For example, if the FCFF turns positive only in year 5, and we use terminal value from year 6 onwards, the model will not be able to value the stock price accurately. The FCFF need to run positive for a few years before we assume terminal value for the rest of the years. Similarly, we need cash balance, debt ratio, etc to reach equilibrium. 10 years is a good number to work on since a business cycle is roughly 10 years. For Sea Ltd however, 10 years may be barely enough to reach equilibrium.

The 2021 Q2 results are already announced. Incorporate the Q2 results of FY2021 (income statement) in the following way:

That is, the 2021Q2 column tabulates the Q1 & Q2 results.[SJ4]  The 2021Q3Q4 column tabulates your own estimates. Then column 2021 is the summation of column 2021Q1Q2 and column 2021Q3Q4. This applies only for the Income Statement figures. For Balance Sheet figures, you should tabulate only the 2021 column[SJ5] .

State all your assumed financial ratios and parameters clearly. Please refer to Q2 results. You will find that many of the financial ratios have changed. Please use financial ratio assumptions in tune with the Q2 results. Simply assume the sale growth rates, there is no need to break down into number of users and QAU for Garena, and GMV for Shopee.

Compute the FCF, estimate the price per share.  Adjust your parameters so that you obtain the current market price. Prepare working to explain how you derive the rd, tax rate, beta, re, WACC, and g. Justify each of the financial ratios to make intuitive sense.

You will find difficulty in keeping the Cash positive, the net income positive and the FCF positive. Model such that the gross margin would increase, marketing cost would reduce, and the fixed asset turnover would increase. Take note of the tax. The income tax rate comes in only after the net income turns positive. The tax expense for negative net income years are due to other non-income based taxes.

You are not allowed to increase common equity, except for what was done in the third quarter.