Assignment 1 - G11 PDF

Preview

Summary

Warning: TT: undefined function: 32 Warning: TT: undefined function: 32COR1305: Spreadsheet Modeling and AnalyticsArindam CHATTERJEEAssignment 1 (TOTAL of 50 Marks)AY20 19 -20: TERM 1 – Section GDue in Week 6, Wednesday ( 23 rd of September 2019 ) 7:00 PMLabel your filename with your name and the as...

Description

COR1305: Spreadsheet Modeling and Analytics Arindam CHATTERJEE

Assignment 1 (TOTAL of 50 Marks) AY2019-20: TERM 1 – Section G11 Due in Week 6, Wednesday (23rd of September 2019) 7:00 PM Label your filename with your name and the assignment number: e.g. ArindamCHATTERJEE-A1.xlsx where A1 refers to Assignment 1. Drop it in the eLearn Submission Folder of your section with the name Assignment 1. Each question should be answered in its own Worksheet within the single Excel Workbook that you upload. Name the worksheets Q1, Q2, Q3, Q4 and Q5. 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, with previous files getting overwritten by subsequent uploads. Late submissions will attract penalties. Valuation Rubric: 1) 2) 3) 4)

Correctness: 60% Documentation: 20% Formatting: 10% Creativity/Bonus: 10%

This assignment has five questions.

1

Question 1 (5 marks) The atomic weight of carbon is 12.011. The atomic weight of hydrogen is 1.0079. The atomic weight of oxygen is 15.9994. Create a worksheet 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. Try your worksheet on propane C3H8 and on glucose C6H12O6.

Question 2 (5 marks) Salespeople receive $15,000 base salary plus a commission of 2.5% of sales. Anyone who sells over $5 million receives a free vacation in Bintan. Anyone who sells over $7 million receives an additional $7,500 bonus. At the end of the year salespeople are rated as follows: under $3 million "Dud", $3 million through $5 million "Star", over $5 million "Superstar". Create a worksheet that allows you to enter a salesperson's name and annual sales amount and then calculates the following: Salesperson Summary Name: (input) Annual sales: (input) Base salary: $15,000 Commission: (formula) Bonus: (formula) Total pay: (formula) Free vacation? (formula) Status: (formula)

Question 3 (10 marks) Scientists at the Institute for Robotics have created a new type of robot that reproduces itself. The new robot takes two weeks to gather materials and then builds one new robot (exactly like itself) each week for three weeks. The first new robot is activated at the end of week 3, the second new robot is activated at the end of week 4, and the third new robot at the end of week 5. As soon as it is created, each new robot goes thought the same cycle of gathering materials for two weeks and then creating three new robots, one per week. As so on for each of the new robots. At the

2

end of the first five weeks of its existence each new robot ceases building new robots but retires and “lives” forever. The scientists plan to build only one robot with their own hands and then set it in motion. Create a spreadsheet model that will tell you how many robots there will be at the end of each week for 52 weeks.

Question 4 (15 marks) ICBM is a start-up computer company. This quarter they expect Sales of $500,000. They expect their Sales to grow at a rate of 12.5% per quarter. Their Cost of Goods Sold is running at 35% of Sales and is expected to stay at that rate. Their Selling Costs are 12% of Sales and are expected to stay at that rate. Their General and Administrative Costs (including Research and Development) are $450,000 this quarter and are scheduled to rise at 4.0% per quarter. (a) Design and implement a worksheet to make 20 quarter income statement projection for ICBM. You should have a separate input area with well labeled separate cells for each of the numbers above. You should have a separate row for each item in the income statement and a separate column for each quarter. The final row should be for Earnings Before Taxes. Use the Fill operations wherever possible. In the initial quarters, ICBM should show operating losses (negative Earnings Before Taxes). Under the assumption stated above, what is the first quarter in which ICBM shows an operating profit (positive Earnings Before Taxes)? (b) Suppose Sales grow at 11.5% per quarter. Now in what quarter would ICBM first show an operating profit? (c) Suppose Sales grow at 10% per quarter and General and Administrative Costs grow at 6% per quarter and Selling Costs are 11% of Sales. Now in what quarter will ICBM first show an operating profit?

Question 5 (15 marks) You plan to deposit a fixed amount at the beginning of each year in a bank account for 20 years. Assume that you will earn a constant interest rate on your money. Design and implement a worksheet that will show you how much you will have accumulated at the end of each of the 20 years. You should have separate, well-labeled cells for the amount of your annual deposit and for the interest rate. If you change either of these cells, all the accumulations should change automatically. Assume that the interest is paid at the end of the year. Use your worksheet on the following inputs and tabulate the results for the 20 year-end bank balances, and show them separately for each of the following cases below:

3

(a) $7,500 deposit per year and 4.50% per year interest rate, (b) $7,500 deposit per year and 11.25% per year interest rate, (c) $11,750 deposit per year and 6.50% per year interest rate, and (d) How much do you need to deposit each year to have exactly $1,500,000 at the end of 20 years assuming that you earn 8.25% interest per year? (e) What annual interest rate would you need to obtain in order to accumulate $2,500,000 at the end of 20 years if you deposit $13,500 per year?

4...