Apple Inc.s Financial Modeling with PlannersLab

Objective

Following the initial assignment where we used PlannersLab to learn its basic features and components, now we will develop a financial model for Apple Inc. The goal is to create charts that provide us insights of Apples products based on their past performance.

New tasks

For this new assignment, we will have three years of data. That is, we will use Apples 10K form, which is an annual report required by the U.S. Securities and Exchange Commission providing a comprehensive summary of a companys financial performance (adapted from Wikipedia). In this case we have data for 2018, 2017, and 2016, and the objective is to forecast 2019 at least. You can continue the forecast for additional years if you choose to do so.

Data required

From the Table of Contents, you want to select Managements Discussion and Analysis of Financial Condition and Results of Operations, and start browsing by scrolling down until you reach the section Sales Data (around Page 26) As you can see, we can obtain Net Sales by Product (middle of the table) and Unit Sales by Product (end of the table) for iPhone, iPad, Mac, Services, and other products. Columns with the title Change just list percentages of increases or decreases from one year to the next (negative percentages decreases are listed within parentheses). In addition, you should continue scrolling down until you find Gross Margin for 2018, 2017, and 2016 (around page 29). In this small table you can see again Net Sales, but also you get Cost of Sales per year.

Notes

When reading the 10K form pay close attention to the units used for dollars and product units since they use Millions and Thousands. Make adjustments for these difference in scale so your calculations will not be giving you surprising values (useless values, actually).

For simplicity, you can approximate some of the values, or you can make assumptions. Please include explicit notes highlighting these assumptions/approximations in your submission.

Data Structure

In the initial PlannersLab tutorial assignments we had 4 nodes under Model Design (left side of the screen) listing Sales Volume which contained products units, calculated based on their corresponding growth-rates. Then, we had the Materials Expenses node containing products costs, calculated based on their corresponding cost-increase-factors. Later, we had the Sales Price node containing products price, calculated based on their corresponding price-increase-factors. Finally, we had the Profit Before G&A node where we calculated the profit for each product line. That is,

Profit = Revenue Total Cost

Revenue = Units Sold * Product Price

Total Cost = Units Sold * Product Cost

New Calculations

We will create the same node structure for this assignment following the example from PlannersLab tutorial. Therefore, we need to have the values for the same type of variables: product unit growth-rates, calculate product units (only 2019 calculation is required), product costs increase-factors, calculate product costs, product price increase-factors, calculate product prices, and profits calculations for all products.

Data Preparation

So, consider Apples main products: iPhone, iPad, and Mac. Create an Excel worksheet listing 2016, 2017, 2018 values so you can plan the data you will use for PlannersLab. As mentioned before, you already have Unit Sales, and you need to calculate product prices and product costs. Product unit prices can be estimated by dividing Net Sales by Unit Sales.

Now, calculating product unit costs would be a bit trickier. One way to go around the problems is to calculate it as follow: ((net sales/total net sales) * total cost of sales) / unit sales. Note that the above may oversimplify your calculations. The goal is to approximate the unit cost by calculating the contribution of the net sales of a given product group in the total net sales.

Entering New Data

Once you have all values for the initial three nodes (Sales Volume, Materials Expenses, and Sales Price) you can enter the values in Planners Lab following the same process used in the initial PlannersLab tutorial. Create the four nodes, enter all the variables (in this case for Apples four main products), the same way described in the tutorial including profit calculations within Profit Before G&A node.

Notice that growth-rates, cost-increase-rates, and price-increase-rates for 2019 should be estimated. You can decide to average previous values, or decide based on the economy that these rates should be higher or lower. Just provide all your assumptions highlighting them in your submission.

New Insights

Once you complete Modeling, go to Playground in PlannersLab, and look to create charts that can provide answers to the questions listed below. Choose among line and bar charts depending on what you consider better illustrate an answer.

Action

Add the screenshots of your all works that you did in Excle amd Planners Lab and screenshots all corresponding charts, and mainly provide an explanation based on the charts values about what we can learn from them toward answering the question.

