Q. How do you provide Dynamic Range in 'Data Source' of Pivot Tables?

Q. The following net cash flows relate to two projects:

NET CASH FLOWS (IN $ 1,000)
YEAR	0	1	2	3	4	5	6
PROJECT A	-60	20	20	20	20	20	20
PROJECT B	-72	45	22	20	13	13	13

1. Calculate the NPVs for each project, assuming 10% cost of capital.
2. Assuming that the two projects are independent, would you accept them if the cost of capital is 15%?
3. What is the IRR of each project?
4. Which of the two projects would you prefer if they are mutually exclusive, given a 15% discount rate?

Q. How much down payment should a person deposit at the beginning of 5 year duration for accumulating Rs. 100000 at the end. He also plans to deposit Rs. 10000 per year as a recurring deposit at the end of each year. The bank offers an 8% rate of interest compounded annually. Use FV to compute the accumulated amount at the end of the duration. Use Goal Seek for finding the answer with statical function and also with Goal seek.(create two worksheets)

Q. Johnson has two options

• The car dealer has offered Jonathan a deal on a car loan. He has told Jonathan that he can finance a new Honda and pay it off over the next five years with monthly payments of $290 and a 10% down payment. The sale price of the car is $17,500. Write an Excel formula to determine the annual interest rate Jonathan is being charged.
• Another car dealer has offered to sell Jonathan this same car for $17,500 with a 10% down payment. The dealer has offered him 4.9% financing (annual rate compounded monthly) to be paid back over the next four years in equal monthly installments. Write an Excel formula to determine the amount of the monthly car payment for this loan.
• Which offer is better for Johnson and Why?

Q4. An individual/company takes a loan from a lender on some terms and conditions. Prepare a loan/lease schedule statement by using the following instructions in spreadsheet software (e.g. MS Excel)

Q. Use the following sample format for the preparation of loan schedule statements.

Loan Details
Amount of Loan
Rate of Interest
Duration of Loan
Repayment Periodicity
Instalment

Loan Schedule
Period	Instalment	Principal Paid	Instalment Paid	Closing Balance

1. Assume loan amount (in Rs.) on your own
2. Assume the rate of interest (yearly) on your own
3. Assume the duration of the loan (in years) on your own
4. Make the periodicity of the loan such that one of the four possible periodicities (Monthly, Quarterly, Half Yearly or Yearly) can be chosen.
5. Instalment is computed dynamically by taking the values of loan amount, rate of interest, and periodicity into account.
6. The period column should compute period number (from 1 to N) dynamically, where N is the maximum period number. (You can also think of period number as instalment number)
7. Compute the opening balance, interest due, instalment, and closing balance for each period dynamically.
8. How much interest will be paid at the completion of the loan.
9. What principle you have to pay to the bank if you wish to close the loan halfway(.i.e) if initial plan was of 60 periods ,you want to close it in 30 periods);

Q. Consider the following scenario

• If Demand = 500,000 units per year, Carrying Cost= 30 percent of acquisition cost per unit per year, Ordering Cost= $5.95 per order, and acquisition cost = $2.50 per unit.

• What is the EOQ?
• What is the Total Cost at the EOQ?

• Do a scenario analysis (with report and pivot) for the following variations in the decision variable

• Demand= 350000 Carrying Cost =25% Ordering Cost=$5.85
• Demand= 320000 Carrying Cost =35% Ordering Cost=$6.05

• Do a cross tabulation what if analysis of Economic Order Quantity using Data tables for the following variations

• Carrying cost variation- 20%,25%,30%,35%,40%,455,50%
• Ordering Cost variation

$5.75,$5.80.$5.85,$5.90,$5.95.$6.00,$6.05,$6.10,$6.15,$ 6.20