Hello,

Need assistance with formulas in an excel file which allows me to solve 4-7.

Thanks!

Mini Case #1: Ca pita] Budgeting at Rio Negro, Inc. Assignment Overview Rio Negro. Inc. {RNI} is in the business ot'transporting cargo between ports in Calilomia and Washington. [ls l'leet includes a small dry-urge- vesscl. the Adar-razor. The item is 25 years old and badly in need ot'an ovcrhaul. II is Marci-I Ellie. and Michael John, the nance director. hasjusl been presented ltvith a proposal that would require the one-time esp-encloses shown below in Table I. [t'the proposal is accepted. these expenditures will be made in the next few days. Mr. John believes that all these outlays could be depreciated for tart purposes in the seven-year MACRS class (see Table 2 below for rates}. Overhaul oflhe Adar-acre; will begin as soon as the expenditures in Tabiel are made. but the vessel will be out of service for several months. The overhauled vessel would resume commercial service in one year. RNl's ehiel'engineer's estimates of the post-overhaul operating costs are in Table 3. In addition to the overhaul described above. the chiel'cnginoer' suggests installation of'a brand- new engine and control systern. Installation ofthis new engine would cost an extra 5600.000 {This additional outlay would also qualify for tax depreciation in the seven-year mess class}. However. it'the additional equipment is installed. it would result in reduced fuel. labor, and maintenance costs as shown in Table -'l. The operating cost estimates in Tables 3 and 4 are oirrent for March 20th. However. these costs will increase with ination, which is forecasted at l25% a year. Depreciation artd operating oosts attributable to the overhaul of them will begin one year alter the vessel is put back into conunereial service. The revenues from operating the vessel will be the same for both types of overhaul. Even with the proposed overhaul. the W cannot continue tor-ever. After the overhaul. its remaining useful life is estimated to be only 12 years. Its salvage value when nally taken out ol' service will be trivial. Thus. Mr. John feels it is unwise to proceed without also considering the purchase oi'a new vessel. Racettc tit Sons (MS). a Colorado shipyard, has approached RNI with a design incorporating a Kort nozzle, extensively automated navigation and power control systems. and much more comfortable acconuncdaticns for the crew. ass is offering the new vessel for a fixed price of $3,000,000, payable halfirnmediately and hall'on delivery in one year. Esli mated annual operating costs of the new vessel are in Table 5. The operating cost esli males in the table are eun'enl For March 2015. but will increase with ination. The crew would require additional training to handle the new vesselis more complex and sophisticated equipment. Training would result in a one-time cost of $50,000 payable one year following delivery ofthe new vessel. This east is tax deductible. The estimated operating casts for the new vessel assume that it would be operated in the same way as the Mucus. However, the new vessel will be able to handle a larger load on some rotncs, which is expected to generate additional revenues of approximately $l?$.0'00 per year in the rst year of operation. These revenues are expected to grow at the rate ot'intlation. Revenues and operating costs from the new vessel will begin one year atter it is delivered. The new vessel is estimated to have a useful service life ot'20 years. but it will be depreciated for tax purposes according to the "Ti-year MACRS schedule. The new vessel is not expected to have any.r resale value at the end of its 20-year useful life. All revenues and costs [including depreciation} associated with the new vessel will bogin one year alter it is delivered. The Airfares-or is carried on RNI's books at a book value ol'only $100,000 and the book value oflhe spare parts is $40000. 11ie titan-racer could probably be sold now "as is." together with its ettenSive inventory of spare parts. for $200,000. Mr. John stepped out on the Foredeck ofthe Mucus as she drugged down the Cook Inlet. "A nlsty old tub," he muttered. "but she's never let us down. ['0 bet we could keep her going until next year while Raeette d: Sons are building her replacement. We could use up the spare parts ("0.000} to keep her going and we should even be able to sell or scrap her for book value when her replacement arrives." RN! evaluates capital investments ohis type Ming :1 3.5% mat of capital. (This is a nominal, nnt real, rate.) RIETI'F. lax rate is 35%. Table 1: vuhaul Exgmdiiurea Overhaul engine and generators $311-$000 RepJace radar and other eicclron'lc equipment 'FSDHL') Repairs 1c hull and superstrmaure SWINE Painting. and other repairs 95,000 820,000 Year 2 24.49 Year 3 I149 Year 4 I149 Year 5 8.93 Year 6 8.93 Year 1 3.93 Year 3 4.45 l-'uel "50.000 Labor and benets 43:1003 Maintenance 14 LIME] Olher M 51.1mm Fuel SWELHHU Labor and benefits 405.000 Maintenance JIJSJ'HIU {llher ] 10.00:: I {32E} III" Table 5: Banting Cam 1le Vessel Fuel Labor and henelils SSUIHJU Maintenance 'P' Ulher HIS INJU BHSJJ'HU Guidelines for Case Analysis Inefoomiugaidr mm'nsthdforefs "trim 1r'ou may use internet sources, hooks, all posted materials (including Discussion Board Claim, and your notes. Anymerssm Waited uniesrm m a mum efumdsulie integrity. This includes face-lo- tace or electronic correspondence concerning the specic details ofthe ease with any other person that is not: a member of your assigned group. whether or not they have current or past afliation with Texas were Corpus Christi University. The case is due on the date indicated on the course schedule. Late papers may be accepted with a reasonable excuse. but will be amssed a 20% grade reduction penalty. Cases should be typed ll I2- point font. double-spaced. with a minimum of I knelt margins. The case report should he written according to the following format: I. Introduction 2. Analysis 3. Conclusion The inu'oducu'on sets the stage for the work to tollow and should consist ot'a short paragraph ofthe key problems} or issueis} that your analysis addresses. The analysis will ounsti Lute the bulk ofthe written presentation and will be a. direct response to the questions below. Use clear. concise. and complete sentences. Do not use bullet points or numbered paragraphs. The conclusion should be a silent paragraph that summarizes the key points or" the analysis. Your report shoqu not exceed ve pages ofdomblcgace test with I incl margins It the sides. top, and bottom of the page. This does not include exhibits ofyour computations. You may suhnntmsEmsISjrmM "Wwwmmm MW} rg'mdinstaquourrqmrt. Your analysis of"Rio Negro, Inc." should consist of answers to the questions below. Do mm the gum Wmtqusmt Instead. write a 'hriel'intrltsduletoryI statement that summarizes the question before you proceed with your analysis. 4. Calculate the present value ol'the proposed overhaul ofthe Aria-mas, with and without the new engine and control system. Should RNI do the basic overhaul or the expanded overhaul with the new engine and control system? For the moment. ignore the option to purchase a new vessel (you will evaluate that option in question 2 below]. Finite awpmagrmingivgmmmdsm'b' Wmmonzgmafmwmmu; show datailad'congpmions mmmmmmmoa 1'. 5. Calculate the present value of buying and operating the new vessel. What. il'any, additional information would be useliul to you in your analysis? Finite awpmagrmingivgmmmdsm'b' Wmmonzgmafmwmmu; show datailad'congpmions mmmmmmmoa 2. 6. Calculate the equivalent annual costs of {a} overhauling and operating die Manors: for 12 more years {with and without the new engine and control system} and {to} buying and operating the proposed replacement vessel for 20 years. You should use the real discount rate for this analysis. Based on your answer. what should RNI do? Hideous EMW givbggyowmmctazrhr apiebringwrarmirgmoamuicm;mw dsfoaa'caagmzhbm EulourEmstspraadirhsef labeledthibfti Why is the equivalent annual cost method potentially usel! in. decision making in this case? Why would you use the ma! discount rat: to cumpulc the EAC'? What problemls} do you see with using the equivalmi annual cost method to waluatc RNI's options? the to mamm. Your report is intended for Illa senior management of Rio Negro. Inc.. so he sure that you write in a professional style thlt is Easy to follow