Financial Accounting 30000
FINAL PROJECT
Apple’s Financial Model
Objectives
. Reinforce relationships among financial statements
. Review and tie the material learned throughout the quarter.
. Apply the knowledge you acquired.
Assignment: The attached Excel spreadsheet is designed to walk you through the construction of a full financial model of Apple Inc. Your task is to complete the model by projecting financial statements over the next five years. You are to use your accounting knowledge. To make this a useful learning experience, I provided a detailed set of instructions below. You may, from time to time, deviate from these instructions if you choose to do so.
You are to use the spreadsheet to construct Apple’s projected Income Statement, Balance Sheet, and Cash flow statement for the period 2023-2027 and calculate free cash flow. Assume that you are at the end of the 2022 accounting year.
Instructions:
You will often need to interpolate/project certain accounting ratios into the future based on historical trends. There are various ways to do this. For simplicity, I suggest that you use the moving average or moving median values computed over the preceding 4-5 years. For example, see how I projected revenue growth for product categories.
Income Statement modeling
1. Financial modeling starts with forecasting sales revenue, which is the main driver of quantities you see in financial statements. You can use forecasts prepared under the “Revenue Forecasts” tab or use your own forecasts. I included a more detailed forecasting procedure for scenario 1 (optimistic scenario) and just assumed a constant growth rate for the remaining two scenarios.
2. Start by projecting income statement items using revenue forecasts. Utilize the ratios provided at the
bottom of the Income Statement to project other lineitems. You need to interpolate these rates over the projected period. You can see how these ratios are computed based on historical information by clicking on a ratio and examining the formula used in its calculation.
3. A typical way to forecast COGS is to forecast gross margin and back out COGS from sales revenue.
4. R&D and SG&A are usually modeled as a percentage of Revenue based on projected historical ratios.
5. Provision for income taxes is modelled by applying projected effective tax rate to projected pre-tax income.
6. Note: to project interest expense, you will need to project changes in long-term debt, which is a step that you do when you project the balance sheet accounts. You can leave interest expense blank in the beginning.
Balance Sheet modeling
You do not need to forecast the cash flow balance at this point. This will be the last step after you complete the cash flow statement.
Working capital
1. Start by projecting working capital accounts (inventories, receivables, payables, and other current
assets/liabilities). To project these accounts, we typically use one of two drivers: Revenue or COGS. The first step is to compute historical Turnover ratios. To do so, divide the corresponding driver by the end-of- period account balance (think of what the appropriate driver should be). An example of how to do this for Accounts receivable is provided in the spreadsheet, which turns over about 14 times per year in 2022.
2. Next, you will interpolate/project turnover ratios into the future (they are usually relatively stable over time). As usual, you can use the moving average/median approach.
3. Finally, use the projected turnover ratios and projected drivers to infer the projected account balances.
4. Populate the projections to the Balance Sheet.
Non-current assets
1. Next, you need to project Long-Term Assets. To perform these projections, unlike in the case of working
capital, you need to model changes that happen on the T-account, i.e., reconcile beginning (BB) and ending balances (EB) due to new capital expenditures, depreciation and amortization and “other changes” (you can assume that these changes are zero since we do not know what these are).
2. Apple has two types of long-term assets: PPE and Other non-current assets. Because they do not separately report depreciation and amortization for these categories, our strategy will be to model their combined balance and subsequently disaggregate it based on the historical percentage of PPE in non-current assets (computed in the spreadsheet).
3. Unless you have other information, capital expenditures for a large company usually are a stable % of
Revenue (computed in the spreadsheet). You can use this ratio to model future capex using Revenue as a driver. To model depreciation, you can use the historical depreciation rate (computed), interpolate it going forward, and use it to compute depreciation amounts (this approach is a simplification, in practice, you may do something more complex).
4. Populate the projections to the Balance Sheet.
Debt and non-current liabilities
1. Apple has three types of debt on the balance sheet: Term debt, commercial paper, and Other non-current liabilities. We will model these items separately.
2. To model term debt, we combine its current and non-current portions. Come up with projections for new issuance and repayments for each year. Feel free to come up with your own (non-zero) numbers or use moving averages/medians to make these projections. You can assume that fair value adjustments are zero (these are hard to predict).
3. Use historic interest rate to project interest (provided in excel file). For simplicity, you can assume that accrued interest is equal to the cash payment in each of the projected periods.
4. Once you project total term debt into the future, you can separate it into current/non-current portions by interpolating the historical percentage of current term debt.
5. To model commercial paper, use an analogous approach.
6. To model non-current liabilities, assume that the balance will stay constant. It is harder to model these since Apple does not provide enough information.
7. Populate the projections to the Balance Sheet.
Financial assets
1. The strategy is to combine current and non-current balances and model purchases, sales, and maturities of marketable securities (when debt security matures, the investment balance is reduced accordingly). You can project purchases/sales/maturities based on historical trends or assume your own numbers.
2. Fair value adjustments (gains/losses due to mark-to-market accounting) are largely unpredictable, so you can use zeros as your predictions.
3. Populate the projections to the Balance Sheet.
4. After you project the total balances, you can split them into current/non-current amounts based on historical ratios.
Shareholder equity
8. Assume the company will be issuing equity in some of the periods. You can make up your own numbers.
9. You can project share-based compensation based on historical trends or assume that it stays constant.
10. You can assume that “Other” changes in common stock will be zero going forward.
11. When modeling retained earnings, note that dividends are sticky over time and hardly change from period to period. This helps you to make projections.
12. Apple is phasing out its share repurchase program. Assume that their share repurchases decline 30% each year (relative to the prior year). Unless you prefer otherwise, you can assume that the price paid by the shareholders when the shares were initially issued was zero (which is not an unreasonable approximation).
13. Finally, assume that the “Other” change in retained earnings will be zero going forward. (These may arise when accounting rules change. For example, if new accounting rules require a company to switch from historical cost to fair value to measure certain assets, this will generally generate a gain (loss) due to a change in accounting method. Such gain/loss is not reported in the income statement but directly credited (debited) into retained earnings.)
14. ***Finally, Apple’s investment AFS debt investments did not do well in 2022, as can be seen by a
significant increase in their AOCI. Assume that the entire unrealized loss that appears in AOCI will be realized in 2023 due to the sale of these assets. Hint: The loss recorded in the income statement is a non-cash loss that needs to be added back in the cash flow statement (under other adjustments). 15. Populate the projections to the Balance Sheet.
Cash flow statement modeling
1. Use the projections that you created when constructing Income Statement and Balance Sheet to complete the Cash Flow Statement.
2. Assume there are no business acquisitions, no purchases or sales of non-marketable securities during the projected period.
3. Assume there is no restricted cash (bottom of the cash flow statement) in the future five years.
4. Given that we did not model deferred tax assets or liabilities, assume that any account balances related to deferred taxes do not change over time.
5. Once you have completed cash flow statement forecasts and computed the ending balances, populate them to the Balance Sheet.
6. Is your balance sheet in balance? You are all set.
Free cash flow
Compute free cash flow (Operating CF less Capex) under Optimistic, Neutral, and Pessimistic Revenue growth scenarios.
Deliverables:
. Please submit the completed worksheet in Excel format. You are to use formulas where possible instead of manually entering the numbers to facilitate grading.
. You may also submit a sheet with written notes if you believe this will be useful (this is not required). Such notes may detail your assumptions, especially in cases where you deviated from the instructions I provided or thought they were not clear.