View the step-by-step solution to:

FINC6601 Module 04 Assignment CAPITAL BUDGETING Due: E-submission: on Tuesday, October 27, 2015. Type of Assignment: Deliverables: An MSWord document...

I have a project due on NPV for a Finance class.  The NPV is supposed to be a positive value.  Mine keeps coming up negative and I can't figure out why.  Would you be able to look at the scenario and my spreadsheet and tell me where I went wrong?

FINC 6601 Module 04 Assignment Page 1 FINC6601 Module 04 Assignment CAPITAL BUDGETING Due : E-submission: 6:00 p.m. on Tuesday, October 27, 2015. Type of Assignment : This is an individual assignment but you can seek assistance of others provided that you do the computations yourself and write up your own work. Deliverables : 1. An MSWord document or MSExcel worksheet containing the answers to the questions below. (Probably an MSExcel worksheet – which is probably the most efficient way to do this project.) Your document should include your name in the text as well as a neat and professional set of answers. 2. Use the naming convention “Last name_first initial_MOD04.xlsx.” For example, if I were turning in this assignment it would have the name “Highfield_R_MOD04.xlsx”. Post your document on Blackboard by the due date. Purpose : To provide specific practice in applying the fundamentals of capital budgeting to a corporate investment decision in a realistic setting. To complete the problem successfully you need to accurately estimate the cash flows for the project from estimates of sales and costs and compute several standard capital budgeting criteria including NPV, payback period, discounted payback period, IRR and Profitability Index. Please write up the case in a professional manner. If you do the computations in a spreadsheet (recommended), please include this spreadsheet in your submission, named according to the convention given above. If you do the computations by hand, please include an appendix to your word document showing the steps you took to get to your answers. GWYNT INDUSTRIES, INC. GWYNT Industries is a specialized manufacturing firm. It has been looking for new opportunities to expand its profitability as a manufacturer and has recently been approached to be the sole source contract supplier to a major computer manufacturer of an important computer component (cooling fan). The contract with the computer maker would be guaranteed for four years with no expectation of contract extension. A significant reason for this offer is a new, innovative and particularly reliable manufacturing approach for this component that GWYNT developed after extensive research and development. GWYNT has also researched the market for independent sales of the same component to parts distributors for specialty makers and hobbyists. GWYNT Industries must now decide whether to make the investment necessary to go into the computer cooling fan business. The contract with the major maker would begin this year, and the specialty/hobby market could also begin this year. They could enter either market alone or both markets simultaneously. If the contract offer is accepted, GWYNT must immediately abandon an existing product line that would have generated incremental after-tax cash flows of $500,000 in each of the next two years (i.e., this existing product line has two more years remaining before the product would be obsolete anyway.) If it leaves this existing market, the opportunity is effectively gone forever. Further, the R&D costs noted above to develop the process have totaled about $ 150,000 , and the m a rk e ting research on the specialty/hobby market co s t $50,000. The CFO of GWYNT Industries , h as a s k e d you, as a financial analyst, to e va lua t e this new opportunity p ro j e c t a nd to pr ov id e a r e comme nd a ti o n o n w he th er to go ahe a d w i t h th e inv es tment that would allow them to enter one or both markets . To simplify the analysis, assume that the initial in v e s tment that w il l occur
Background image of page 1
FINC 6601 Module 04 Assignment Page 2 immediately (i.e., production facilities will immediately be switched to the new product) and all other c as h f l o ws wi ll occur a t y e a r - e nd . GWYNT Industries must initi a ll y i n v est $1.6 million to purchase new production e quipm e nt for the new product. Some of this cost, $390,000 can be recouped at the end of the project, 4 years from now, by selling the production equipment. As noted above, GWYNT Industries can potentially s ell th e cooling fans in two di s tinct m ar ket s (the markets are independent, i.e., GWYNT will be able to see in both markets): 1. Under contract : The contract with the major computer maker calls for 45,000 cooling fan units in the first year at $40 per unit. Thereafter, the number of units delivered will increase by 2% per year and the price received by GWYNT will increase by 2% percent above the inflation rate each year until the end of the four-year contract. 2. Th e specialty/hobbyist m ar k e t : Thi s retail m ar ket has hi g h e r ma r gi ns ; GWYNT e x p e ct s t o receive a unit price of $60 in this market in the first year. The market research predicts that the size of this entire market will be 90,000 units in the first year and that GWYNT can obtain a 15% market share of the market. It is expected that unit sales in this entire market will grow by 4% per year, and that GWYNT will be able to maintain its 15% market share and increase its price by 2% above the inflation rate each year. The variable c os t to produc e each unit is $18 in the first year . Since the product in each market is essentially identical, this unit cost is the same in each market. However it is projected that this cost will increase each year by 3% p e rc e nt abo v e th e inflati o n r a te each year because of efficiency losses as the production equipment ages. Further, GWYNT will incur general, marketing and administrative costs of $350,000 th e first year. These period costs are ex pected t o incr e a s e a t the in f l a tion rat e in the s ub se quent y e a r s. These general marketing and administrative costs will cover their involvement in either market or both markets. GWYNT’s marginal corpor a t e ta x r ate is 34% perc e nt . Annu a l in f lation i s e x p e c t ed to remain con s tant a t 1.5% . The comp a n y use s a 12% di s c o unt r a te t o evaluate new investment deci s i o n s of this kind . The a ppr o pri a te depr e ci a t i on s ch edule for t he e qui p ment is th e sev en-ye ar MACRS depreciation schedule. This allows the following depreciation schedule for the four years of the project: First year 14.3% Second year 24.5% Third year 17.5% Fourth year 12.5% The immediate initi a l working capital requirement i s $200,000. Ther ea ft e r , the net w orking c apital re quirement s w ill be 11% p ercent of s ale s (i.e., required at the end of each year except the last, when working capital is recovered.) You are asked to provide the following items (and the analysis that leads to them): NPV , IRR, pa y back p e riod , discounted payback period, a nd profitability index for this project . A recommendation about whether Gwynt should proceed with this project based on your decision rules. Assuming that the contract market sales are guaranteed but the Specialty/Hobbyist market sales are not, provide an estimate of the minimum share that Gwynt must achieve in the Specialty/Hobbyist market for this overall project to have a positive net present value.
Background image of page 2
Show entire document
GWYNT Industries Inc Project Rice_L_MOD4 Worksheet for Cash Flows of the GWYNT Industries Inc. ($ in ??) Opera±ng Revenues and Costs of GWYNT Industries Inc Contract Opera±ons Year 0 Year 1 Year 2 Year 3 Year 4 Year Quan±ty Sold Price Cumula±ve Price Calcula±ons Cumula±ve Investments: 1 45000 40 1800000 18 810000 45000 45000 40 40 18 (1) Produc±on Equipment ($1,600,000) * 2 45900 41.40 1900260 18.81 863379 900 45900 1.4 41.4 0.81 (2) Accumulated deprecia±on 14.30 38.80 56.30 68.80 3 46818 42.85 2006151.3 19.66 920441.88 918 46818 1.449 42.849 0.84645 $1,116,000 $753,000 $511,000.00 $390,000.00 ???? 4 47754.36 44.35 2117905.87 20.54 980874.554 936.36 47754.36 1.499715 44.348715 0.88454025 (4) Opportunity Cost ($1,000,000) Quan±ty sold rises 2 % per year. Prices rise 3.5% per year. Unit costs rise 4.5% per year. (5) Net Working Capital (end of year) $200,000 $198,000 $209,028.60 $220,676.64 $232,969.65 Reported prices and costs (Columns 3 and 5) are rounded to two digits a²er the decimal. (6) Change in Working Capital -$200,000 -$11,028.60 -$11,648.04 ??????? Sales revenues and opera±ng costs (Columns 4 and 6) are calculated using exact, i.e. nonrounded, prices and costs. (7) Total cash ³ow of investment ($2,800,000) ($11,029) ($11,648.04) ??? [(1)+(4)+(6)] Income: Opera±ng Revenues and Costs of GWYNT Industries Inc (8) Sales revenues $1,800,000 $1,900,260 $2,006,151.30 $2,117,905.90 Specialty/Hobby Market (9) Opera±ng Costs (810,000) (863,379) (920,441.88) (980,874.55) Year Quan±ty Sold Price Cumula±ve Price Calcula±ons Cumula±ve (10) Deprecia±on 1 (11) Income before taxes 2 3 (12) Tax at 34 percent 4 (13) Net Income Based on a total market share of 90000 units and a market share of 15%. Equipment Deprecia±on Year 1 $484,000.00 2 $363,000.00 3 $242,000.00 4 $121,000.00 Sales Revenues Cost per Unit Opera±ng Costs Quan±ty Sold Calcula±ons Cost per unit Calcula±ons (3) Adjusted basis of equipment a²er deprecia±on (end of year) Sales Revenues Cost per Unit Opera±ng Costs Quan±ty Sold Calcula±ons Cost per unit Calcula±ons Amount of deprecia±on
Background image of page 1
Cumulatve 18 18.81 19.65645 20.54099025 Cumulatve
Background image of page 2
Show entire document
Sign up to view the entire interaction

Top Answer

Kindly find the answers in the attached file... View the full answer

Capital Budgeting copy.xlsx

Calculation of depreciation
4 cost
12.50% total
Profit/loss on disposal
cost Depreciation

Sign up to view the full answer

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.


Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask a homework question - tutors are online