financial modeling

Objective: The objective of this assignment is to help you apply the assumptions learned in class to a different company. This will help you understand how to apply these assumptions in real-world scenarios.

Instructions: Using the template provided on Canvas, select a company and apply the assumptions learned in class to that company. You should analyze the company’s financial statements and identify key assumptions related to revenue, expenses, and assets/liabilities. You should then use these assumptions to create a cash flow model for the next 5 years.

When calculating NPV use an interest rate of 12%

Requirements:

1. The selected company must be different from IBM.
2. Apply the assumptions of the attached to a company financials.
3. You should use these assumptions to create a financial plan for the next 5 years.
4. Use the template provided on Canvas to complete the assignment.
5. The assignment must be submitted before the deadline.
6. The grading for the assignment is pass/fail. You will receive comments on areas to improve for the final.

Build your own budget
model using the template
You’ve been appointed as the Manager of Information Systems at XYZ Corporation, and your
initial task is to evaluate the profitability of a new project within your department. You are tasked with
calculating the free cash flows and NPV for a proposed project involving the development of a nextgeneration smartphone with advanced features.
The development of this new smartphone requires an initial capital expenditure equal to 20% of
XYZ Corporation’s Net Property, Plant, and Equipment (PPE) at the project’s commencement.
The product is expected to have a life span of five years.
Initial-year revenues for the new product are projected to be 4% of XYZ Corporation’s total
revenue for the latest fiscal year for which data is available. The revenue generated by this product is
anticipated to increase by 18% in the second year, followed by 12% in the third year, and a steady 6%
annual growth for the remaining two years of the project’s expected life.
Your responsibility is to determine the remaining cash flows associated with this project. The
company has stated that the operating expenses and net working capital requirements for this project
will be in line with the rest of the organization. For capital budgeting purposes, assume straight-line
depreciation over a five-year period.
➢ Calculate the Free Cash Flow for each year.
a. Project profitability is expected to be consistent with XYZ Corporation’s existing projects in the
latest fiscal year. Estimate (revenues – costs) for each year using the latest EBITDA/Sales profit
margin.
b. Determine annual depreciation by applying straight-line depreciation to these assets over a
five-year period.
c. Calculate the annual net working capital required by assuming that the level of NWC will be a
constant percentage of the project’s sales. Use XYZ Corporation’s NWC/Sales for the latest fiscal
year to estimate this required percentage.
d. To calculate the free cash flow, subtract the additional capital investment and the change in
net working capital for each year.
Assumptions
Year 1 Sales
3.0%
of 2018 Sales
Revenue Growth – Base Case
Cap Ex
Tax Rate
Cost of Capital (WACC)
Actual Year
Relative
Revenue
yoy growth
EBITDA
Depreciation
EBIT
Taxes
Net Income
Depreciation (+)
Change in NWC (-)
CapEx (-)
Free Cash Flow
Depreciation Schedule
CapEx Yr 0
Depreciation 0
TOTAL
15%
15.0%
21.0%
12.0%
5%
5%
of 2018 PPE
2018
Year 0
2019
Year 1
2,947,504
–
(1,618,950)
(1,618,950)
Year 0
1,618,950
–
10%
940,594
323,790
616,804
129,529
487,275
323,790
(77,640)
733,425
2020
Year 2
3,389,629
15%
1,081,683
323,790
757,893
159,157
598,735
323,790
(11,646)
910,879
2021
Year 3
3,728,592
10%
1,189,851
323,790
866,061
181,873
684,188
323,790
(8,929)
999,050
2022
Year 4
3,915,022
5%
1,249,344
323,790
925,554
194,366
731,187
323,790
(4,911)
1,050,067
2023
Year 5
4,110,773
5%
1,311,811
323,790
988,021
207,484
780,536
323,790
(5,156)
1,099,170
Year 1
Year 2
Year 3
Year 4
Year 5
323,790
323,790
323,790
323,790
323,790
323,790
323,790
323,790
323,790
323,790
2018 Data
Sales
EBITDA
Calculated EBITDA
Net PPE
AR (net receivables)
AP (payables)
Inventory
NWC
Modeling Assumptions
EBITDA/Sales
NWC/Sales
check –
NWC Requirements
NWC (3% of Sales)
Change in NWC
Year 0
Year 1
77,640
77,640
Year 2
89,286
11,646
Year 3
98,215
8,929
Year 4
103,125
4,911
Year 5
108,282
5,156
98,250,123
31,353,123
31,353,123
10,793,000
29,820,000
6,558,000
1,682,000
2,588,000
31.9%
2.6%