A- Investigate your current profits from different product lines (use a line chart and use all goal variables in your drawing, except the total profit). Interpret the results. Save the layout.

B- Assess sales volume data. Use a chart to document your findings. What are your best selling products in terms of units sold? Do you find any similarities or differences between profits and units sold?

C- Based on the information obtained from questions 1 and 2, Are there any products for which the profits are expected to decline?

D- What would it take to double the profits on your least profitable product group? Before you can answer the question, you should examine which variables the profit depends on. To do that, while in Playground mode, create a new layout and drag a Variable-Tree onto it. Next, add the desired goal variable to the tree and then choose year 2019 (your first forecast year). Expand all branches. You should see that profits depend on three factors: sales growth rate, cost increase factor, and price increase factor.

Sales Data The following table shows net sales by reportable segment and net sales and unit sales by products for 2018 2017 and 2016 dollars in milions and units in thousands 2018 Change 2017 Change 2016 Net Sales by Reportable Segment Americas 112,093 16% $ 96,600 125 $ 86,613 Europe 54930 105 49952 Greater China 51.942 44.764 Japan 21.733 Rest of Asia Pacific 15.190 13654 Total net sales 165 $ 229 234 65 $ 215 639 265595 Net Sales by Product Phone 35 $ 7% 135 iPad Mac Services Other Products Total net sales 166.699 18.805 25.484 37.190 17.417 265.595 1855 141 319 (21% 19.222 (1)% 25,850 24% 29 980 35 % 12. 3 16 $ 229 234 136,700 20,628 22,831 24.348 11.132 215.639 16 5 65 $ Unit Sales by Product iPhone iPad Mac 217,722 43.535 18.209 -% -% (5)% 216,756 43,753 19.251 25 (4)% 45 211.884 45,590 18,484 (1) Includes deferrals and amortization of related software upgrade rights and non-software Services (2) Includes revenue from Digital Content and Services, AppleCare Apple Pay Bensing and other services Services et les in 2018 included a favorable one-time em of $236 million in connection with the final resolution of various lawsuits Services sales in 2017 included a favorable one-time adjustment of 5640 million due to a change in estimate based on the wa y additional supporting information (3) Includes sales of AirPods Apple TV. Apple Watch Beats products, HomePod, iPod touch and other Apple-branded and the party accessories Apple Inc. 2018 Form 10-K123 Sales Data The following table shows net sales by reportable segment and net sales and unit sales by products for 2018 2017 and 2016 dollars in milions and units in thousands 2018 Change 2017 Change 2016 Net Sales by Reportable Segment Americas 112,093 16% $ 96,600 125 $ 86,613 Europe 54930 105 49952 Greater China 51.942 44.764 Japan 21.733 Rest of Asia Pacific 15.190 13654 Total net sales 165 $ 229 234 65 $ 215 639 265595 Net Sales by Product Phone 35 $ 7% 135 iPad Mac Services Other Products Total net sales 166.699 18.805 25.484 37.190 17.417 265.595 1855 141 319 (21% 19.222 (1)% 25,850 24% 29 980 35 % 12. 3 16 $ 229 234 136,700 20,628 22,831 24.348 11.132 215.639 16 5 65 $ Unit Sales by Product iPhone iPad Mac 217,722 43.535 18.209 -% -% (5)% 216,756 43,753 19.251 25 (4)% 45 211.884 45,590 18,484 (1) Includes deferrals and amortization of related software upgrade rights and non-software Services (2) Includes revenue from Digital Content and Services, AppleCare Apple Pay Bensing and other services Services et les in 2018 included a favorable one-time em of $236 million in connection with the final resolution of various lawsuits Services sales in 2017 included a favorable one-time adjustment of 5640 million due to a change in estimate based on the wa y additional supporting information (3) Includes sales of AirPods Apple TV. Apple Watch Beats products, HomePod, iPod touch and other Apple-branded and the party accessories Apple Inc. 2018 Form 10-K123