Excel

Problem 1: See “Problem 1” Tab in Excel Answer Sheet

Problem 2

In June of 2019 Bob and Lila founded a company to make dog collars with GPS tracking chips. They capitalized the company with $100,000 for 1,000 shares.

Today Bob and Lila are analyzing an offer from an Angel investor to purchase $2,000,000 of non-participating, convertible preferred stock.

The angel investor requires a minimum of a 40% annualized return

Bob and Lila are prepared to give up 30% of company for the Angel investment

After reviewing similar technology companies with the Angel investor, Bob, Lila and the investor agree to assume that the company will be sold at the end of 5 years from the date of the Angel investment for a price of 15 times Net Earnings After Taxes for the year (12 months) preceding the sale date (year 5)

Question 1)
Complete (fill in or calculate) the highlighted (olive green) cells in column E of Stacked bar diagram in tab 2.

Question 2)
a) What is the dollar amount of sales proceeds (total price for company) needed to meet the return payoff required by the Angel investor from sale of company at end of year 5 to meet his/her 40% annual rate of return (IRR) investment hurdle? (cell G14)
b) What are the dollar proceeds (payouts) to the Angel investor and to the Founders (Bob and Lila) from the sale from the company (cells G21 and G34)

Question 3)
a) What is required Net Earnings After Tax in year 5 for the company to potentially realize the sale value required to meet the investor’s required annualized rate of return (assuming they can sell company for 15 times Net Earnings After Taxes).
b) Bob and Lila want to know how much Revenue they need in year 5 to support the required price for sale of the company. Using the assumptions in the attached income statement (B59:E68), what is the Revenue required in year 5?
c) Bob and Lila’s company recorded revenues of $2,000,000 in the year preceding today (date of investment). What is the annual revenue growth rate needed to realize the required Revenue in year 5?

Problem 3

Andesite Technologies, Inc. designs and manufactures specialized motorized bicycles for the burgeoning eco-mobility market.

The Company’s actual financial statements for Fiscal Years Ended (FYE) 12/31/2018, 12/31/2019 are shown on the answer spreadsheet (Tab: Problem 3).

Series A Shareholder has asked and received Board approval (as of today) for Andesite to pay a dividend to Series A shareholder assuming that Days Cash on Hand ratio for the balance sheet (Cash/(operating expenses/365 days)) equals 90 days.

The Series A investor wants to know whether they can they can expect a dividend in 2020 and/or 2021 and if so, how much for each year?

As the Company’s CFO, the Board has asked you to prepare a projected income statement, balance sheet and cash flow statement for FYE 12/31/2020 and FYE 12/31/21. (Assume that there are 365 days in both years) to determine whether and/or when (at the end of which years) the company can pay its Series A shareholder a dividend.

(All numbers in thousands same as financial statements)

For 2020 You assume the following:
Income statement:
i. Revenues of $7,000
ii. Gross margin = 33%
iii. Other RATIOS same as 2019
Balance sheet:
iv. Days Cash on Hand (Cash/(operating expenses/365 days)) must equal 90 days at year end (Line 69)
v. Other 2020 balance sheet RATIOS remain the same as 2019
Questions:
1. Prepare projected 2020 financial statements in column D of the Problem 3 tab under the headings “Projected FYE 12/31/2020”.
2. Will Andesite generate enough cash to support operations in 2020 without any additional cash put into company (from debt or equity) – yes or no? (cells D111 or D113)
3. (cell B111 or cell(s) in 2020 cash flow statement)
If YES, provide the amount of the dividend in cell. Assume all excess cash (cash not required for working capital) paid out to Series A shareholder.
If NO, indicate size bank loan in cell D102 that would be required in 2020 to support operations assuming company needs to maintain 90 Days Cash on Hand per the assumptions? Make sure you adjust financial statements to appropriately reflect loan. (Hint- look at balance sheet)

For 2021 You assume the following:
a. Your statements for 2021 start with and reflect your 2020 projected statements including any debt or equity added to balance sheet
b. 2021 Income statement assumptions:
i. Revenues of $12,000
ii. RATIOS same as 2020
c. 2021 Balance sheet: assumptions
iii. Days Cash on Hand (Cash/(operating expenses/365 days)) must equal 90 days at year end (same as 2020)
iv. Company believes it can improve its working capital management to:
a. Reduce “Accounts Receivable in Days of Revenue” to 45 days
b. Reduce “Inventory in Days of COGS” to 60 days
c. And get away with Increasing its “Accounts Payable in Days of Cost of Sales” to 40 days
v. Other balance sheet RATIOS remain the same as 2020
Questions:
4. Prepare projected 2021 financial statements in column E of the Problem 3 tab under the headings “Projected FYE 12/31/2021”.
5. Will Andesite generate enough cash to support operations in 2021 if there are no changes to debt or equity – yes or no? (Cell E111 or E113)
6.
If YES, provide the amount of the dividend in cell. Assume all excess cash (cash not required for working capital) paid out to Series A shareholder.
If NO, indicate size bank loan in cell E102 that would be required in 2020 to support operations assuming company needs to maintain 90 Days Cash on Hand per the assumptions? Make sure you adjust financial statements to appropriately reflect loan. (hint – look at balance sheet)

