Question

Create an excel spreadsheet with the following information

Mary Fernandez is a student in the Spring Quarter class of Enterprise Finance. It is now 7:30 AM and she is attending the October meeting of the San Diego Venture Group. There are approximately 300 people attending the meeting: bankers, accountants, lawyers, headhunters and entrepreneurs. Mary is a bit lost and wanders to the back of the room to get a cup of coffee. Looking for the cream for her coffee she stumbles into John Thompson.

John is the son of a doctor who majored in Computer Science while at UCLA. He has relocated back to San Diego and for the last four years he has been a software engineer.

John is an avid waterman. He surfs, swims, paddles and stand up paddles every day and hopes to do so the remainder of his life. John has found that FitBit, the Apple Watch and other devices do not work for him. John needs something that is waterproof, can track his efforts if he is running or biking and calculate his calorie and level of exercise if he is swimming, surfing, paddling, etc. There are devices that handle part of what he is looking for but nothing seems to have it all. John decides to write the programing that will be the basis for the “Iron Fit” that keep track off John’s exercise activities no matter what he is doing.

After a year plus of effort John has written the necessary software and through friends has come up with a product design. After going through a number of prototypes John has finally come up with a product and is looking to roll it out. Friends have told him that the surf and action sport market is where he should first launch the product. John wants to immediately roll out to bicycle shops, running shops and others. The ultimate goal is to sell the product through the major sporting goods retailers and big box stores. The initial reactions to the Iron Fit have been overwhelmingly positive. The next step is for John to raise some money and build a company but he is at a loss as to how to build the financial statements required for presentations to Angel Investors and Venture Capitalists.

Mary tells John that she would be happy to build the model. John mentions that he will need to hire 3 additional engineers to continue to refine and expand the product. Each engineer makes approximately $120,000 per year. In addition, he will need two VPs of marketing. One to sell the product to the action sports industry and one to sell the product to traditional bicycle, running and fitness shops. Each VP will command a salary of $150,000 per year. In addition, they will manage 3 sales people each, six total within the Company, at approximately $80,000 per annum. The marketing budget, for advertising and other materials, will be $350,000 for each marketing group per year or $700,000 for the Company as a whole. Marketing expenses are expected to be spent in an equal amount per month. The initial back office will contain an accountant @ $80,000 and two receptionists/secretaries @ $40,000. Additional salary expenses, including payroll taxes, health insurance and other benefits, are budgeted at 30% of total salaries. John hopes to make a salary of $175,000. Annual office expenses including occupancy are expected to be $25,000. John expects his salary expenses to increase by 5% in the second year and the other expenses to increase by 10%.

Capital expenses include a computer for each individual, $1,000, two network printers, $1,000, telephone, $1,000, two servers, $5,000 each, software, $10,000, and networking, $1,000.

John expects his gross margin as a % of sales to be 50%. The sales price of the Iron Fit will be $125.00 to stores with the retail price being approximately $160.00.   John is planning to keep the sale price constant in the second year in order to grab more market share.

Please complete an initial model for John’s company for the first two years. (This will require a month by month analysis.) What is the amount of capital needed? Assuming a required rate of return by investors of 20% per annum and the sale of the company at the end of the second year at seven times Year 2 EBITDA what is the company worth? (Please note that when discounting monthly cash flows you will need to divide the interest rate by 12.) What do you think about this deal? What questions do you need to ask if you were an investor?

Estimated Number of Units Sold

Month       Month         Month       Month         Month       Month      

1                2                3                4                5                6

0                250            500            1,000         5,000         6,000

Month       Month         Month       Month         Month       Month      

7                8                9                10              11              12

7,000         8,000         8,000         8,000         9,000         9,000

Month       Month         Month       Month         Month       Month      

13              14              15              16              17              18

10,000       10,000         12,000       12,000         15,000       15,000

Month       Month         Month       Month         Month       Month      

19              20              21              22              23              24

17,000       17,000         20,000       20,000         20,000       20,000

Answer 1

