Your family owns a cheese company (CheeseQueen) in Janesville, WI. Its products are sold in southeast Wisconsin. A couple of months ago, your parents were approached by S-Mart (a supermarket chain) with a request to supply cheese for its stores in Madison for the next four years. Your parents are considering building an additional production line to take this offer. They want to conduct a capital budgeting analysis to see whether it is acceptable. You, a student majoring in finance in UW Whitewater, were the first person they think of when they need such help. After you talked to them briefly over the phone, you decided to go back home to meet your parents so that you can collect all relevant information to get it started ASAP.
The information you collected are listed as follow:
To take on this contract, CheeseQueen needs to purchase new equipment at the cost of $460,000. The new equipment will be depreciated using a five-year MACRS schedule. The used equipment can be sold at 20% of its purchase price at the end of year 4.
The new production line would be placed on a farm currently rented by a small business. The rental income is $15,000/year (after tax). If your parents accept the project, they would have to terminate the rental contract. Your parents also have an agreement to sell this farm in five years to a real estate developer for $250,000 (after tax). It means that after this project is completed, your parents have to restore the farm to its original condition. The restoration will happen in year 5, and the cost is $80,000.
The contract calls for delivery of 55,000 pounds of cheese per year at $7 per pound for four years. To utilize the capacity of the new equipment, your parents plan to produce 60,000 pounds, 83,000 pounds, 85,000 pounds, and 74,000 pounds, respectively, over the next four years. The excess production is going to be sold at $9 per pound in retail market.
The estimated variable cost is $2.1 per pound, and the fixed cost is $200,000 per year.
CheeseQueen’s net working capital (NWC) investment is 10% of sales. The NWC will be built up at the beginning of each year. (i.e. NWC needed for year t = 10% of sales of year t)
CheeseQueen’s tax rate is 30%. Its cost of capital is 10% (discount rate). Assume a loss in any year will result in a tax credit.
You are asked to build an Excel model to calculate payback period (use user-defined function), net present value, and internal rate of return for the project. Upon completion of the analysis, would you recommend your parents to take the project?
Although your parents are comfortable with the numbers they estimated for the new project, they also understand that some of the variables will be different from what they expected. They are primarily concerned about the retail price/pound, the variable cost/pound, % of NWC of annual sale, and the cost of capital. Your parents would like you to carry out a scenario analysis with three scenarios: the normal, the best, and the worst, based on these four variables. They believe each variable could vary from 80% to 120% of the input value. For instance, the retail price/pound has an input value of $9. Your parents believe it could go as low as $9*80%=$7.2 and as high as $9*120%=$10.8. They would love to know the payback period, net present value, and internal rate of return for each scenario.
Please note: The operation of this project ends at end of year 4 and the machine is sold at end of year 4. Pay particular attention to what items should be placed in year 5 and what items should not be placed in year 5. Please also remember to save your file as a macro-enabled workbook to preserve your user-defined function.
Inputs
Tax rate: 30%
Variable Cost/pound: $ 2.10
Fixed Cost/year: $ 200,000.00
% of NWC of annual sale: 10%
Cost of machine $ 460,000.00
Restoration Cost of the Farm $ 80,000.00
% of Pretax salvage value of initial purchase price: 20%
Rental Income from the farm/year $ 15,000.00
Contract Price/pound $ 7.00
Retail price per pound: $ 9.00
cost of capital (discount rate) 10%
Sales Price of the Farm $ 250,000.00
Year 1 Year 2 Year 3 Year 4 Year 5
Total Pounds sold per year
Pounds sold through contract:
Contract Sales
Pounds sold in the retail market per year:
Retail sales
Total Revenue
Variable Cost:
Fixed Cost
Total Cost:
Year 1 Year 2 Year 3 Year 4 Year 5
Depreciation rate:
Depreciation
Accumulated depreciation
Adjusted basis of machine
Net working capital Year 1 Year 2 Year 3 Year 4 Year 5
Beginning NWC
End of year NWC
NWC cash flow
Pretax salvage value
Taxes on sale
Aftertax salvage value
Year 1 Year 2 Year 3 Year 4 Year 5
Sales revenue
Operating costs
Depreciation
Income before taxes
Taxes
Net income
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
EBIT
Depreciation
Taxes
Cash flow from operations
Equipment
Opportunity Cost (Farm)
Expense associated with Farm Restoration
Net working capital Cash Flows (change of NWC)
Total cash flow of project
NPV IRR Payback
We are a professional custom writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework.
Yes. We have posted over our previous orders to display our experience. Since we have done this question before, we can also do it for you. To make sure we do it perfectly, please fill our Order Form. Filling the order form correctly will assist our team in referencing, specifications and future communication.
1. Click on the “Place order tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
2. Fill in your paper’s requirements in the "PAPER INFORMATION" section and click “PRICE CALCULATION” at the bottom to calculate your order price.
3. Fill in your paper’s academic level, deadline and the required number of pages from the drop-down menus.
4. Click “FINAL STEP” to enter your registration details and get an account with us for record keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
5. From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.
Need this assignment or any other paper?
Click here and claim 25% off
Discount code SAVE25