Sample Exam Paper A Solutions PDF

Preview

Summary

IS102 Computer as an Analysis Tool AY2013-14 Term 1 Final Exam 27 November 2013 IS102 Computer as an Analysis Tool INSTRUCTIONS TO CANDIDATES      Time allowed for this examination paper is 2 hours. This examination paper contains TWO (2) questions and comprises SEVEN (7) pages inclusive of thi...

Description

IS102 Computer as an Analysis Tool

AY2013-14 Term 1 Final Exam 27 November 2013 IS102 Computer as an Analysis Tool

INSTRUCTIONS TO CANDIDATES  

  

Time allowed for this examination paper is 2 hours. This examination paper contains TWO (2) questions and comprises SEVEN (7) pages inclusive of this instruction sheet. You are required to answer ALL questions within the spaces provided in the examination paper. This is an OPEN BOOK examination. You are ALLOWED to use a Laptop but must DISABLE ALL NETWORK CONNECTIONS. There shall be NO ENQUIRY during the exam. State any assumptions if necessary when you answer the questions.

Student Name:

Section:

___________________________________

________________

Instructor Name:

Seat No

Exam Venue: _________________________

_______

________________

_________

Marks Awarded

2013-14T1

Question 1

10

Question 2

15

TOTAL

25

1

IS102 Computer as an Analysis Tool

Question 1 (10 marks) 3 competing taxi service companies proposed 3 different fare schemes as shown in TABLE 1-1. Johnny plans to take a taxi from home to work daily and wants to find out which taxi company should he patronise. He estimates the distance to travel is 16.4km. Help him decide which taxi company is the best option for him by modeling the computation in TABLE 1-2 below. Note:  Starting fare – this is charged when the passenger first boards the taxi  Rate 1 – this is charged at the start of 1 km and will be used for all block distances that are started within the first 10 km  Rate 2 – this is charged after the first 10 km and when Rate 1 charge is completed for all its block distances started

a)

Fill in the formulas for the cells indicated below. Note that the formulas must work when using the fill function for all 3 companies . (5 marks)

Cell

Formula

D14 (2 marks)

=IF($D$7>10,ROUNDUP((9/D13),0)*D12,ROUNDUP(($D$7-1)/D13,0)*D12)

D18 (2 marks)

=IF($D$7>(1+ROUNDUP(9/D13,0)*D13), ROUNDUP(($D$7-1-ROUNDUP(9/D13,0)*D13)/D17,0)*D16,0)

D19 (1 mark)

=SUM(D10,D14,D18)

2013-14T1

2

IS102 Computer as an Analysis Tool

b)

Fill in the numerical values for cells D14:F14, D18:F18 and D19:F19 in TABLE 1-2 above. (3 marks)

c)

Johnny notices that the differences among the total fares charged by the three taxi companies are not much. In fact, at a particular travel distance, there might be 2 or even 3 of the companies charging the same total fare. Describe how you would modify the model in TABLE 1-2 to determine this travel distance. Note that you do not need to determine the actual answer of this travel distance. (2 marks)

Solution Compute the absolute differences in the total fare between Company 1 and Company 2, Company 2 and Company 3, Company 3 and Company 1. With the differences computed as Diff1, Diff2 and Diff 3, determine the minimum of all these differences to be Diff-Min. Using Goal Seek or Solver, set the value of Diff-Min = 0 by changing the travel distance value.

2013-14T1

3

IS102 Computer as an Analysis Tool

Question 2 (15 marks) Every year, your company reviews the performance of its production machines so that any replacements can be delivered before the end of the financial year. The cost of replacing each machine is $200,000 (price of new machine). There is a market for used machines that are less than 6 years old, which could be considered. The average annual running costs are expected to increase as machine gets older (1st year running cost is $20,000). Resale value of a machine and its running cost are given in Table 2-1. Note: Capital cost = Price of new machine – Resale value of old machine Cumulative Running cost = Cumulative cost of maintenance & operations over the machine's life

2013-14T1

4

IS102 Computer as an Analysis Tool

a)

Draw an influence diagram to analyze the current situation that would help you propose a cost effective procurement strategy for the machines. (2 marks)

Solution

Purchase value Capital cost Resale value Total cost Cumulative running cost

Average running cost a year

Age of replacement

b)

Based on the resale values and running costs given in Table 2-1, suggest suitable mathematical models to represent their trends. (2 marks)

Solution Plot a graph of these values and use the add trendline function to determine the most suitable function Resale value model: y=298365e-0.4x Running cost: y=1000x + 18000

2013-14T1

5

IS102 Computer as an Analysis Tool

c)

Create a table as shown in Table 2-2 to compute the average running cost of machines per year. Fill in the formulas listed below. (2 marks)

Solution Cell

Formula =$C$4-H4

H9 =SUM($D$5:H5) H10 =SUM(H9:H10) H11 =H11/H8 H12

What is the best age to replace the machines assuming that your company will buy new machines? (1 mark)

Solution Year 5, lowest average cost a year

d)

In some circumstances, it is better to buy second hand machines as resale value declines rapidly over the initial years. Create a table as shown in Table 2-3 to compute the average cost per year taking into account the year the machine was bought and the year the machine was sold. For example, cell G18 represents buying a 1 year old machine and selling it after 3 years, in year 4. (Note that left lower triangular matrix is infeasible) Fill in the formula used listed below.

(3 marks)

Solution Cell

Formula

H17

=(LOOKUP($C17,$C$3:$H$3,$C$4:$H$4)LOOKUP(H$16,$C$3:$H$3,$C$4:$H$4)+SUM($D$5:H$5))/(H$16-$C17)

2013-14T1

6

IS102 Computer as an Analysis Tool

What is the most cost effective procurement strategy for the machines?

(1 mark)

Solution Buying 4 years old machines and selling them a year later.

e)

If you are facing an uncertain market condition which causes the prices of machines to drop between 5% to 10% each year following a Uniform Distribution, with respect to previous year’s value (assume the running costs remain unchanged), describe how would you modify the resale value model to address this condition. Evaluate your modified model to determine an average running cost per year and comment on how it will impact your procurement strategy in buying machines. (4 marks)

Solution Create a varying interest rate for resale value between 5-10% (1 mark) Simulate for at least 100 replications using data table (1 mark) Compute average running cost around $31k to $32K (1 mark) No effective strategy due to fluctuating average running cost. (1 mark)

__________________________________________________________________________________ END OF PAPER

2013-14T1

7...