profitability managment

Background and objectives The objective of this exercise is to evaluate your practical skills for the types of tasks that the Profitability

Management team is facing by putting you in a hypothetical situation. We want to evaluate (1) your ability to

interpret unstructured information, (2) build accurate models and analyze numerical data and (3) communicate your

findings and recommendations both in a written document and through oral presentation.

Task at Hand – Building a 3-Statement Model All information below is hypothetical.

Background  You are approached by a group of friends who have an idea for a business based on a mobile application.

They invite you to invest along and ask you to help build the financial projections before making an

investment.

 You have to build a model which calculates the evolution of the 3 financial statements – P&L, cash flow

statement and balance sheet, for a 5-year period (yearly granularity).

 The business model is simple: AI-powered news aggregation service for the consumer segment. Each

subscriber pays a certain monthly fee and is able to access unlimited number of news articles specifically

collected and prioritized for him/her by your AI algorithm. Your company in turn sources the news from

other premium sources (in other words you buy wholesale, repackage and sell retail).

Key data points and assumptions  You plan to build and develop the business for 5 years until it reaches “stable operation”. At the end of year 5

you will either sell the business or liquidate its assets if you can’t find a buyer.

 You and your friends will invest together the sum of $550,000 initially.

 First the business needs servers. One server costs $10,000 to purchase and install in the data-center and can

serve maximum of 2,000 subscribers. The server must be purchased and installed before the demand hits

(i.e. previous year).

 The number of subscribers depends on the price – the higher the price the less subscribers:

a. The formula to calculate the size of the addressable market is 1,000,000 / (monthly subscription fee ^

2). For example if you price the service at 10 USD/month, then you can expect to have 1,000,000 /

(10 ^ 2) = 10,000. If the price is higher some subscribers will not be interested in the service and vice

versa. Keep in mind, you can’t drop the price below the variable cost (described further down).

b. Furthermore you know that when the business launches in year 1 you can hope to get only 25% of

the addressable market, because not everyone will be aware of the service. Then in year 2 you

expect to have 50% of the addressable market; in year 3 – 75% of the addressable market and in year

4 and 5 – the full 100% of the addressable market.

c. For simplicity ignore gross additions and churn – the above formulas / rules should give you the total

base at any given time. Also ignore the effect of competition, market share and seasonality.

 The business must rent space and connectivity in a data center. For simplicity assume that one active

subscriber (all subscribers are active) generates 0.5 GB / month. This is important because the data center

charges for the volume of monthly generated traffic. The bad news is that the data center works in big blocks

of 100 TB. In other words you cannot buy 10 GB for example, but increments of 100 TB. For every 100 TB you

must pay 75,000 USD annually. For simplicity assume that 1,000 GB = 1 TB.

 Each server has a useful life of 5 years with zero residual value and you must reflect the depreciation using

linear model in your financial statements.

 You plan to maintain a small office and staff. Your costs will be as follows:

a. For office rent and utilities you will be paying 3,000 USD / month

b. You will pay monthly salary of 7,000 USD to each of three managers (CEO, Technology, Sales)

c. Two admin staff to handle the accounting, contracts and correspondence, travel, etc. – two people at

2,500 USD/month

d. One marketing expert – 4,000 USD / month.

 For the news feeds you have to pay the news providers, because they provide professionally prepared

content by journalists. Luckily there is a wholesale aggregation service which makes your life easier

operationally. Unfortunately the news aggregator has fixed fee of 15,000 USD / month to “keep the pipe

open”. Additionally the provider also charges a monthly fee of 2 USD / subscriber / month.

 The price to the end customer and all variable and fixed costs per unit will remain the same for the 5 year

period.

 For simplicity assume that you will not have any other assets apart from the servers.

 For simplicity, consider there is no tax.

 For simplicity assume you will not be borrowing, lending, raising capital or paying dividends.

Your Task  Your task is to build the three financial statements (P&L, cash flow statement, balance sheet) in evolution for

5 years, yearly granularity – in Excel.

 You should also answer the following questions:

 Optimum price.

At what monthly subscription price will you reach highest value of the business at the end of Y5? For

simplicity assume that “value” is the net book value of the assets at the end of Y5. For simplicity use whole

dollar values for the monthly price. What is the net book value of the assets at the end of Y5 at this optimum

price? What is the net book value of the assets if the price is below or above the optimum (+/- 3 data

points)? Illustrate through meaningful visualization the relationship between price and the NBV of the

business’ assets at end of Y5.

 Annual return equivalent.

Let’s assume you have selected the best price to maximize the value of the business and let’s assume you can

sell the business at the end of Y5. Now your friends are thinking “we can invest the 550 K USD in the stock

market at 10% annual return. Keeping the money invested, five years down the road will get a return without

the risk of a startup and all the operational hassle”. So they want to know what is the annual return

equivalent of the business and if it is above 10%. (Here we use “equivalent” because the annual return of a

start-up will not be the same each year, but if we assume the same annual rate of return each year, what %

annual return rate will generate the same end value of the initial investment 5 years later? Do not forget

compounding, let’s say annually). If you assume that the average annual investment return over the next 5

years is expected to be 7% (for equivalent risk), what is the incremental dollar amount of the start-up’s assets

at the end of the 5-year period?

Instructions  There are no extra points for complexity – the simpler the better. When preparing the exercise we used 80

lines of Excel (less than 2 pages A4). We expect the whole task to take around 2-3 hours.

 Please prepare a short presentation (structure and content up to you) – max 5 slides.

 The case is designed in a way that you should not need any external information. If you stumble upon a

missing input or unclear point, make your best estimate / judgment / assumption. We will not be able to

answer questions. In any case it is our strict requirement that this remains exclusively your own work.

 Please submit your outputs (Excel model + PowerPoint presentation) by email within 5 hours of receiving the

assignment (even if they are only partially completed). We will use the sent email time stamp to establish the

start and end time.