Problem 4

4 years ago, Ravi and Jamelle, started RightPack to development and sell an APP they developed to optimize luggage selection for travelers.

In setting up the company they decided to simultaneously establish an employee stock option plan equal to 20% of the initial total shares outstanding knowing that they needed to hire several employees immediately. One benefit to setting up the plan when they founded the company was to establish a low exercise price. (They knew from their entrepreneurial finance class that exercise prices for options needed to reflect the most current value of the company at the time of the option award.) As a result, an outside accounting firm set an initial exercise price for the options of $0.02 per share. RightPack granted options on 50,000 shares to 5 employees (collectively referred to as the “Initial Employees”). The options vest monthly over 4 years with a 1 year “cliff” and are options for common stock – one share of common per option.

On the first day of year 2 they closed a Series A Convertible Preferred round for $2,000,000 at $2.00 per share. The preferred shares have a 1x preference (of the investment) and do not participate if the investor keeps the preference. However, Series A shares do enjoy full-ratchet (100%) antidilution protection. This protection is achieved through through issuance of additional shares if necessary.

At the same time (1st day of year 2), they hired a Chief Technology Officer (CTO) and awarded him options on 25,000 common shares. In keeping with the requirement to set the exercise price based on the current value of the company, the Board awarded the options with the same terms (4 year vesting and 1 year “cliff”) as the initial options but with an exercise price of $2.00 per share.

A pandemic then shut down global travel for a while severely hurting company growth. The same Series A investor, not wanting to give up on the idea, agreed to fund a Series B round on the first day of year 3 but at a reduced price per share. The investor purchased 1,000,000 shares of Series B Convertible Participating Preferred stock for $1,250,000. The Series B shares have a preference of 1.5X the invested amount. After being paid the preference the Investor will receive its “as-if Series B preferred converted to common” percentage (fully diluted) of proceeds available to common shareholders.

On the same day as the Series B funding, the investor and the company agreed that they needed a more seasoned CEO and hired Igor as CEO. The terms were the same as other grants but with an appropriate exercise price established by the Series B round pricing.

(A history of the option awards and option terms are described in lines 66-74 on Answer Spreadsheet)

On the last day of 4th year since founding MEGATravel purchased RightPack for $7,000,000 in cash.

QUESTIONS:

A) Calculate and Fill in the highlighted cells (yellow) in Columns B through H (Cells B9:H59) of the Stacked bar diagram for stacked bar in Problem 4 tab of the Answer spreadsheet. I suggest using /showing formulas used to fill in highlighted cells. You can insert hard values calculated in head/by hand but I may not be able to follow if error. Any reference to “ownership interest” is the percentage of fully diluted shares (all converted to common). (note: you may get a circular reference error in cell H27 depending on how you calculate. Input the hard value if you get such an error).
B)
a. Calculate and fill in highlighted cells (yellow) in column K regarding ownership percentages of sale proceeds. (You need to determine values for all yellow highlighted cells in Column K to determine fully diluted ownership percentages. For clarification, “fully diluted” is the ownership interest if converted to common stock.)
b. What is price per share of common stock available to common stock shareholders in this transaction? Indicate answer in cell D77 (highlighted in yellow). (Hint: consider what the appropriate number of shares should be and consider the pool available to pay common shareholders after paying preferences.)
c. Complete the Waterfall (Payout) table (Fill in highlighted cells D82-D87). Please show dollar amounts received by shareholders. For Founders, CTO, CEO and Initial employees please record the NET amount received (e.g. the amount received after subtracting cost of exercising options). (IMPORTANT NOTE: By agreement the value of any unexercised options and the proceeds from payments to exercise options on the date of the acquisition are returned to the balance sheet of the company for use by the new owner and not split with other common shareholders (in other words these adjustments/proceeds are factored into the purchase price and benefit the acquirer. They are not redistributed/reallocated to the other common RightPack shareholders as sales proceeds)