All instruction are in the documents provided
Microsoft Word - Review Assignment Opening a new business.docx FIN 449 Review Assignment: Opening a new business Report and discussion due Tuesday Jan 21 Download the spreadsheet Review Assignment to do all analysis. This assignment is more detailed than cases will be! All students must do this assignment individually o You must create your own spreadsheet (using the spreadsheet provided). o All students must submit their own written analysis. o You may discuss small parts with other students o While later projects can mostly be worked in teams, I want everyone to make sure they are ready for this class with basic Finance and Excel skills. Use cell references for all calculations. Feel free to ask me questions. In this project, you will create a company based on a project I imagined producing t-shirts. If you want to change the project, it can be anything you want, but use the basic structure and numbers I created. For example, you could sell beer growlers for $20 each but apply all materials and equipment used to the growlers. The nature of the project will only matter in the discussion. Instructions: 1. First you will use the inputs from the project cash flows tab of the Review Assignment put together a capital budgeting project. Do this in Excel on the spreadsheet (no need to write the intermediate numbers in write up document.) a. There are two pieces of machinery. Assume they are depreciated 100% in year 1 (bonus depreciation) due to 2018 tax law change. For tax purposes, the machinery will have a book value of zero at the end of year 1 so when you sell the equipment it will be fully taxed. Assume quantity year 1 = 10,000 tshirts. b. Use the inputs given in spreadsheet to calculate. Use cell references! i. Revenues each year (separately years 1 – 5) ii. Variable costs each year (also separately years 1-5) iii. Capital expenditure cash flows for buying machine in year 0 and selling in year 5. iv. Operating Cash Flows v. Net Working capital balance, change and recovery of Net Working capital. Then Net working capital cash flow. vi. Calculate the free cash flows of the project from years 0-5. Note this is also the free cash flow of the company as there is only one project. c. Initially use the 8% required rate given and find Net Present Value, Internal rate of return and Modified Internal Rate of Return. 2. Create a Balance Sheet in year 0 (2021) for the company in Balance Sheet tab for the first year of the project (which is also the first year of the company). You will reference the cells created in the Project CF tab. a. Use assets from project given: cash, Inventories, both pieces of equipment. To cushion the company for unexpected shortfalls, assume you raise an additional $10,000 of cash over the $5000 needed for the project. Note: Put the full cost of equipment (before taxes) as you will need to raise the full amount initially. FIN 449 Review Assignment: Opening a new business Report and discussion due Tuesday Jan 21 b. For Liabilities assume a bank will loan you $5000 of short term debt and long term debt of 80% of the equipment value in an amortized loan. c. You will need to raise the remainder of the money to finance the assets through equity (friends and family). d. Check that your Balance Sheet balances! 3. In the Balance sheet tab, also find the weighted average cost of capital using a few assumptions: a. First calculate weights of debt and equity. For a public company, you would use market values to find the weights. However, for this private new company, you will use balance sheet values as this is the proportion invested in the company. (Add short and long term debt together assuming they have the same costs.) b. Assume a cost of debt of 7%. (You will not be able to use traded bonds). c. Even though the company’s equity holders are probably not well diversified, you promise equity holders an amount based on the CAPM (Security Market Line model) using the estimated numbers in the spreadsheet. d. Calculate weighted average cost of capital. e. Because there is only one project, the required rate on the project is the cost of capital so recalculate the Net Present Value and Modified Internal Rate of return based on the weighted average cost of capital in the cf tab. 4. Create an Income Statement in the Income Statement tab for the first two years of the project referencing the cash flows from the Project analysis. a. Note: While we do not subtract interest expense in project analysis, it needs to be subtracted in an income statement. Use the information from the Balance Sheet tab to find interest expense. b. Assume the t-shirt project is the only source of revenues and costs. c. Calculate the two years of income statements first revenues, variable costs and fixed costs from expected project cash flows. 5. Calculate variations on the income statement: Create the Income Statement if quantity is 30% below expected. (Since there is only one product this will reduce revenues and variable costs by 30%.) 6. Calculate financial ratios in the Income Statement tab. Refer to income statements and balance sheet. a. First calculate ratios for years 1 and 2 based on the Income Statements and the Balance Sheet. b. I gave suggested ratios but you may want to add others to complete your analysis. c. Do the ratios based on income 30% below expected. Most ratios can be simply dragged over. 7. Other calculations: To make a recommendation on whether the company should be created (see #8), you may want to do other calculations. Note that in an amortized loan, business must pay both interest and principal although only the interest expense is tax deductible and on the Income Statement. Also the cash flow from the project analysis does not include financing cash flows. FIN 449 Review Assignment: Opening a new business Report and discussion due Tuesday Jan 21 8. Write up an analysis of this proposal on whether this company should be created based on this project. At this point you might change the type of project to selling a different product. a. Summarize the situation, decision, and main factors to consider. b. Secondly discuss the anticipated success of the project use numbers as expected. (Be specific about what the numbers are telling you.) c. Discuss how unexpected factors (be specific about these risks) may affect numbers analyzed. Be specific about how these factors would likely affect the numbers. Discuss at least two unexpected events might affect this company. d. How does the bonus depreciation from 2018 Tax Laws affect the decision? e. Finally discuss whether you believe you should undertake this project given the numbers, the risks and what you know about the project. 9. Submit your spreadsheet and short written analysis on Canvas before class on Jan 13. Include a few key numbers in the analysis, but the write up should be based on the numbers not a summary of the numbers. 10. Come to class ready to discuss your analysis. Notes: 1) This project has much more explanation than later cases will. Feel free to be creative and bring up factors not described in the case. 2) 2018 Tax Cut and Jobs Act included a provision for 100% of the cost of most equipment to be depreciated for tax purposes the first year the project is in use. (I used year 1 and called this depreciation “Bonus Depreciation”.) Unfortunately, the Higgins book does not discuss this. 3) If you have any questions, please ask me through email or office hours! Do not spend hours trying to understand a small detail. This example is totally written by me so it is possible I left out something needed or have an error. I am also happy to answer any questions about the material. This project is to review and get you ready for this class, not to frustrate you! 4) Don’t forget to adjust for NPV flaw in Excel; the function is =NPV(r, CF1 – CFn) Excel assumes the first cash flow is in time 1 not 0. To correct for this, I use only cash flows 1 through n in the NPV function then outside of the function I add CF0. Net Present Value in Excel: =NPV(r, CF1 – CFn) + CF0 5) When calculating internal rate of return use all cash flows =IRR (CF0 – CFn) For Modified IRR (=MIRR), also use all cash flows and assume both financing and reinvestment rates are the required rate on the project.