Assignment:
Rudy Flyer Corporation is a large manufacturer of fresh and frozen desserts in the Midwest. Rudy Flyer uses a material known as PCN for insulating its packaging to maintain product freshness.
Steve Benson, the Chief Operating Officer, believes that the company could save money by buying packaging from an outside manufacturer rather than manufacturing packaging materials internally. He requested a report showing Rudy Flyer Corporation's current package manufacturing costs from the Accounting Department. After conducting some of his own research, he contacted Freeze Me, Inc., and received a proposal for insulated packaging materials. Freeze Mc, Inc. quoted a price of $500,000 for 5,000 packages each year for five years. The cost would increase in the same ratio if there was any excess demand for packaging. The analysis of Rudy Flyer's annual packaging costs from the Accounting Department has the following information:
|
Direct material (PCN)
|
$90,000
|
|
Other direct materials
|
150,000
|
|
Direct labor
|
180,000
|
|
Packaging manager's salary
|
90,000
|
|
Depreciation of machinery
|
70,000
|
|
Packaging Department overhead
|
78,000
|
|
Rent expense
|
15,000
|
|
General administrative overhead allocation
|
24,000
|
Other information is as follows:
• Two years ago, the Rudy Flyer purchased the machine used in production for $500,000. At the time of the purchase, the machine had a useful life of seven years and a terminal disposal value of $10,000. Its current salvage value as of today is $240,000.
• Rudy Flyer uses 20 tons of PCN each year. The company purchased 100 tons of PCN three years ago for $450,000. The price of PCN is $4,800 per ton today. If Rudy Flyer were to stop manufacturing packaging materials, it could dispose of its PCN supply for a net amount of $3,600 per ton.
• Rudy Flyer has no other materials inventories.
• Rent expense represents an allocation based on the packaging department's percentage of total square footage in the production facility. Rudy Flyer is also presently renting a separate warehouse for $27,000; this space would not be needed if the production of packaging materials is outsourced to Freeze Mc, Inc.
• The packaging department's overhead costs would be avoided if it no longer manufactures packaging materials. The department manager would be moved to a different position within the company.
• Rudy Flyer has a tax rate of 40% and a required rate of return 0112%.
Required
1. Assume that you are on the Accounting staff of Rudy Flyer Corporation. Prepare a net present value (NPV) analysis that would be presented to the CFO of the company. The CFO will want to see the details of your analysis so he can determine if your analysis is correct (i.e., sources of cash inflows and outflows, taxes, depreciation, etc.). To check your numbers, calculate the net present value in two ways: first, use the NPV function in Excel, and second, use the appropriate factors from the present value tables. Your numbers will be slightly off due to rounding but the two amounts should be within approximately $100 of each other.
2. Now prepare a net present value (NPV) analysis that would be presented to the CEO of the company. She has a non-business background and does not like to be bothered with too many details. She always says, "Just tell me what time it is, not how to make a watch!" In other words, communicate your decision to her through a spreadsheet that contains important information but not unnecessary details. As one example, the CEO does not need to know how you discounted after-tax cash flows. She should be able to see the assumptions that go into the analysis in a way that is easy to follow and that makes the recommended decision clear. Set your print area to print the analysis for the CEO.
Note: Since you are preparing two different analyses, you should have two separate output analysis areas in your file (either in different tabs or separate and segregated areas in the same tab). Both of these analysis areas should be protected and locked so that changes cannot be made to the formulas (see Guidelines for Spreadsheets above). The assumptions/input area should be unlocked so that users can make changes to assumptions and input amounts if the need arises.
Considerations for this assignment:
• You may set up your spreadsheet however you want, but it is likely easiest to think about the problem if you schedule out the cash flows and calculate NPV for the Make option and then separately schedule out the cash flows for the Buy option.
• Your net present value will be negative for both decision alternatives because you arc not given cash inflows for product sales (these revenues do not meet the definition of relevance).
• Do not forget that any time an asset is sold, we must calculate the gain or loss on the sale. Both gains and losses have tax implications.
• Only include relevant items in your analyses. One way to check to make sure you have omitted irrelevant items is to see if you have any line items that arc identical (i.e., have the same purpose and the same amounts each year) under both the Make option and Buy option.
• You may have some cash inflows or outflows that occur on the first day. Be sure to keep these separate from any cash inflows or outflows that occur in future periods or else they will not be handled correctly when discounting to the present value.