IS102 Mid Term Test Sample Paper 1 Open Book PDF

Preview

Summary

IS102 Mid-Term Test SampleName: ________________________________ UserID: _______________Before you start, please write clearly your name and SMU user ID.Time : 1 hours Requirements : Open book, Computer with Excel. Restrictions : No communications of any kind (i. mobile phone, email, chat, etc.).Thi...

Description

IS102 Mid-Term Test Name: ________________________________

Sample1 UserID:_______________

Before you start, please write clearly your name and SMU user ID. Time: 1.5 hours Requirements: Open book, Computer with Excel. Restrictions: No communications of any kind (i.e. mobile phone, email, chat, etc.).

This test comprises question 1 (8 marks), question 2 (9 marks) and question 3 (8 marks plus 4 extra bonus marks), giving a total of 25 and 4 bonus marks. The maximum total you can get from this test is 25 marks, corresponding to the 25% for the course total.

Do not turn over this page until the instructor gives the instruction to do so. Write your answers in the space provided below each question.

2006Term2/LTY

1

Question 1 (8 marks) Sam needs to rent a car for his upcoming trip. CheapWheels charges $20.25 per day plus $.14 a mile. Easy Rider charges $18.25 a day plus $.22 a mile. Sam plans to do a lot of driving on his 3-day trip. a) Sketch an Excel model to support your analysis.

(3 marks)

Formulas for the following key cells are:

b) Which company should Sam go with? Explain your choice.

(2 marks)

c) At what mileage will Sam be indifferent to either rental company? If the trip is shorter than 3 days, will the indifferent mileage be longer or shorter? (2 marks)

d) Does the difference in cost go up or down as mileage increases?

2006Term2/LTY

(1 mark)

2

Question 2 (9 marks) Alphonso borrowed $50,000 from Timmy and promised to return the money in monthly installments over 2 years. Each month, the repayment amount to Timmy should rightly be about equal for ease of tracking. Since Alphonso does not have a steady income and is not sure he can meet all the planned (equal) payments, he asked to structure the payments such that he will try to pay the required amount as far as possible. If he is short of money, he must pay at least 70% of the planned payment, and if he has excess money, he can pay more than the planned payment. The planned monthly payment amount for the remaining loan balance is always recomputed each month as if it is a fresh loan for the remaining number of months. The agreed interest rate for the loan is 4% per annum. a) Sketch an Excel model to support their analysis. Include columns to show the monthly interest payment, loan repayment, loan outstanding and planned and actual repayments. (3 marks)

Formulas for the following key cells are:

2006Term2/LTY

3

b) Assume first payment is due at the beginning of the next month.

(3 marks)

The planned monthly amount Alphonso needs to repay is $___________. The minimum amount he has to repay is $ _____________.

c) Alphonso did so well in his business that he repaid Timmy $3,000 and $2,600 in months 1 and 2 respectively. (3 marks) The planned monthly payment is now $____________. The minimum amount he has to repay is $ _____________.

2006Term2/LTY

4

Question 3 (8 marks + 4 extra bonus marks) High quality Arabica coffee plants grow practically wild on the upper slopes of the foothills behind a village in East Timor. International prices for green Arabica coffee beans have recently plummeted, moving from over $3.50 per kg in 1998 to about $0.50 per kg in 2006. As a result, the villagers have abandoned the coffee plantation to focus on subsistence farming to feed their families. The village chief (“chafe”) is deciding if he should marshal the villagers to return to the plantation again. The cost of hiring villagers to maintain the coffee plantation is about $ 5,000 per month. With better upkeep, the plantation's yield is expected to improve. The expected output yields are 40, 60, 69, 73 and 74 tonne for 0, 1, 2, 3 and 4 months of upkeep respectively. One tonne (metric ton) weights 1,000 kg. In addition, harvesting the coffee for sale should the chafe choose to do so would cost $0.70 per kg. Other than wages, all money spent and profit earned go into the village cooperative, co-owned by every family in the village. a) Using the table below as a starting point, create a model to help the chafe make his decisions. (3 marks)

Formulas for the key cells are as follows: Maintenance cost (/mth) Harvest cost (/kg) Yield (tonne) Profit (no harvest) Profit (with harvest)

2006Term2/LTY

D4 E4 D7:I7



D10:D22

______________________________________

E10

______________________________________

5

The major decisions to make are

b) If the coffee price this coming season is expected to be $0.75 per kg, what should he do? What if the coffee price per kg goes up to $0.80, $0.85, $0.90, $0.95 or even $1.00? Explain how the profit changes with changes in the price. (3 marks)

2006Term2/LTY

6

c) It can be assumed that coffee price behaves like a random walk: the magnitude of annual percentage change has a stationary distribution and the coffee price is equally likely to increase or decrease. Using the table below, sketch out the model of how you would now simulate the 2007 coffee price. (2 marks)

Formulas for the key cells are as follows: This Year’s Price

C49

Change Direction (+1 or -1)

E49

_____________________________________

Next Year’s Projected Price

F49

_____________________________________

Price %Change magnitude

D53

____________________________

Simulated 2007 Price

B64

____________________________

d) From the simulation,

(Extra bonus: 4 marks)

coffee price for 2007 is expected to be _______________ and its standard deviation is ________________. Based on this simulation, the recommended actions for 2007 are

2006Term2/LTY

7...