Initial capital needed
Computer (15#*1000)	15000
Two net work printers	1000
Telephone	1000
Two servers	10000
Software	10000
N/Wkg	1000
Total	38000
#Total no.of individuals
Engineers	3
VPs	2
Sales people under each VP(2*3)	6
A/ctant	1
Receptionist/sec.	2
John	1
Total no.of individuals	15

Month	0	1	2	3	4	5	6	7	8	9	10	11	12
0.Initial capital exp. (As in Table)	-38000
1.Sales units		0	250	500	1000	5000	6000	7000	8000	8000	8000	9000	9000
2.Selling price/unit		125	125	125	125	125	125	125	125	125	125	125	125
3.Gross Margin(2*50%)		62.5	62.5	62.5	62.5	62.5	62.5	62.5	62.5	62.5	62.5	62.5	62.5
4.Total Gross margin(1*3)		0	15625	31250	62500	312500	375000	437500	500000	500000	500000	562500	562500
5.Engineer's salary(120000/12*3)		-30000	-30000	-30000	-30000	-30000	-30000	-30000	-30000	-30000	-30000	-30000	-30000
6. VP's salary(150000/12*2)		-25000	-25000	-25000	-25000	-25000	-25000	-25000	-25000	-25000	-25000	-25000	-25000
7. Sales salary(80000/12)		-6667	-6667	-6667	-6667	-6667	-6667	-6667	-6667	-6667	-6667	-6667	-6667
8. Mktg. exp.(700000/12)		-58333	-58333	-58333	-58333	-58333	-58333	-58333	-58333	-58333	-58333	-58333	-58333
9. Back office salaries(80000+40000)12		-10000	-10000	-10000	-10000	-10000	-10000	-10000	-10000	-10000	-10000	-10000	-10000
10. Benefits(5+6+7+9+11)*30%		-25875	-25875	-25875	-25875	-25875	-25875	-25875	-25875	-25875	-25875	-25875	-25875
11.John's salary(175000/12)		-14583	-14583	-14583	-14583	-14583	-14583	-14583	-14583	-14583	-14583	-14583	-14583
12.Office exp.(25000/12)		-2083	-2083	-2083	-2083	-2083	-2083	-2083	-2083	-2083	-2083	-2083	-2083
14.Net cash flow(0+4+(5 to 12))	-38000	-172542	-156917	-141292	-110042	139958	202458	264958	327458.3	327458	327458	389958.3	389958
15.EV at end Yr. 2
16.Total (14+15)	-38000	-172542	-156917	-141292	-110042	139958	202458	264958	327458.3	327458	327458	389958.3	389958
PV F at 20%12=1.67%	1	0.98357	0.9674	0.9515	0.9359	0.9205	0.90541	0.89053	0.875906	0.86152	0.84737	0.833449	0.81976
PV at 1.67%	-38000	-169708	-151804	-134443	-102988	128835	183307	235954	286822.7	282111	277478	325010.3	319672
NPV /Company's present worth	53919832

Month	13	14	15	16	17	18	19	20	21	22	23	24
0.Initial capital exp. (As in Table)
1.Sales units	10000	10000	12000	12000	15000	15000	17000	17000	20000	20000	20000	20000
2.Selling price/unit	125	125	125	125	125	125	125	125	125	125	125	125
3.Gross Margin(2*50%)	63	63	63	63	63	63	63	63	63	63	63	63
4.Total Gross margin(1*3)	625000	625000	750000	750000	937500	937500	1062500	1062500	1250000	1250000	1250000	1250000
5.Engineer's salary(120000/12*3)	-30000	-30000	-30000	-30000	-30000	-30000	-30000	-30000	-30000	-30000	-30000	-30000
6. VP's salary(150000/12*2)	-25000	-25000	-25000	-25000	-25000	-25000	-25000	-25000	-25000	-25000	-25000	-25000
7. Sales salary(80000/12)	-6667	-6667	-6667	-6667	-6667	-6667	-6667	-6667	-6667	-6667	-6667	-6667
8. Mktg. exp.(700000/12)	-58333	-58333	-58333	-58333	-58333	-58333	-58333	-58333	-58333	-58333	-58333	-58333
9. Back office salaries(80000+40000)12	-10000	-10000	-10000	-10000	-10000	-10000	-10000	-10000	-10000	-10000	-10000	-10000
10. Benefits(5+6+7+9+11)*30%	-26093.8	-26094	-26094	-26094	-26094	-26094	-26094	-26094	-26093.8	-26094	-26094	-26093.8
11.John's salary(175000/12)	-15312.5	-15313	-15313	-15313	-15313	-15313	-15313	-15313	-15312.5	-15313	-15313	-15312.5
12.Office exp.(25000/12)	-2291.67	-2291.7	-2291.7	-2291.7	-2291.7	-2292	-2291.7	-2291.7	-2291.67	-2291.7	-2291.7	-2291.67
14.Net cash flow(0+4+(5 to 12))	451302	451302	576302	576302	763802	763802	888802	888802	1076302	1076302	1076302	1076302	9665625	Yr. 2 EBITDA
15.EV at end Yr. 2												67659375	EV=9665625*7
16.Total (14+15)	451302	451302	576302	576302	763802	763802	888802	888802	1076302	1076302	1076302	68735677
PV F at 20%12=1.67%	0.806294	0.79305	0.78	0.7672	0.75461	0.7422	0.73002	0.71803	0.706237	0.69464	0.68323	0.672005
PV at 1.67%	363882	357905	449529	442145	576372	566905	648846	638188	760124.8	747639	735359	46190690
NPV

The deal is good as the NPV is POSITIVE.

As an investor, on eneeds to sure about:

1. continued demand as projected

2. possibility of escalation in costs projected.