Hi, attached is the assignment I received in a class. I have done a majority of it, but I am really stuck on the part where "John expects his gross margin as a % of sales to be 50%" and basically everything after that. I know that the we have to calculate the PV of Cash Flows, PV of Sale, Total Value of the Company, and the Capital required. The professor told us that the following values for each would be:

PV Cash Flows: $7,444,887

PV of Sale: $42,083,032

Total Value of Company: $49,527,919

Capital Required: $809,458

However, my problem is just knowing what to do afterwards. I am also unsure if I did the EBITDA part right or not. I will attach what I have done on excel so far, but everything else should be on the third spreadsheet. Please let me know what to do and please let me know if I made any mistakes. Also the functions should be on the excel cells.

Thank you!

MGT 181 Enterprise Finance CASE STUDY #4 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. c Estimated Number of Units Sold Month 1 0 Month 2 250 Month 3 500 Month 4 1,000 Month 5 5,000 Month 6 6,000 Month 7 7,000 Month 8 8,000 Month 9 8,000 Month 10 8,000 Month 11 9,000 Month 12 9,000 Month 13 10,000 Month 14 10,000 Month 15 12,000 Month 16 12,000 Month 17 15,000 Month 18 15,000 Month 19 17,000 Month 20 17,000 Month 21 20,000 Month 22 20,000 Month 23 20,000 Month 24 20,000 Case Study #4 Revenue Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Units Sold Sales Price - 250 $125 500 $125 1,000 $125 5,000 $125 6,000 $125 Total Sales - $31,250 $62,500 $125,000 $625,000 $750,000 Month 7 7,000 $125 Month 8 8,000 $125 Month 9 8,000 $125 Month 10 8,000 $125 Month 11 9,000 $125 Month 12 9,000 $125 Month 13 10,000 $125 Month 14 10,000 $125 Month 15 12,000 $125 Month 16 12,000 $125 Month 17 15,000 $125 Month 18 15,000 $125 Month 19 17,000 $125 Month 20 17,000 $125 Month 21 20,000 $125 $875,000 $1,000,000 $1,000,000 $1,000,000 $1,125,000 $1,125,000 $1,250,000 $1,250,000 $1,500,000 $1,500,000 $1,875,000 $1,875,000 $2,125,000 $2,125,000 $2,500,000 Month 22 20,000 $125 Month 23 20,000 $125 Month 24 20,000 $125 $2,500,000 $2,500,000 $2,500,000 Year 1 61750 Year 2 188,000 $7,718,750 $23,500,000 Total Sales (24 months) $31,218,750 Expenses Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Month 13 Month 14 Month 15 Month 16 Month 17 Month 18 Employee Salaries Engineers VPs of Marketing Sales People Accountant Receptionists/Secretaries John Additional Salary Expenses $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 Other Expenses Marketing Budget Office Expenses $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $64,166.67 $2,291.67 $64,166.67 $2,291.67 $64,166.67 $2,291.67 $64,166.67 $2,291.67 $64,166.67 $2,291.67 $64,166.67 $2,291.67 Capital Expenses Computer Network Printers Telephone Servers Software Networking $15,000.00 $2,000.00 $1,000.00 $10,000.00 $10,000.00 $1,000.00 Total $39,000.00 $220,208.33 $220,208.33 $220,208.33 $220,208.33 $220,208.33 $220,208.33 $220,208.33 $220,208.33 $220,208.33 $220,208.33 $220,208.33 $220,208.33 $234,239.58 $234,239.58 $234,239.58 $234,239.58 $234,239.58 $234,239.58 Month 19 Month 20 Month 21 Month 22 Month 23 Month 24 Year 1 Year 2 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $360,000.00 $300,000.00 $480,000.00 $80,000.00 $80,000.00 $175,000.00 $442,500.00 $378,000.00 $315,000.00 $504,000.00 $84,000.00 $84,000.00 $183,750.00 $464,625.00 $64,166.67 $2,291.67 $64,166.67 $2,291.67 $64,166.67 $2,291.67 $64,166.67 $2,291.67 $64,166.67 $2,291.67 $64,166.67 $2,291.67 $700,000.00 $25,000.00 $770,000.00 $27,500.00 $234,239.58 $234,239.58 $234,239.58 $234,239.58 $234,239.58 $234,239.58 $2,642,500.00 $2,810,875.00 Revenue Month 1 Units Sold Sales Price - Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Month 13 Month 14 Month 15 Month 16 Month 17 250 $125 500 $125 1,000 $125 5,000 $125 6,000 $125 7,000 $125 8,000 $125 8,000 $125 8,000 $125 9,000 $125 9,000 $125 10,000 $125 10,000 $125 12,000 $125 12,000 $125 15,000 $125 $0.00 $31,250 $62,500 $125,000 $625,000 $750,000 $875,000 $1,000,000 $1,000,000 $1,000,000 $1,125,000 $1,125,000 $1,250,000 $1,250,000 $1,500,000 $1,500,000 $1,875,000 Employee Salaries Engineers VPs of Marketing Sales People Accountant Receptionists/Secretaries John Additional Salary Expenses $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $30,000.00 $25,000.00 $40,000.00 $6,666.67 $6,666.67 $14,583.33 $36,875.00 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 Other Expenses Marketing Budget Office Expenses $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $58,333.33 $2,083.33 $64,166.67 $2,291.67 $64,166.67 $2,291.67 $64,166.67 $2,291.67 $64,166.67 $2,291.67 $64,166.67 $2,291.67 $220,208.33 $220,208.33 $220,208.33 $220,208.33 $220,208.33 $220,208.33 $220,208.33 $220,208.33 $220,208.33 $220,208.33 $220,208.33 $220,208.33 $234,239.58 $234,239.58 $234,239.58 $234,239.58 $234,239.58 Total Sales Expenses Capital Expenses Computer Network Printers Telephone Servers Software Networking $15,000.00 $2,000.00 $1,000.00 $10,000.00 $10,000.00 $1,000.00 Total $39,000.00 EBITDA $220,208.33 -$188,958.33 -$157,708.33 -$95,208.33 $404,791.67 $529,791.67 $654,791.67 $779,791.67 $779,791.67 $779,791.67 $904,791.67 $904,791.67 $1,015,760.42 $1,015,760.42 $1,265,760.42 $1,265,760.42 $1,640,760.42 Month 18 Month 19 Month 20 Month 21 Month 22 Month 23 Month 24 Year 1 Year 2 15,000 $125 17,000 $125 17,000 $125 20,000 $125 20,000 $125 20,000 $125 20,000 $125 61750 188,000 $1,875,000 $2,125,000 $2,125,000 $2,500,000 $2,500,000 $2,500,000 $2,500,000 $7,718,750 $23,500,000 Total Sales (24 months) $31,218,750 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $31,500.00 $26,250.00 $42,000.00 $7,000.00 $7,000.00 $15,312.50 $38,718.75 $360,000.00 $300,000.00 $480,000.00 $80,000.00 $80,000.00 $175,000.00 $442,500.00 $378,000.00 $315,000.00 $504,000.00 $84,000.00 $84,000.00 $183,750.00 $464,625.00 $64,166.67 $2,291.67 $64,166.67 $2,291.67 $64,166.67 $2,291.67 $64,166.67 $2,291.67 $64,166.67 $2,291.67 $64,166.67 $2,291.67 $64,166.67 $2,291.67 $700,000.00 $25,000.00 $770,000.00 $27,500.00 $234,239.58 $234,239.58 $234,239.58 $234,239.58 $234,239.58 $234,239.58 $234,239.58 $2,642,500.00 $2,810,875.00 $1,640,760.42 $1,890,760.42 $1,890,760.42 $2,265,760.42 $2,265,760.42 $2,265,760.42 $2,265,760.42 $5,076,250.00 $20,689,125.00 MGT 181 Enterprise Finance CASE STUDY #4 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 1 0 Month 2 250 Month 3 500 Month 4 1,000 Month 5 5,000 Month 6 6,000 Month 7 7,000 Month 8 8,000 Month 9 8,000 Month 10 8,000 Month 11 9,000 Month 12 9,000 Month 13 10,000 Month 14 10,000 Month 15 12,000 Month 16 12,000 Month 17 15,000 Month 18 15,000 Month 19 17,000 Month 20 17,000 Month 21 20,000 Month 22 20,000 Month 23 20,000 Month 24 20,000