Assignment practice paper PDF

Preview

Summary

Warning: TT: undefined function: 321COR1305 Spreadsheet Modeling and Analytics (SMA)Assignment 1 (TOTAL = 51 Marks) – AY2019/20 Term 2 - Due in Week 4Label your filename as follow: e. JackTan_A1_GX where GX refers to section GX; A1 refers to Assignment 1 and drop it into eLearn Dropbox Assignment 1....

Description

COR1305 Spreadsheet Modeling and Analytics (SMA) Assignment 1 (TOTAL = 51 Marks) – AY2019/20 Term 2 - Due in Week 4 Label your filename as follow: e.g. JackTan_A1_GX.xlsx where GX refers to section GX; A1 refers to Assignment 1 and drop it into eLearn Dropbox Assignment 1. Note the date due policy. All submissions will be time stamped. Multiple submissions are permitted but only the last submission will be considered in terms of time and marks. Late submissions will have marks deducted accordingly. Question 1 (12 marks) Company has two types of raw materials. One is raw_A for product A and the other is raw_B for product B. The average output benefit generated by per unit of raw material varies with their quantity. Table 1 is the known data which provides the lists of average benefits generated by per unit of raw_A or per unit of raw_B at different procurement amount. You are going to predict the new productivity and total output for A or B given any unit holdings of raw_A or raw_B as input respectively. Table 1. Average output benefit per unit of raw input Qty (units)

raw_A ($) per unit

raw_B ($) per unit

1998

8

20

10002

9.3

25

19893

9.6

30

48987

10

34

76099

9

28

99029

6.8

19

In your worksheet, draw scatter curves using the data in Table 1. X-axis represents the quantity, Y-axis represents the average output benefit per unit of raw input for raw_A and raw_B respectively. According to the prior knowledge, the productivity curve should follow a polynomial trendline with an order equal to 2. Please follow the prior knowledge and add the trendlines for the curve of raw_A and the curve of raw_B respectively and then derive the function of productivity using quantity as input, the productivity and total output from each type of material as output. After that, please use the derived productivity functions for raw A and raw B to calculate their output given the following unit holdings for raw_A and raw_B respectively and further calculate the company’s total output in each year. Table 2. Quantity of raw_A and raw_B Year

raw_A (Qty)

Raw_B(Qty)

2018 2019

10000 40000

3000 20000

1

Question 2 (10 marks) The atomic weight of carbon is 12.011. The atomic weight of hydrogen is 1.0079. The atomic weight of oxygen is 15.9940. Create a model that allows you to enter the number of carbon atoms, hydrogen atoms, and oxygen atoms in a molecule and calculates: (i) the total number of atoms in the molecule and (ii) the molecular weight of the molecule. Create 2 test cases using: a) ethane C2H6 and b) sucrose C12H22O11 c) You have discovered a suspicious compound and you suspect that the atoms making up the compound are carbon, hydrogen and oxygen. The molecular weight of the molecule of the compound of 46.0634. Can you guess what atoms make up the compound? You can use trial and error, or Goal Seek method (you need to briefly explain in a note about how you use trial and error or how you configure Goal Seek). Question 3 (11 marks) Aunty May plans to start a business. She anticipates $180,000 in revenue for the first year and that revenue will grow at 12% per year. She estimates that her total expenses for the first year will be $100,000 and will be grow at 6% per year. a) Design a table that shows her revenue, expense, and income before tax, as 3 different columns, during the first 8 years of operations. No test cases needed. b) She is thinking maybe she should not be too ambitious and start small instead. By starting small, her initial revenue is expected to be $100,000, initial expense is $50,000, annual growth in revenue is 8%, and annual growth in expenses is 5%. No test cases needed. Design a second table to show the computation for 8 years of operations. The models created so far are tables which extend from year 1 to year 8, which may be cumbersome if one wishes to determine Aunty May's income in say, year 20. (You may argue this - Prof, just need to fill the cells down the table to year 20, it is not cumbersome. Well, that's not my point.) Using the model in part b), how can you modify your model to determine her income in year N without the need for a long table? No test case needed. Question 4 (10 marks) You are considering purchasing a new car. The price would be $150,000. You would pay $15,000 now as down payment and pay the rest using a loan on a monthly basis over four years. The automobile dealership is offering loan promotions where either,  Plan A - you will receive a $1,000 rebate right now to offset your down payment and the annual interest rate on the loan will be 4% or,  Plan B - the annual interest rate on the loan will be 3% but there is no rebate.

2

a) Create 2 models to compare the two plans by calculating,  The monthly payment amount  The present value of the total outflow of cash for each plan, assuming an annual 2.5% discount rate  Which is the better deal? b) At what rebate amount for Plan A will there be no difference between the 2 plans? Question 5 (8 marks) At Shingapore Management University, students who register for at least 5 courses are considered Full-Time students and pay $9,100 tuition per term. Students who register for less than 5 courses are considered Part-Time students and pay $2000 per course. a) Create a worksheet that allows you to enter a student's name and the number of courses for which he or she is registering. The worksheet should then display, i) the status of the student (Full-Time or Part-Time) and ii) the amount of tuition owed. b) Create a set of test cases using the following three students: Maria Shaparova 4 courses, John Daly 6 courses, Tiger Woods 3 courses. (Your layout should list input table, model table and the table for test cases)

3...