Create an excel spreadsheet with the following information

Mary Fernandez is a student in the Spring Quarter class ofEnterprise Finance. It is now 7:30 AM and she is attending theOctober meeting of the San Diego Venture Group. There areapproximately 300 people attending the meeting: bankers,accountants, lawyers, headhunters and entrepreneurs. Mary is a bitlost and wanders to the back of the room to get a cup of coffee.Looking for the cream for her coffee she stumbles into JohnThompson.

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

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

After a year plus of effort John has written the necessarysoftware and through friends has come up with a product design.After going through a number of prototypes John has finally come upwith a product and is looking to roll it out. Friends have told himthat the surf and action sport market is where he should firstlaunch the product. John wants to immediately roll out to bicycleshops, running shops and others. The ultimate goal is to sell theproduct through the major sporting goods retailers and big boxstores. The initial reactions to the Iron Fit have beenoverwhelmingly positive. The next step is for John to raise somemoney and build a company but he is at a loss as to how to buildthe financial statements required for presentations to AngelInvestors and Venture Capitalists.

Mary tells John that she would be happy to build the model. Johnmentions that he will need to hire 3 additional engineers tocontinue to refine and expand the product. Each engineer makesapproximately $120,000 per year. In addition, he will need two VPsof marketing. One to sell the product to the action sports industryand one to sell the product to traditional bicycle, running andfitness shops. Each VP will command a salary of $150,000 per year.In addition, they will manage 3 sales people each, six total withinthe Company, at approximately $80,000 per annum. The marketingbudget, for advertising and other materials, will be $350,000 foreach marketing group per year or $700,000 for the Company as awhole. Marketing expenses are expected to be spent in an equalamount per month. The initial back office will contain anaccountant @ $80,000 and two receptionists/secretaries @ $40,000.Additional salary expenses, including payroll taxes, healthinsurance and other benefits, are budgeted at 30% of totalsalaries. John hopes to make a salary of $175,000. Annual officeexpenses including occupancy are expected to be $25,000. Johnexpects his salary expenses to increase by 5% in the second yearand 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%. Thesales price of the Iron Fit will be $125.00 to stores with theretail price being approximately $160.00.   John isplanning to keep the sale price constant in the second year inorder to grab more market share.

Please complete an initial model for John’s company for thefirst two years. (This will require a month by month analysis.)What is the amount of capital needed? Assuming a required rate ofreturn by investors of 20% per annum and the sale of the company atthe end of the second year at seven times Year 2 EBITDA what is thecompany worth? (Please note that when discounting monthly cashflows you will need to divide the interest rate by 12.) What do youthink about this deal? What questions do you need to ask if youwere 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