Financial Model Exercise

Note: All data presented on this page is fictional and does not represent actual company data.

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 Comcast has been approached by a property development company, Arctic Land Co$.,$ in Seattle, WA to build a

"fiber neighborhood" in a new residential housing community. Arctic Land Co$.$ estimates that there will be $1,500$ homes

built in the new community and wants to partner with Comcast to sponsor the new community.

At a formal presentation, Arctic Land Co$.$ offers Comcast a "premium packaging" option with the proposed deal, featuring

three products per home that activates Comcast services. The products will be Video $("$TV$"),$ Data $("$Internet$"),$ and Xfinity

Home Security.

Following the presentation from Arctic Land Co$.,$ Comcast Division President, John Doe, approaches your team about

valuing the investment and providing a recommendation for whether or not Comcast should agree to build the "fiber

neighborhood" and sponsor the new community.

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)$

Comcast will construct network connections to all homes, regardless if the homes activate Comcast services

Assume homes are completed by Jan $1^{st}$ of each year and Comcast receives revenue from Comcast$-$serviced

homes starting on Jan $1^{st}$ of each year

$60\%$ of cumulative homes built activate Comcast's "premium" $3-$Product package $($Video$,$ Data, XF Home$)$

ARPU's $($Average Revenue per Unit per Month$)$ in Year $1$

Video $=$$$44\mathrm{.}00$

Data $=$$$32\mathrm{.}00$

Xfinity Home $=$$$33\mathrm{.}00$

ARPU Growth in each year from Year $2$ to Year $5$

Video $=2\mathrm{.}0\%$

Data $=1\mathrm{.}0\%$

Xfinity Home $=0\mathrm{.}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 Comcast service at $$102$ per

Comcast home $($equipment costs are only incurred in the year of new home activation, not afterwards$)$

Discount Rate $=8\mathrm{.}0\%$