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:

 

1. 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. 

 

2. 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. 

 

3. 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. 

 

4. The estimated variable cost is $2.1 per pound, and the fixed cost is $200,000 per year. 

 

5. 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)

 

6. 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

Answer rating: 100% (QA)

Solution Lets continue with the timeline Year 2 Sales revenue of 55000 x 11 10 NWC 6050 Operating co... View the full answer