Reference no: EM132383431

IS602 Spreadsheet Modeling for T&O Decisions Assignment - School of Information Systems, Singapore Management University, Singapore

Question 1 - 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 C₂H₆ and

b) sucrose C₁₂H₂₂O₁₁

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? Explain how you get the atoms.

Question 2 - 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.

c) 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 3 - The Friendly Mortgage Company offers you choice of 10-year, 20-year, and 30-year home mortgages all at the same interest rate of 6% for a loan amount of $500,000.

(a) Create a model that will allow you to enter the amount of the mortgage and the annual interest rate. For each loan period choice, the worksheet should calculate:

i) the monthly payment,

ii) the total amount paid back to the mortgage company (in absolute dollar terms), and

iii) the total amount of interest paid.

(b) For the 30-year loan, and assuming the same loan amount of $500,000, at what interest rate, would your total amount paid be twice the loan amount? Explain how you get the amount.

Question 4 - You are considering purchasing a new car. The price would be $18,239. You would pay $2,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 11.9% or,
• Plan B - the annual interest rate on the loan will be 7.9% but there is no rebate.

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 8% discount rate
• Which is the better deal?

b) At what rebate amount for Plan A will there be no difference between the 2 plans? Explain how you get the amount.

Question 5 - Averosas are a new breed of small, furry animal that resemble guinea pigs. They are becoming favourite pets among pet lovers. You have decided to earn some extra money by raising averosas in your room and selling them. You bought a one-month-old male averosa and a one-month-old female averosa to begin the breeding process.

Averosas have their first litter at six months of age. Every three months from then on, each averosas pair will give birth to 2 new pairs of averosas (assume two males and two females). Of course, these new babies will go through the same breeding cycle. That is, each averosas pair will give birth to 2 pairs of averosas at the end of 6 months (2 quarters), 9 months (3 quarters), 12 months (4 quarters), 15 months (5 quarters), 18 months (6 quarters), 21 months (7 quarters), 24 months (8 quarters), etc.

At the end of each quarter, you plan to sell all the aversosas that have reach two years of age to the local pet stores.

Set up an Excel worksheet to answer these questions. Assume you will have the averosa colony for the next five years.

You would like to know,

(a) how many pairs of averosas will be born in your room each quarter and,

(b) how many pairs of averosas will be living in the room each quarter and,

(c) how many pairs of averosas you'll be able to sell at the end of each quarter.

Assuming everything goes as planned,

(d) What is the total number of averosas you would have owned during the five years?

(e) What is the total number of averosas you would have sold in five years?

(f) What is the maximum number of averosas that will be in your room at any one time?

Note: No test cases are needed for this question.

Hint: Set up your model as follow:

• 1^st column - Label 1 to 20 down the column to represent end of Quarter 1 to end of Quarter 20.
• 2^nd column to 10^th column - Compute the number of pairs of averosas at age 0 (just born), age 1 quarter, age 2 quarters, ...., until age 8 quarters.
• 11^th column - Sum up the total number of pairs of averosas at the end of each quarter.