Accounting and Financial Analytics

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.
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.
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.
Requirements: typed | .doc file
1 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.
2 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.
3 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
4 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.
1 INSTRUCTIONS You have been hired by International Business Machines Corporation (IBM) as a Manager of Information Systems. Your first assignment is to negotiate and understand the profitability of a new project in your department. Determine the free cash flows and NPV of a proposed new type of tablet computer similar in size to an iPad but with the operating power of a high-end desktop system. Development of the new system will initially require an initial capital expenditure equal to 15% of IBM’s Net Property, Plant, and Equipment (PPE) at the very start of the project. The product is expected to have a life of five years. First-year revenues for the new product are expected to be 3% of IBM’s total revenue for the latest fiscal year for which data is available. The new product’s revenues are expected to grow at 15% for the second year then 10% for the third and 5% annually for the final two years of the expected life of the project. Your job is to determine the rest of the cash flows associated with this project. The company has indicated that the operating costs and net working capital requirements are similar to the rest of the company and that depreciation is straight-line for capital budgeting purposes. ➢ Compute the Free Cash Flow for each year. a. Assume that the project’s profitability will be similar to IBM’s existing projects in the latest fiscal year and estimate (revenues – costs) each year by using the latest EBITDA/Sales profit margin. b. Determine the annual depreciation by assuming IBM depreciates these assets by the straight-line method over a five-year life. c. Calculate the net working capital required each year by assuming that the level of NWC will be a constant percentage of the project’s sales. Use IBM’s NWC/Sales for the latest fiscal year to estimate the required percentage. d. To determine the free cash flow, deduct the additional capital investment and the change in net working capital each year.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *