Sample Paper 4 - Answers PDF

Preview

Summary

AY2013-14 Term 2 Final Exam21 April 2014IS102 Computer as an Analysis ToolINSTRUCTIONS TO CANDIDATES Time allowed for this examination paper is 2 hours. This examination paper contains TWO (2) questions and comprises ELEVEN (11) pages inclusive of this instruction sheet. You are required to answer A...

Description

IS102 Computer as an Analysis Tool

AY2013-14 Term 2 Final Exam 21 April 2014 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 ELEVEN (11) 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-14T2

Question 1

13

Question 2

12

TOTAL

25

1

IS102 Computer as an Analysis Tool

THIS PAGE INTENTIONALLY LEFT BLANK

2013-14T2

2

IS102 Computer as an Analysis Tool

Question 1 (13 marks) Part A (7 marks) The table below shows grading records for 5 students. There are five component marks, Assignment1, Assignment2, Midterm, Final Exam and Project, each taking 10%, 10%, 25%, 30%, and 25% in a student’s “Weighted Total” calculation.

A1)

Please use Autofill method to fill in Column H and Column I, where Column H gives out a student’s weighted total mark (using the five components and their respective weights) and Column I gives the student’s rank based on their weighted total mark. What is the formula for Cell H4 and I4? (2 marks)

The formula for Cell H4 is:

=SUMPRODUCT(C4:G4,C$1:G$1)

The formula for Cell I4 is:

=RANK(H4,H$4:H$12,0)

A2)

Design a formula to allow the teacher view student’s average weighted total based on gender.

Use Cell B15 to take an input of gender, “M” or “F”.

2013-14T2

3

IS102 Computer as an Analysis Tool

If B15 is “M”, Cell B16 should give out the average weighted total for all male students; If B15 is “F”, Cell B16 should give out the average weighted total for all female students. What is the formula for Cell B16? (1 mark) The formula for Cell B16 is:

=SUMIF(B4:B12,"="&B15,H4:H12)/COUNTIF(B4:B12,"="&B15)

A3)

Use some DATALOOKUP function to allow the teacher search for a student’s full information based on rank.

Use Cell A22 to take an input, from 1 to 5, which is the rank information. The teacher, once input the rank, should be able to pull out the corresponding student’s full record, from Cell B22 to I22. What is the formula in Cell B22? Note that you are not allowed to change the original data in A4:I8. (2 marks) The formula for Cell B22 is:

=INDEX(A4:A8,MATCH($A22,$I4:$I8,0))

A4)

Use some DATALOOKUP function to allow the teacher search for a student’s full information based on the student’s name. The method you designed here must be different from the one you used in above question A3 (i.e., use different Excel formula).

2013-14T2

4

IS102 Computer as an Analysis Tool

Use Cell A24 to take an input, which will be the student’s name. The teacher, once input the name, should be able to pull out the corresponding student’s full record, from Cell B24 to I24. What is the formula in Cell B24? (2 marks) Note that you are not allowed to change the original data in A4:I8. The formula for Cell B24 is:

=VLOOKUP($A24,$A4:$G8,2,FALSE)

Part B (6 marks) Robert is running a bubble tea shop. He operates the shop 20 days a month and the unit cost for making one cup of bubble tea is S$0.5. He knows that demand is a linear function of his price. B1)

Build your spreadsheet using the following inputs. Find the demand-price linear relationship using the given data. Put the intercept of this demand-price equation in Cell B6, and slope in Cell C6. Both cell values should be accurate to two decimal places. What is the linear equation? (1 mark)

The linear equation, in form of “demand= CellB6 + CellC6 * price” is:

B12=B6+C6*A12

2013-14T2

5

IS102 Computer as an Analysis Tool

B2)

If Robert decides to charge at $1.1/cup, do the calculation for him based on your above result. What is the # of cups he will sell per day (Cell B12), and what is the profit per month for him (Cell C12)? Provide the numerical answers for these two cells, both accurate up to two decimal places. (2 marks)

The numerical answer (i.e., value) for Cell B12 is _____208.3 cups____________________

The numerical answer (i.e., value) for Cell C12 is ______$2500_______________________

B3)

Build an income-expense table for Robert, in A14 to D28, as follows

Robert has a saving account at the bank, with an annual interest rate of 1.5%. Now (i.e., at month 0), he has S$10,000 in the account. For the next one year (month 1 to month 12), each month, he will withdraw S$2,000 at the first day of each month as his expense, and deposit his profit from the bubble shop into his account at the last day of each month as income. Profit from the bubble tea shop can be referenced from the value you get in Cell C12. Calculate the balance in his account for each month in column D. What is the formula for Cell D17? What is the final balance amount by the end of 12th month – the numerical value at Cell D28 – please be accurate up to 2 decimal places? (2 marks)

