Mc 2010-11: Assignment I

MC 2010-11: Assignment I

Released Date: 21-7-10 Submission Date and Time 31-7-10, 5:30 PM

Instructions:

1. This is an individual assignment (not group).

2. You are required to develop spreadsheet solutions to the following problems and submit worksheets of your solutions as a single xlsx file on Moodle.

3. The worksheets must be neat, and well documented. Insert comments in cells where ever necessary and insert an extra worksheet to give an overall summary of your solutions.

4. Submit your assignment file to Moodle well on time to avoid system congestions or break-downs during the last minute.

Assignment Problems (given in the attached pages):

1. Corporate Crèche Planning

2. Eclipse Tourism

Note:1. Where ever there are questions in these problems, asking you to fill in the blanks, take them as hints for you to complete your worksheet development.

2. The workbook with worksheets containing data for both these problems is uploaded on Moodle. Use only the workbook provided there in developing your solutions. Do not change the given layouts and the data provided in the worksheets. You may add comments / extra worksheets for documentation.

Problem I: Corporate Crèche Planning

Fueled by popular demand ABC co. wants to start a crèche for its employee's children. The crèche will be housed in the company and all infrastructure and fixed costs will be borne by the company. However the monthly salary for the crèche staff and some miscellaneous monthly expenses have to be borne by the parents.

The employees at the company fall into four different grades according to their salary, and will be charged at different rates. The ratio of the differential fees is given in table 1. For example, if Grade1 employees are charged Rs. 100/month then Grade2 employees will be charged Rs. 125/month.

The fees charged will also depend on the age of the child (as younger children need more care)- the ratio of which is given in Table 2. For example, if a child less then 1 year is charged Rs.200/month, then a 4 year old child should be charged only Rs.100/month.

There are two kinds of staff at the crèche - 1 supervisor and multiple assistants. The number of assistants to be employed depends on the number and age of the children. The ratio of assistants to children is given in Table 2. For example for children 1 year and below, an asst. ratio of 0.5 means 1 assistant is needed for every 2 children.

The monthly salary for the different kinds of staff is given in Table 3. Monthly misc expenses the crèche will incur is also given in F3.

The database containing enrolment details is given separately on a sheet named 'db' in the same workbook in range A1:C51. This contains 3 fields : Name of child, age of child in months and grade of employee parent.

Assume all amounts are in Rs. and are monthly charges.

Note: Do not change any data or fields in the spreadsheet. Do not create any additional columns.

1) Table 4 contains summarized enrolment details categorized by age brackets and employee grades. For example, there are 3 children in the age range 0 to 1 year for Grade1.

a) Use the Data Table feature to fill this table till column F. You can use cells in range E22:F23 for the criteria range if needed, and B23, C23 for text if needed.

b) Write a formula for G17 ( to be copied till G20) for total children in each age bracket

c) Formula for H17 ( to be copied till H20) for number of assistants needed in each age bracket. Note that the number of assistants needed in each age bracket have to be positive integers. For example, if 2.3 assistants are needed for a particular age-bracket you need to allocate 3 assistants.

2) Table 5 contains the fees for each category of age and grade. The lowest fee is for the Grade,Age combination : Grade1, Age3 to 8 years. An initial value for this fee is given in cell D3. Write a formula for B27 (which will be copied thru to fill Table 5)

3) Write Formulae for the cells:

a) G11 to calculate total assistants.

b) B33 to compute the total fee.

c) C33 to compute total expenses.

d) D33 to compute the deficit.

4) The company wants the monthly fee collection to meet the total monthly expenses. Use the Goal Seek tool to find out the new fees to be charged (assume that the expenses remain the same).

Problem II: Eclipse Tourism.

(The global date format is set to dd/mm/yyyy in the following excel Sheet.)

Figure-1. Data on Solar Eclipses during 2011-2020.

A B C D E F G H I J K L M N

1 CalendarDate EclipseType Eclipse

Magnitude Duration Visibility Year Month Day DurMin DurSec TotSec Ndate Elapsed

Days Month

Names

2 2011 Jan 04

Partial 0.858 - EUR,ASA,AFR 2011 1 4 - - - 04/01/2011 168 Jan

3 2011 Jun 01

Partial 0.601 - ASA,NAM,ARC 2011 6 1 - - - 01/06/2011 316 Feb

4 2011 Jul 01

Partial 0.097 - INO 2011 7 1 - - - 01/07/2011 346 Mar

5 2011 Nov 25

Partial 0.905 - AFR,ANT,AUS 2011 11 25 - - - 25/11/2011 493 Apr

6 2012 May 20

Annular

0.944 05m46s

ASA,PCF,NAM,ASA,PCF,NAM 2012 5 20 5 46 346 30/04/2012 670 May

7 2012 Nov 13

Total