Question
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
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.
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started
Finance For Executives Managing For Value Creation
Authors: Gabriel Hawawini, Claude Viallet
3rd Edition
0324274319, 9780324274318
