Accounting and Financial Analytics

1. Organization of Excel Files: A significant portion of your grade will depend on how you display and organize your Excel files. A well-structured file should include Excel formulas, clear references to assumptions, and effective color-coding. This formatting is essential to distinguish between header rows, assumptions, and calculations. Please include the legal scenario, and all other scenarios as their own separate tab.
2. Negative Net Working Capital: Negative Net Working Capital (NWC) is not necessarily a problem. If this is happening to you first, check if you’ve inadvertently included cash in your current assets – I did in the template file so there’s a good chance you might be as well. If that doesn’t resolve the issue, assume 10% of sales as a conservative approach.
3. Impact of the $300 Million Investment: The $300 million investment is considered a sunk cost. Sunk costs have no bearing on future decisions; they should not influence your decision-making process. Whether you proceed with the project or not, this investment is a historical expense and won’t affect your future actions. This concept was not covered in class, so consider this as additional guidance.

• The WACC in the project is the discount rate

In question 6 the probability of the legal case is 20%, there is a typo whereby it says 5% early in the problem.

Accounting and Finance Fundamentals
Professor: Will Gogolak
Assignment: Final Group Project
INSTRUCTIONS
One member of the group is responsible for submitting the assignment via Canvas. Assign this responsibility to only one
person. The submission deadline is 11:59 p.m. on the due date in Canvas. Extensions will not be granted to any particular
group or individual. I would recommend beginning as soon as possible to avoid any last-minute fire drills.
What I have observed to be effective in the past is when students divide the work up as follows; this is not a prescriptive list;
however, it is an approach that has worked well for most groups:
–
Each student must read the assignment independently and make a list of the problem’s assumptions; everyone is
required to read it more than once
Students then read the questions in this document listed below
Use IBM as a starting point for the Free Cash Flow analysis – adjustments and additions will be necessary
Groups that do best meet every day for a few minutes to touch base – meetings can be as short as 30 minutes. The
purpose of the meeting is to conduct a status check, address roadblocks, and resolve issues. This approach is
frequently more effective than attempting to complete everything in the final hour.
DELIVERABLES
1. You need to submit a completed excel file. Submit one excel file using the templates discussed in class; use the
financial statement data to create your own Free Cash Flow model and other templates to get to WACC. This file
should have multiple tabs that are clearly labeled.
2.
You must also return your responses to me in this work document. You must submit typed answers to the question
directly beneath the last question of this file. You are required to return this word document to me with your
responses.
3.
All members must be present for the final meeting. You will be required to meet with me in order to discuss the
model. This meeting is an opportunity for me to ask questions about the model and for you to discuss whether you
believe the company should pursue the project.
ACIDEMIC HONESTY
Please do not distribute this document to anyone other than members of your group. Please only work with your group.
Anyone caught sharing documents with someone outside of the group will be held accountable for academic honesty
violations. In other words, refrain from posting the final on websites that others can access later. It is unfair to other
students.
1
Base Case Assumptions
You have been hired to work as a consultant to cover robotic implementation projects. You were hired by the client to
negotiate and analyze the profitability of a new robotics project within one of their departments.
Through robotic delivery directly to consumers, new technology has reimagined local delivery options. The company you consult for
wishes to integrate robotics technology into the delivery of existing products and charge customers a subscription fee for the more
efficient delivery. It is up to your team to determine whether this is a sound financial decision. Calculate the free cash flows and net
present value of a proposed project using the assumptions listed below.
The company wants to consider using Nuro to deliver their products directly to the customer’s doorstep. Want hot pizza right away?
Want to have your groceries sent to you? No fear, Nuro is here! Click here to find out more about how your company can use Nuro.
Installing robotic services to support the delivery will initially require capital expenditure equal to 15% of the firm’s PP&E in
the most recent fiscal year for which data are available. This money is spent during year zero of the project. The PP&E
installation is a capitalized expense with a five-year expected life. The project has a timeline of 0 – 5 years.
First year revenue is expected to be 15% of the firm’s total revenue for the most recent fiscal year for which data is available.
The new revenues are expected to grow at a rate of 15% in the second year, 10% in the third, and 5% in the final two years
of the project’s expected life.
Assume that the profitability of the project will be comparable to the profitability of the firm’s existing projects in the most
recent fiscal year. Estimate profitability by using the most recent EBITDA/Sales profit margin. If your company does not list
depreciation or one of the EBITDA line items then simply use “Operating Profit,” “Operating Income,” or “Income from
Operations” as EBITDA .
To determine the annual change in net working capital requirement, use a constant percentage of the project’s sales.
Estimate the required percentage using NWC/Sales for the most recent fiscal year.
Additionally, it should be noted that the company has previously attempted and failed to enter the robotics delivery market.
Thus far, the firm has invested more than $300 million in examining this revenue stream.
Assume the project will run from 0 to 5 years –there should be no other years in the timeline. It is your responsibility to
ascertain the free cash flows associated with this project and opine on a decision for the client.
2
Additional information with high-level guidance
Consider the following assumptions and modeling guidelines – these are the bare minimum requirements; additional
requirements may be necessary.
•
Calculate the annual depreciation based on the assumption that these assets are depreciated using the straight-line
method over a five-year life.
•
There should only be costs in year 0; there should be NO cash flows in year 6. The project should only go to year 5.
•
To calculate free cash flow, properly time capital investments and changes in net working capital.
•
Ensure that each component of robust cash flows have their own line item in the free cash flow build.
•
Assume the cost of capital to be 12%
•
Delivery technology video: https://youtu.be/ytH4t8fsIzw
3
Final Questions
Calculate the NPV and IRR of the project using Excel under the various assumptions provided below.
1.
(Base case) What is the payback period for the project, and what is the discounted payback period for the
project using base case input assumptions?
2.
(Base case) What is the net present value and internal rate of return in the base case?
3.
(Base case) Is this a project that would be attractive to shareholders? How would you characterize the base case
– good, bad indifferent? Does the project appear to be one that the investment committee would consider
accepting? Your answer must include an analysis of cash flow timing, practicality, and so forth. Consider the
company’s strategic vision in light of the findings in its financial presentations. You should have a minimum of 300
words in your response. Make a case for and against the project, weighing the advantages and disadvantages.
This response should be in regards to the base case.
4.
(Base case: Minimum Shareholder Value Created) Assume that the firm accepts projects only if the NPV exceeds
5% of the enterprise value (Total Debt + Total Equity). What level of revenue growth is required to reach this
minimum amount? Assume a flat-line revenue amount for this problem – the revenue growth rate should not
vary year-to-year.
5.
(Simulated events: Revenue growth) What will happen to the project’s NPV if growth prospects rise and the
economy experiences a moderate expansion? Assume that the annual growth rate is 12 percent. Describe in 100
words what you would recommend the company do in this situation. Additionally, create a slow growth scenario
where there is no revenue growth.
6.
(Simulated events: Estimating potential tail risks with expected NPV given two states of nature) Assume the
project faces potential legal challenges in year two, resulting in the suspension of services. This event has a 5%
probability. The company is guessing that the legal issues may only effect year two; revenues will be zero and
legal fees will total 1% of total company operating costs during year 2. Create a legal scenario on a separate tab
by copying + pasting the base case tab and changing assumptions. The only difference is that in year two, there
will be no revenues and only legal expenses. What is the project’s expected net present value if this event is
weighed against the base case with a probability at 20%/80%? Indicate the expected NPV. Does the legal scenario
change the way you initially viewed the NPV analysis? You should have a minimum of 100 words in your response.
7.
(Conceptual understanding: Sources of capital inside vs. outside capital) Is there sufficient cash on the balance
sheet to invest in the project without requiring external financing? Is this a factor in your decision whether or not
to accept the project? Why? You should have a minimum of 100 words in your response.
4
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%