Goal Build a financial model that values the investment opportunity described below, solving for (1) cumulative 5-year revenue, (2) 5-Year NPV, and (3) 5-Year IRR. Investment Opportunity – Background...

Two exercises.


Goal Build a financial model that values the investment opportunity described below, solving for (1) cumulative 5-year revenue, (2) 5-Year NPV, and (3) 5-Year IRR. Investment Opportunity – Background Imagine company X has been approached by a property development company, Company Y., in Seattle, WA to build a “fiber neighborhood” in a new residential housing community. Company Y. estimates that there will be 1,500 homes built in the new community and wants to partner with Company X to sponsor the new community. At a formal presentation, Company Y. offers Company X a “premium packaging” option with the proposed deal, featuring three products per home that activates Company X services. The products will be Video (“TV”), Data (“Internet”), and Home Security. Below are some estimates to help build the financial model to value the investment: · 1,500 homes to be built/completed in community (500 in Year 1, 500 in Year 2, 500 in Year 3) · Company X will construct network connections to all homes, regardless if the homes activate their services · Assume homes are completed by Jan 1st of each year and Company X receives revenue from serviced homes starting on Jan 1st of each year · 60% of cumulative homes built activate Company X’s “premium” 3-Product package (Video, Data, Security Home) · ARPUs (Average Revenue per Unit per Month) in Year 1 · Video = $44.00 · Data = $32.00 · Security Home = $33.00 · ARPU Growth in each year from Year 2 to Year 5 · Video = 2.0% · Data = 1.0% · Security Home = 0.0% · Operating Expense Assumptions for Years 1-5 · Tech/Eng = 10% of Total Annual Revenue · Customer Service = 2% of Total Annual Revenue · Sales & Marketing = 5% of Total Annual Revenue, plus $50,000 per year in Years 1-5 to sponsor community · Programming = 30% of Total Annual Revenue · Construction Costs will be incurred in the year prior to the homes being completed at $750 per home · Equipment Capex costs will be incurred in the same year customers activate Company X service at $102 per Company X home (equipment costs are only incurred in the year of new home activation, not afterwards) Task · Develop a one page executive summary of AT&T’s latest earnings release · Include any relevant graphs, charts, data, and commentary that you think will give our senior leadership key insights into AT&T and how they are performing Audience · Division President and their Senior Leadership Team Goal · Providing the executive team a quick, thorough, and easily consumed document that delivers key takeaways from the earnings release/investor call