Case study- Excel (Coral Bay Electronics)
ksmith21
CoralBayElectronics_Student_2.xlsxProject
| Coral Bay Electronics | ||||||||||||||
| Input Area: | ||||||||||||||
| Equipment | ||||||||||||||
| Salvage value | ||||||||||||||
| R&D | ||||||||||||||
| Marketing study | ||||||||||||||
| Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | ||||||||||
| New tracker sales(units) | ||||||||||||||
| Depreciation rate | ||||||||||||||
| Sales of old tracker | ||||||||||||||
| Lost sales in old tracker (units) | ||||||||||||||
| Price of new tracker | ||||||||||||||
| Variable cost of new tracker | ||||||||||||||
| Fixed cost of new tracker | ||||||||||||||
| Price of old tracker | ||||||||||||||
| Price reduction of old tracker | ||||||||||||||
| Variable cost of old tracker | ||||||||||||||
| Tax rate | ||||||||||||||
| Net working capital: % of next year net sales | ||||||||||||||
| Required return | ||||||||||||||
| Output Area: | ||||||||||||||
| Sales | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | ||||||||
| Sales of new tracker | ||||||||||||||
| Lost sales due to new tracker | ||||||||||||||
| Lost rev. due to new tracker | ||||||||||||||
| Net sales of new tracker | ||||||||||||||
| Variable Cost (VC) | ||||||||||||||
| VC of new tracker | ||||||||||||||
| VC saving-Due to existing sales loss | ||||||||||||||
| Net VC of new tracker | ||||||||||||||
| Net Sales | ||||||||||||||
| Net variable costs | ||||||||||||||
| Fixed costs | ||||||||||||||
| Depreciation | ||||||||||||||
| Operating income before taxes (EBIT) | ||||||||||||||
| Taxes (20%) | ||||||||||||||
| Net operating profit after taxes (NOPAT) | ||||||||||||||
| +Depreciation | ||||||||||||||
| Operating cash flows | ||||||||||||||
| Net Working Capital Requirement (NWC) | ||||||||||||||
|
tc={BF327D80-BB3D-4062-9018-4808AEEC72ED}: [Threaded comment] Your version of Excel allows you to read this threaded comment; however, any edits to it will get removed if the file is opened in a newer version of Excel. Learn more: https://go.microsoft.com/fwlink/?linkid=870924 Comment: NWC investment starts in year 0 |
NWC Cash Flow | |||||||||||||
|
tc={B131CF15-39E9-4757-80B6-E62FD593ABA1}: [Threaded comment] Your version of Excel allows you to read this threaded comment; however, any edits to it will get removed if the file is opened in a newer version of Excel. Learn more: https://go.microsoft.com/fwlink/?linkid=870924 Comment: It is the amount of the NWC that is not recovered yet |
After-tax Salvage Value (year 5 only) | Year 5 | ||||||||||||
| Salvage value | ||||||||||||||
| Book value | ||||||||||||||
| Gain or Loss | ||||||||||||||
| After-tax salvage cash flow | ||||||||||||||
| Net Cash Flow (Net CF) | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | ||||||||
| Cumulative Net CF | ||||||||||||||
| Payback period | ||||||||||||||
| PI | ||||||||||||||
| IRR | ||||||||||||||
| MIRR | ||||||||||||||
| NPV | ||||||||||||||
| Your final recommendation for this project: | ||||||||||||||
