Questions are based on the following mini-case: CGX Transmitters is developing a 3rd generation optical transmitter. Somebody in your department worked on it and prepared a pro-forma analysis but then he left the firm last week. You suspect that he was fired because of a very bad analysis.
1 a)
Take the completed pro-forma analysis and correct all mistakes.
Carefully read the case. Use the provided pro-forma analysis and correct all the mistakes your colleague made. For your answer, provide the completed pro-forma analysis with the project OCF, taxes, capital investment, side effects, and project net cash flow in year 0, 1, 2, 3, 4. Provide all columns and highlight in yellow your final net cash flow answers. The pro-forma analysis should be performed in the same spreadsheet, with well organized, complete, and readable explanations so that it can be passed by to the CFOs desk tomorrow COB, and sent to the CEO shortly.
1 b)
What is the NPV of the project? What is the projects IRR? Should the project be started?
1 c)
The firm was also approached by a military agency. They are proposing a government contract that will give the exclusive rights of the product to the military. They want to know at what price you can deliver the same quantities to the military and not sell any on the open market.
(A) The CEO asks for the contract bid-price (i.e. the price per unit) that will guarantee the project makes sense financially?
(B) The CEO also wants to know at what bid-price the project will bring in $2 million in present value to the company?
1 d)
Your boss has started thinking about financing. She just heard of an option called PIPE. She strongly suspects that it is not related to any plumbing, but is some sort of equity issuance. A fellow NASDAQ listed technology firm (Exela Technologies, Inc.) completed a pipe transaction earlier this year to raise $26.8 million and your boss looked at their official disclosure:
Giovann Project 2021 - Word Search References Design Layout Mailings Review View Help > A Aa A 21 -A 1. AaBb (A) Aa 1.A.1 A 1.A.1.1 1.1.1.1.1 Heading 1 Heading 2 Heading 3 Hlading 4 Headings CRC so Paragraph Styles Edit Questions are based on the following mini-case: CGX Transmitters is developing a 3 generation optical transmitter Somebody in your department worked on it and prepared a pro-forma analysis but then he left the firm last week. You suspect that he was fired because of a very bad analysis The CEO needs the capital budget on his desk Wednesday morning because she senses that one of our major customers might be interested in an exclusive contract. She knows that you are took FIN 303W Therefore you can complete and correct the budget that the finance group started and she asks the CFO to assign you. You collect the following information: - The project will last for four years. - The initial investment in equipment is $30 million. The firm will use a seven year MACRS schedule with the book value of the equipment going down from $30 million in year zero down to a book value of $9.372 million in year four (A colleague reminds you to not forget to use any remaining book value to reduce taxes on a potential re-sale at the end. He believes that the previous guy got canned for mistakes like this!) - The firm estimates that they will be able to sell the equipment and license the technology) to another firm for $12 million in year four. This sumber does not include any potential taxes on this transaction . The firm spent $12.5 million on R&D last year in order to research the technology behind the 3 generation transmitter. The generation transmitter is a technological break through relative to the older 2" generation unit - If you undertake this project, operating income before tax of the older technology 2 generation optical transmitter will decline by an annual amount of $1.2 million for each of the best four years. - The sccounting department will assign a $1 million administration costs per year for years one to four. You check with them, and they give you the following breakdown: $175,000 a year for accounting services to produce paperwork necessary to run the project and comply with federal and state regulations, od $825.000 towards the overall costs of maintaining the firm's headquarters. Even though the headquarters will not be involved with running this project, they argue that the cost of the headquarters has to be spread over all existing projects. income before tax - The tax rate is 214. The cost of capital is 12%. Assume that the firm's other projects yield a positive D Focus arch o i DELL 10 F12 Project 2021 - Word p Snanchi Giovanna E Design Layout References Mailings Review View Help prind Reple Select ing A A A - AO E... 21 1. AaBl (A) Aa 1.A.1 A 1.A.1.1 1.1.1. - - a. ==== Heading Heading 2 Heading) Hlading & Headings P Syles You check with them, and they give you the following breakdown: 5175,000 a year for accounting Services to produce paperwork becessary to nie the project and comply with federal and state regulations, $825.000 towards the overall costs of maintaining the firm's headquarters. Even though the beadquarters will not be involved with running this project, they argue that the cost of the headquarters has to be spread over all existing projects. The tax rate is 21% The cost of capital is 121. Aenume that the firm's other projects yield a positive income before tax The trainer is expected to sell for $11.50 with per unit variable cost of 83.70. The following sales Information is compiled by the marketing department in 5 million) and networking capital by the Bccounting departe Year Units Sold Networking capital 0 Onts Si 1 million 1 1.6 millions $2.7 million 12 24 millionen 544 million 3 22.9 million units $2.5 million . 0.5 millions 50 Hence, the sales in year or will be worth $18.40 million 1.6 millions11.50 sale price and the COGS in year one will be 55.92 million (16 million units 31.70 per unit cost). Similarly, you need to compute the dollar ment of sales and COGS in year two to year four ch O DELL 2 FS 5 F6 F7 F8 F9 F10 F11 F12 Pris 90 88000 AutoSave GD Suggested Project Pro Forma Analysis 2021 - Excel Search File Home Insert Draw Page Layout Formulas Data Review View Help Percentage Times New Roman 12 AA == 29 BIU-CA = == = == Paste $ % 948-28 Conditional Format as Formatting Table Styles Clipboard Font Alignment Number B20 fi SIRR(B16-F16) C D E M Questions 1&2 0 3 Sales 4 COGS 5 Depreciation 2. 27.600 8880 5.000 1 18.400 5.920 5.000 1.571 5.909 2.700 1.600 3. 3.350 10.730 5.000 3.700 13.920 2.300 -1.900 4 9.200 2.900 5.000 0.260 0930 0.000 -2.500 G H 1 Assumptions Price 11.50 Cost 3.70 0.21 Cost of Capital 12.0% 7 Cash Flow from operations Net Working Capital 1.100 1.100 30.000 12.500 10.839 4.400 1.700 10.000 -0.005 0.94% -0.175 -0.138 6.248 -0.965 0,175 -0,133 10.799 01:35 -0.175 -0.118 7218 -1.400 10 Capital lavestment 11 R&D Costs 12 Aller tax Savage Vale 13 Side effects on sen (her) 14 Headquarter Couter) 15 Compace costs 16 Nashow 17 18 19 NPU 20 IRUR 21 Suggestion Decision 22 23 24 23 26 NPV Analysis Ready -513.02 5.895 NO Type here to search O o R DELL Giovann Project 2021 - Word Search References Design Layout Mailings Review View Help > A Aa A 21 -A 1. AaBb (A) Aa 1.A.1 A 1.A.1.1 1.1.1.1.1 Heading 1 Heading 2 Heading 3 Hlading 4 Headings CRC so Paragraph Styles Edit Questions are based on the following mini-case: CGX Transmitters is developing a 3 generation optical transmitter Somebody in your department worked on it and prepared a pro-forma analysis but then he left the firm last week. You suspect that he was fired because of a very bad analysis The CEO needs the capital budget on his desk Wednesday morning because she senses that one of our major customers might be interested in an exclusive contract. She knows that you are took FIN 303W Therefore you can complete and correct the budget that the finance group started and she asks the CFO to assign you. You collect the following information: - The project will last for four years. - The initial investment in equipment is $30 million. The firm will use a seven year MACRS schedule with the book value of the equipment going down from $30 million in year zero down to a book value of $9.372 million in year four (A colleague reminds you to not forget to use any remaining book value to reduce taxes on a potential re-sale at the end. He believes that the previous guy got canned for mistakes like this!) - The firm estimates that they will be able to sell the equipment and license the technology) to another firm for $12 million in year four. This sumber does not include any potential taxes on this transaction . The firm spent $12.5 million on R&D last year in order to research the technology behind the 3 generation transmitter. The generation transmitter is a technological break through relative to the older 2" generation unit - If you undertake this project, operating income before tax of the older technology 2 generation optical transmitter will decline by an annual amount of $1.2 million for each of the best four years. - The sccounting department will assign a $1 million administration costs per year for years one to four. You check with them, and they give you the following breakdown: $175,000 a year for accounting services to produce paperwork necessary to run the project and comply with federal and state regulations, od $825.000 towards the overall costs of maintaining the firm's headquarters. Even though the headquarters will not be involved with running this project, they argue that the cost of the headquarters has to be spread over all existing projects. income before tax - The tax rate is 214. The cost of capital is 12%. Assume that the firm's other projects yield a positive D Focus arch o i DELL 10 F12 Project 2021 - Word p Snanchi Giovanna E Design Layout References Mailings Review View Help prind Reple Select ing A A A - AO E... 21 1. AaBl (A) Aa 1.A.1 A 1.A.1.1 1.1.1. - - a. ==== Heading Heading 2 Heading) Hlading & Headings P Syles You check with them, and they give you the following breakdown: 5175,000 a year for accounting Services to produce paperwork becessary to nie the project and comply with federal and state regulations, $825.000 towards the overall costs of maintaining the firm's headquarters. Even though the beadquarters will not be involved with running this project, they argue that the cost of the headquarters has to be spread over all existing projects. The tax rate is 21% The cost of capital is 121. Aenume that the firm's other projects yield a positive income before tax The trainer is expected to sell for $11.50 with per unit variable cost of 83.70. The following sales Information is compiled by the marketing department in 5 million) and networking capital by the Bccounting departe Year Units Sold Networking capital 0 Onts Si 1 million 1 1.6 millions $2.7 million 12 24 millionen 544 million 3 22.9 million units $2.5 million . 0.5 millions 50 Hence, the sales in year or will be worth $18.40 million 1.6 millions11.50 sale price and the COGS in year one will be 55.92 million (16 million units 31.70 per unit cost). Similarly, you need to compute the dollar ment of sales and COGS in year two to year four ch O DELL 2 FS 5 F6 F7 F8 F9 F10 F11 F12 Pris 90 88000 AutoSave GD Suggested Project Pro Forma Analysis 2021 - Excel Search File Home Insert Draw Page Layout Formulas Data Review View Help Percentage Times New Roman 12 AA == 29 BIU-CA = == = == Paste $ % 948-28 Conditional Format as Formatting Table Styles Clipboard Font Alignment Number B20 fi SIRR(B16-F16) C D E M Questions 1&2 0 3 Sales 4 COGS 5 Depreciation 2. 27.600 8880 5.000 1 18.400 5.920 5.000 1.571 5.909 2.700 1.600 3. 3.350 10.730 5.000 3.700 13.920 2.300 -1.900 4 9.200 2.900 5.000 0.260 0930 0.000 -2.500 G H 1 Assumptions Price 11.50 Cost 3.70 0.21 Cost of Capital 12.0% 7 Cash Flow from operations Net Working Capital 1.100 1.100 30.000 12.500 10.839 4.400 1.700 10.000 -0.005 0.94% -0.175 -0.138 6.248 -0.965 0,175 -0,133 10.799 01:35 -0.175 -0.118 7218 -1.400 10 Capital lavestment 11 R&D Costs 12 Aller tax Savage Vale 13 Side effects on sen (her) 14 Headquarter Couter) 15 Compace costs 16 Nashow 17 18 19 NPU 20 IRUR 21 Suggestion Decision 22 23 24 23 26 NPV Analysis Ready -513.02 5.895 NO Type here to search O o R DELL
