GROUP ASSIGNMENT 1 BUILDING FINANCIALS 10 POINTS Your instructor provided an Excel workbook via the course wall for a company called SensiStart, Inc. SensiStart is made up of engineers and they have done a great job of forecasting their sales through market research and forecasting their expenses on their income statement. They have been told by their mentor that having income statements is not enough. They must generate balance sheets and ultimately cashflow statements to really know how much money they need to ask from outside investors. The SensiStart team is ultra-frustrated because developing those income statements was hard enough. They are engineers, not finance people! Through a friend they heard that you were taking a new venture finance class at Whitman and that means that you get all this stuff. Those statements are a piece of cake for you. They have handed over all their work to you and have asked you to do the following: 1. Generate the balance sheet for years 13. a. Years 1 and 2 monthly and year 3 quarterly 2. Generate the cash flow statement for years 13. a. Years 1 and 2 monthly and year 3 quarterly 3. They also are not sure how to handle capital expenditures so, even though they have identified what they are buying and when, they have no idea what depreciation and amortization means. They have left this blank on their income statements. a. You will generate a depreciation and amortization schedule based on the assumptions below. 4. They are also not sure of how to handle insurance. Their agent is telling them they have to prepay it at the beginning of every year. Their insurance quote is: a. Year 1 $5,000 b. Year 2 and Year 3 $7,000 per year i. You will generate a prepaid expense schedule. ASSUMPTIONS YOU NEED TO BE AWARE OF 1. SensiStart has been incorporated as an S Corp. 2. They have outsourced manufacturing for this product and their supplier does not have much faith in them as a new company. The supplier wants to be paid up front for any orders. 3. Their original plan was to sell their product through electronics stores, but all the electronics stores they approached said that they needed to prove they could generate sales through their own channel first. Therefore, they are selling directly from their own website. The good thing about this is that there are no accounts receivables in the first two years. FIN STMTS Group Assignment 1 4. You are to assume all numbers on the income statements are correct. The ONLY change you will make to the income statements will be in the depreciation line item, amortization line item, and insurance expense line item. You will find these highlighted on the workbook. 5. The trademark is amortized for 10 years and the patent for 15 years. 6. All capital expenditures will be depreciated using a five-year straight-line depreciation with $0 salvage value. Please read the Capital Budget Expenditures tab for planned investments. 7. The SensiStart group has been able to scrape up $8,500 of their own money for the venture. That number can be found on the year 1 cash flow statement. 8. In year 3 SensiStart expects to start doing business with electronics stores. To be ready for their orders and to make sure they have inventory on time, they are placing a large order in May of year 2. 9. The electronics stores (wholesale) require 90-day payment terms, which SensiStart agreed to. EXPLICIT INSTRUCTIONS 1. You ARE NOT ALLOWED to hard input ANY numbers in the balance sheet and cashflow statement. This means that everything must be linked to the other statements. The ONLY NUMBER you will hard inputting will be the Outside Capital Investment number on year 1 of the cashflow statement (highlighted in yellow). You may also hard input any zeros or just leave the cell blank. 2. DO NOT change any numbers on the Income Statement, Sales Projections, or Costs, unless instructed to do so. TIPS 1. Before moving on to the next period make sure that the balance sheet is in balance. 2. If you get frustrated take a step back and think about what accounts are being impacted and how (increase? decrease?). 3. Make sure that your formulas are correct when summing up columns (or creating formulas). Some items get subtracted instead of added. ANSWER THE FOLLOWING QUESTIONS 1. The SensiStart team must figure out how much money they need to ask for from outside investors to be able to operate past year 3. They will only be able to raise money once before their three years are up, so it is essential that they get it right. Based on your analysis how much money (in addition to the $8,5oo they are putting in) do you recommend they ask for? Why this amount? 2. Based on the financials, when does SensiStart break even? Why? Hint: Look at your financials closely (month and year). 3. SensiStart just got back some key research that shows their customers are willing to pay $40 more for their product. Instead of a retail price of $89.99, they can charge $139.99. This also means that they can charge a wholesale price of $85.00. FIN STMTS Group Assignment 1 a. Please set the Outside Capital Investment cell for Aug year 1 on the cashflow statement to $85,000. What is the ending cash balance in December of year 2?