Sample Exam Paper B PDF

Preview

Summary

AY2013 - 14 TERM 1 SAMPLE TEST PAPER THREE Nov 2013 IS102 COMPUTER AS ANALYSIS TOOL INSTRUCTIONS TO CANDIDATES 1 The time allowed for this examination paper is 2 hours 2 This examination paper contains a total of two questions. Total mark is 25 and mark distribution: Question 1 – 10 marks Question 2...

Description

AY2013 - 14 TERM 1 SAMPLE TEST PAPER THREE Nov 2013 IS102 COMPUTER AS ANALYSIS TOOL INSTRUCTIONS TO CANDIDATES

1

The time allowed for this examination paper is 2 hours

2

This examination paper contains a total of two questions. Total mark is 25 and mark distribution: x

Question 1 – 10 marks

x

Question 2 – 15 marks

3

You are required to answer ALL questions and this is an OPEN BOOK examination.

4

Restrictions: no communications (email, chat, internet, mobile phone etc.) of any kind. Write your answers in the space provided below each question. This paper will be collected at the end of the test.

Student Name:

NRIC:

Section: Instructor Name: Marks Question 1

10

Question 2

15

TOTAL

25

Awarded

Question 1 (10 marks) When shipping goods between an origin and a destination, the price to charge follows a concave curve to reflect the incremental discounts due to economies of scale. In other words, it is cheaper to ship larger quantities than smaller quantities, in terms of per unit price. For simplicity, the pricing used for shipping between origin A and destination B, is approximated to a three-segment piecewise linear curve as shown in FIGURE 1-1 below.

FIGURE 1-1: Shipping Price Curve

Adam, Bryan and Charles wish to ship some goods from origin A to destination B. The quantity of goods shipped is shown in TABLE 1-1 below.

1 2 3 4

A B C D Customer’s Name Quantity to Ship Unit Price Shipping Price Adam 95 Bryan 134 Charles 287 TABLE 1-1: Customer, Shipping Quantity, Unit Price and Shipping Price

a)

Use the TABLE 1-2 below to design the Lookup table to be used to compute the shipping price for each customer. Fill up TABLE 1-2 according to your design of the Lookup table. This table will be used together with the VLOOKUP( ) function in Excel. (6 marks)

A Range

7 8 9 10

B Unit Price

C Base Price

TABLE 1-2: Design of Lookup Table to Compute Shipping Price

b)

Prepare your Excel model according to TABLE 1-1 and write the numerical values for the unit price and shipping price for each customer in cells C2 to D4 in TABLE 1-1. (2 marks)

c)

Enter the formulas for the following cells in TABLE 1-1 (2 marks, 1 mark each): Cell

C2 =

Cell

D2 =

Question 2 (15 marks) Janet takes a loan of $4000 to buy a notebook. The bank will loan her at 3.5% p.a. over a repayment period of 12 months. Assume that monthly payment is due at the

of each

month. a)

Prepare the Excel model as shown in TABLE 2-1 below and write the values in TABLE 2-1, for cells B1 to B4. (4 marks)

1 2 3 4

A B Loan Amount ($) Annual Interest (%) Loan period (month) Monthly payment ($) TABLE 2-1: Janet’s Loan Scheme

Enter the formula for cell B4: (3 marks) Cell

b)

B4

=

You decided to tabulate a Data Table the monthly payment based on annual interest rate from 3.5% to 8.0% in . What goes into the Row_input_cell, and the Column_input_cell? (2 marks)

Row input cell:

______________________

Column input cell: ______________________

c)

Over the 12-month period, Janet works part-time to pay the monthly payment. However, during some months, she may over-spend and does not have enough money to pay the monthly payment. Each month, Janet has a 30% chance of over-spending. Prepare a simple model as shown in TABLE 2-2, to simulate how often Janet will over-spend and count the number of months Janet will over-spend. Do not write the values in TABLE 2-2.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

D Month 1 2 3 4 5 6 7 8 9 10 11 12

E Random Number ??

Total # months over-spend TABLE 2-2: Janet’s Over-Spending

F Over-Spend? ??

??

Enter the formulas for the cells (??) as indicated in TABLE 1-2. (2 marks each) Cell

E2 =

Cell

F2 =

Cell

F14 =

End of the Examination Paper...