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.
. 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 XXXXXXXXXXfor 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
. 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.)
. 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
ased 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
e subtracted in an income statement. Use the information from the Balance
Sheet tab to find interest expense.
. 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
alance sheet.
a. First calculate ratios for years 1 and 2 based on the Income Statements and the
Balance Sheet.
. 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,
usiness 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.
. 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
ing up factors not described in the case.
XXXXXXXXXXTax 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 e
or. 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 co
ect 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
einvestment rates are the required rate on the project.