20180501003454apexprintingfinancialstatements 20180501003455finc615_npv_irr_ip_4.xlssc
Assignment Scenario:
Over lunch, you and Mary meet to discuss next steps with the expansion project.
“Do we have everything we need on sales and costs?” you ask. ”It must be time to compute the net present value (NPV) and internal rate of return (IRR) of the Apix expansion project.”
“We have the data from James and Luke regarding projected sales and costs, respectively, for the food packaging project,” says Mary. “It is feasible to project that we will receive a tax break from this implementation. I have information from our audit firm that indicates that future depreciation methods for taxes will be straight-line; however, the corporate rates will be reduced to 35% as we assumed in our weighted average cost of capital (WACC) calculation.”
“That sounds good,” you say.
“Right,” says Mary. “You can use a WACC of 10% for the computation of the NPV and comparison for IRR.”
“I’ve got the information I need from Luke and James,” you say. “Does this look right to you? Here’s what they gave me,” you say, as you hand a sheet of paper to Mary.“Let’s look at this now while we’re together,” she says.
The information you hand to Mary shows the following:
- Initial investment outlay of $30 million, consisting of $25 million for equipment and $5 million for net working capital (NWC) (plastic substrate and ink inventory); NWC recoverable in terminal year
- Project and equipment life: 5 years
- Sales: $25 million per year for five years
- Assume gross margin of 60% (exclusive of depreciation)
- Depreciation: Straight-line for tax purposes
- Selling, general, and administrative expenses: 10% of sales
- Tax rate: 35%
You continue your conversation.
“It looks good,” says Mary. “Use this information from Luke and James to compute the cash flows for the project.”
“No problem,” you say.
“Then, compute NPV and IRR of the project using the Excel spreadsheet I sent earlier today,” says Mary. “Use the IRR financial function for the computation of IRR.”
“Okay,” you say. “I’ll submit my Excel file showing the computation of cash flows, NPV, and IRR by the end of week so you can look at it over the weekend.”
“Thanks,” says Mary.
Apex Printing
Balance Sheets
As of December 31, 2013 and 2012
000$ 000$
Assets 2013 2012
Cash 6,000
5,700
Accounts Receivable 2,350 2,300
Inventory 12,100 6,500
Total Current Assets 20,450 14,500
Land 25,000 20,000
Building & Equipment 300,000 300,000
Less: Accumulated Depreciation – Building & Equipment (187,850) (160,000)
Total Long Term Assets 137,150 160,000
Total Assets 157,600 174,500
Liabilities and Stockholders’ Equity
Accounts Payable 4,600 3,500
Salaries Payable 0 2,100
Interest Payable 1,500 0
Short Term Notes Payable 12,000 0
Taxes Payable 0 5,600
Total Current Liabilities 18,100 11,200
Mortgate Payable 54,950 100,000
Total Long Term Liabilities 54,950 100,000
Common Stock 60,000 60,000
Retained Earnings 24,550 3,300
Total Stockholders’ Equity 84,550 63,300
Total Liabilities and Stockholders’ Equity 157,600 174,500
Apex Printing
Income Statements
For the Periods Ended December 31, 2013 and 2012 000$ 000$
2013 2012
Revenue: 450,000 475,000
Less: Cost of Goods Sold (324,300) (374,500)
Less: Depreciation Expense (27,850) (26,000)
Gross Margin 97,850 74,500
Selling, General & Administrative Expenses (29,100) (32,000)
Income Before Interest & Taxes 68,750 42,500
Interest Expense (7,500) (6,000)
Income Before Taxes 61,250 36,500
Income Taxes (35,000) (30,000)
Net Income 26,250 6,500
Apex Printing
Statement of Cash Flows
For the Period Ended December 31, 2013
000$
Cash Flows from Operating Activities:
Net Income 26,250
Adjustments to reconcile net income to net cash provided by
operating activities
Depreciation Expense 27,850
Increase in accounts receivable (50)
Increase in inventory (5,600)
Decrease in salaries payable (2,100)
Increase in interest payable 1,500
Decrease in taxes payable (5,600)
Increase in Short Term notes Payable 12,000
Increase in accounts payable 1,100
Net Cash Flow from Operating Activities
55,350
Cash Flows from Investing Activities:
Cash paid to purchase land
(5,000)
Net Cash Flow from Investing Activities
(5,000)
Cash Flows From Financing Activities:
Cash paid for mortgage (45,050)
Cash paid for dividends (5,000)
Net Cash Flow from Financing Activities
(50,050)
Net Increase in Cash
300
Plus: Cash Balance at December 31, 2012
5,700
Cash Balance at December 31, 2013
6,000
> – .00%
0 1 2 5 0 0 0 0 0 0NPV
IRR
FIN 6
1
5
NPV and IRR calculations
Cost of Capital
1
0
Time/yr
3
4
Cash flow Input here
Discounted CF
NPV 0
=cf1/((1+n)^1)
=cf2/((1+n)^2)
=cf3/((1+n)^3)
=cf4/((1+n)^4)
=cf5/((1+n)^5)
PV factor
0.9090909091
0.826446281
0.7513148009
0.6830134554
0.6209213231
IRR
0.00%
Sheet3