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 yea
· 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 incu
ed in the year prior to the homes being completed at $750 per home
· Equipment Capex costs will be incu
ed in the same year customers activate Company X service at $102 per Company X home (equipment costs are only incu
ed 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