CAT Assignment 1 PDF

Preview

Summary

IS102 Computer as an Analysis ToolAssignment 1Question 1Internet Shopping, Inc. has ambitious plans. This week they have 100,000 visitors to theirwebsite. Of their visitors, 2% place orders. These orders average $24 each. Thus they aretaking orders for $48,000 this week (week 0).For the next two yea...

Description

IS102 Computer as an Analysis Tool Assignment 1 Question 1 Internet Shopping, Inc. has ambitious plans. This week they have 100,000 visitors to their website. Of their visitors, 2% place orders. These orders average $24.00 each. Thus they are taking orders for $48,000 this week (week 0). For the next two years they plan to increase the visitors by 3% per week. They expect the percent who place orders to stay the same. They expect the average amount ordered to increase by $0.15 per week. Create a worksheet to predict their growth over the next two years. You should have a separate input area with the two growth figures. You should have a row for each week. You should have columns for the week number (0 to 104), the number of visitors, number of shoppers, average amount ordered, and total ordered for each week. Changing either of the numbers in the input area should be reflected in changes in your worksheet.

Question 2 A company evaluates new graduates for positions according to three criteria. If the candidate has a Grade Point Average (GPA) of at least 3.3, the candidate receivers 1 point. On the interview, candidates who score 7 or 8 (out of 10) receive 1 point; candidates who score 9 or 10 receive 2 points. The company gives an aptitude test as well. A candidate who scores above 85 gets 1 point. The company adds up the points to obtain a candidate's rating. A candidate who receives a rating of 0, 1, or 2 points is not given a job offer. A candidate who receives 3 points is given an offer as a Junior Salesperson. A candidate who receives 4 points is given an offer as a fast-track Manager in Training. Create a worksheet that allows the Human Resources Department to enter in a candidate's name, GPA, interview score, and aptitude score and determines the employment decision.

Question 3 The Director of New Business Development has come to you with an interesting proposal. The Home Products Division of International Industries is for sale. The best estimate is that the Home Products Division will earn $4 million next year, $5 million the following year, $6 million the third year and that in the fourth year you will be able to sell it for $30 million. Your company expects to earn 20% per year on its investments. a)

Create a worksheet that will tell you a fair price to pay today for the Home Products Division.

b)

On negotiation, International Industries says that they will accept 1/3 of the total payment today, 1/3 in a year, and 1/3 in two years. Create a worksheet that allows you to enter in a total payment and then gives you the rate of return on the investment. Have a cell at the top that tells you whether or not you achieve your goal of a 20% return.

c)

If you assume the correctness of the estimates in a) and the results of the negotiations in b) what would be a fair price to pay to earn 20% on your money?

d)

Suppose you can sell the Division only for $20 million instead of $30 million? Now what would be a fair price to pay?

Question 4 Sales Analysis Product Line by Season (All Sales in $ Millions) Product

Winter

Spring

Summer

Fall

Golf

3.2

6.2

5.7

3.9

Tennis

4.7

5.2

7.1

2.9

Skiing

3.7

2.1

1.1

5.2

Surfing

1.2

2.1

2.9

1.3

Total

Average

Total Average

All the following charts should be as understandable as possible and should be clearly identified with titles, legends, and so on. Be careful not to include blank rows or columns in the data to be charted. Delete them if necessary. a)

Create a Column chart of total sales by season. Here, the total sales row would be plotted as columns (vertical bars) and the season names would be the X axis values. There should be four bars in the graph.

b)

Create a 3-D Column chart of sales for each of the different products by season. Do not include any totals. There should be 16 vertical bars in the chart.

c)

Display the chart in b) as a Bar chart organized by columns.

d)

Display the chart as a Bar chart organized by rows.

e)

Display the chart in b) as a Line chart.

Question 5 The Game of Life was invented by the Cambridge mathematician John Conway in the 1960s. The Game of Life takes place on an infinite two-dimensional grid. Each cell in the grid is either dead or alive. Each cell has eight neighbours, the cells that touch its sides or corners. If a cell is alive it will survive into the next time period (next generation) if it has two or three neighbors that also are alive. If a cell is alive and has four or more neighbours that are alive, it will die of overcrowding. If a cell is alive and has zero or one neighbor that is alive, it will die of exposure. If a cell is dead it will remain dead in the next generation unless exactly three of its neighbors are alive. In this case, the cell will be "born" in the next generation. The rules are applied simultaneously to all cells from one generation to the next. We begin the game at Generation 1 with some configuration of living cells specified by the user. Each subsequent generation is determined by the rules established by Conway. The living cells can flourish and expand or stay the same or die off. Implement the Game of Life in Excel. Living cells should be represented by 1's. Dead cells should appear blank. (Hint: You might want to represent dead cells by 0's but not display the 0's by clicking off the Zero Values box in the View sheet in the Options.) You should use at least a 20 x 20 universe and draw a box around the universe. The universe should be surrounded by a blank row or column on all four sides so nothing leaks at the edges of the universe. At the top of each universe you should display the generation number. The top left of the first sheet in the workbook should be a special universe for the user to enter in the initial configuration of 1's into Generation 1. The rest of the workbook should be protected from being changed. Any initial configuration of 1's should be acceptable. It is up to you how to design the rest of the workbook. You might want to use a separate worksheet for each generation. Or you might want to do all the work in one worksheet. Or you might want to have one area of a worksheet where the universe changes over time. In this case you might want to turn off Automatic Recalculation in the Calculation sheet in the Options and have the formulas recalculate the next generation only when you press F9. The design of the workbook is up to you. Be sure that the rules are applied only to cells in one generation to obtain the configuration of cells in the next generation. The workbook should be easy to use and should accurately portray the progress of life in the universe over at least a dozen generations, and preferably more, for any initial configuration.

a) Try our workbook with the preceding initial configuration, with the four initial configurations that follow. For each of the initial configurations determine the ultimate fate of the inhabitants, for example whether the colony lives and expands forever, is stable, or dies off. i)

1 1 1

1

1 1 1

1 1 1

1 1 1

1 1 1

ii)

iii)

1 1 1

1

1

1 1 1

1

1 1 1

1 1 1

1

1 1 1

iv)...