2013-14T2

6

IS102 Computer as an Analysis Tool

The formula for Cell D17 is:

=(D16-B17)*(1+B$14/12)+C17

The numerical answer (i.e., value) for Cell D28 is: ___$16162.25____________________ B4)

Robert wants to know by the end of which month his account balance will almost reach S$15,000? Use DATALOOKUP function to find the corresponding month. Put the answer in Cell A30. What is the formula for Cell A30? (1 mark)

The formula for Cell A30 is:

=LOOKUP(15000,D16:D28,A16:A28)

! !

2013-14T2

7

IS102 Computer as an Analysis Tool

Question 2 (12 marks) Project Scheduling is a key facet in many supply chain and manufacturing problems. We consider the scheduling problem for a manufacturing company called ISIOZ. For the manufacturing process to provide a finished product, there are five activities that need to be completed: Activity A, Activity C, Activity Q, Activity P and Activity E. Activity durations are not known with certainty since execution of activities is dependent on the nature of the raw material. Given below are the distributions associated with different activities: Activity A: Duration follows the following distribution: Duration Probability

6 0.5

7 0.25

8 0.15

9 0.1

Activity C: Duration follows a normal distribution with mean 7 and standard deviation 2. Activity Q: Duration follows a normal distribution with mean 4 and standard deviation 1. Activity P: Duration follows the following distribution: Duration Probability

14 0.2

16 0.35

18 0.45

Activity E: Has a constant duration of 3 units. EXAMPLE: Due to uncertainty, if an activity has the durations: {2, 3, 4, 6, 3} in 5 different instances, then Average duration = (2 + 3 + 4 + 6 + 3)/5 = 3.6 90th percentile duration = PERCENTILE({2,3,4,6,3},0.9)= 5.2 A)

Please write down the formulae and answers to estimate the average and 90th percentile duration for individual activities A and C? Final value should be provided in the space below. (2 marks)

Activity A: Average: =B5*0.5+B6*0.25+B7*0.15+B8*0.1 (6.85) 90th percentile: =PERCENTILE(B13:B512,0.9) (8) Activity C: Average: =F4 (7) 90th percentile: =NORMINV(0.9,7,2) (9.56) 2013-14T2

8

IS102 Computer as an Analysis Tool

B)

ISIOZ manager has requested your help in computing the 90th percentile duration for completion of both activities A and C, when they start simultaneously (at time 0)? An incomplete model is provided below. Use Monte Carlo simulation (1000 trials) to estimate the 90th percentile duration for completion of both activities: A and C. Provide documentations for your excel model in the space provided below. Final solution value should be provided in cell F12. (3 marks) Documentations

2013-14T2

9

IS102 Computer as an Analysis Tool

C)

Extending on part (B), provide a 90th percentile duration for completing the following combination of 5 activities: Execute A and C simultaneously (they start at the same time 0). Start Q once either A or C is completed. Start P once A, C and Q are all completed. Start E once P is completed.

Provide documentations for your model in the space provided below to calculate the duration for finishing all five activities according to the above constraints. Provide the column titles (A1:F1) and other necessary labels in the figure provided below. Final solution for 90th percentile should be provided in cell J1. (4 marks)

Documentations

2013-14T2

10

IS102 Computer as an Analysis Tool

D)

Finally, compute the combination with the least 90th percentile duration that includes all 5 activities. Given below are the key constraints that have to be satisfied by the combination: 1. Although A, C and Q can start simultaneously, due to physical constraints, only two of these activities can be executed simultaneously at any point in time. 2. P can only start once A, C and Q are all completed. 3. E should only start once P is completed.

There are three possible combinations of the five activities that satisfy all three conditions above. Please list those three possible combinations in the space provided below (Please note that an example of one combination was provided in part C). The best combination with the least 90th percentile duration should be provided in the space below. (3 marks) Combination 1:

(1) Start with A and C, Execute Q when one of A and C finish, Execute P when A, C and Q finish, Execute E when P finishes

Combination 2:

(2) Start with A and Q, Execute C when one of A and Q finishes, Execute P when A, C and Q finish, Execute E when P finishes Combination 3:

(3) Start with C and Q, Execute A when one of C and Q finishes, Execute P when A, C and Q finish, Execute E when P finishes Best combination and 90th percentile duration

As can be seen from sheet (d), combination (1) performs better in terms of 90th percentile duration

End of Paper

2013-14T2

11...