Excel Answers

Create a excel sheet and name it as Student_Attendance sheet with the following columns

a.      S.No , RegId, Name, Gender, Lectures Attended, Lectures Delivered, Attendance percentage.

(Note : Attendance percentage = LecturesAttended/LecturesDelivered * 100)

a.      Highlight Column Headings with background color and a different text color. Also Use text Rotation.

Let Q denote the number of times a printer in a local library will run out of paper: 0, 1, 2, or 3 times in a given day. Let R denote the number of times the circulation desk clerk is called away from the desk. Their joint probability distribution is given in the “Joint Probability” worksheet labeled “Joint Probability” in the Excel file.

Find P(R = 2 | Q = 2). Give your answer to 3 decimal places.

Query 1: List all of the books that are published by Prentice Hall using the LastName field of the Author table, the Title field of the Book table, and the PubName field of the Publisher table. Sort the query in ascending order according to the Title field. Save this query as Prentice Hall Books. Query

2: List all of the books where the publish date is greater than today’s date using the LastName field of the Author table, the Title and PubDate fields of the Book table, and the PubName field of the Publisher table. Sort the query in descending order according to the PubDate field. Save this query as Books not yet published. Query

3: List all of the books where the copyright year is 2010 or the publish date is less than 60 days old using the LastName field of the Author table, the Title, PubDate and Copyright fields of the Book table, and the PubName field of the Publisher table. Sort the query in descending order according to the PubDate field. Save this query as New Books. Query

1. Create a Table: A. Create a table called Publisher having the following structure: Field Name Data Type PubID Short Text PubName Short Text URL Hyperlink Set the field PubID as the primary key. Set the Field Size field property of the field PubID to 5. Set the Field Size field property of the field PubName to 50. Set the caption property of the PubID field to Publisher ID. Set the caption property of the PubName field to Publisher’s Name.

2. Import external data from the Excel spreadsheet file Bookstore. • Make sure to Import the source data into a new table in the current database is selected. • Select the Author worksheet. • Make sure that First Row Contains Column Headings is selected. • For the AuthorID field, set the Data Type option to Long Integer and set the Indexed option to Yes (No Duplicates). • Select Choose my own primary key and make sure the AuthorID field is selected. • Save the table with the name Author

1. Age-related maculopathy is a leading cause of blindness among people 65 and older in the United States, and is estimated to affect between 16 and 26% of people in this age group. In a recent study by Klein, residents aged 43 to 86 years in the town of Beaver Dam, Wisconsin were asked to participate in a study to determine whether cigarette smoking was related to age-related maculopathy. At a baseline examination, participants were asked to report their lifetime smoking habits. After 5 years, participants had an examination to determine whether they had developed age-related maculopathy. The following table presents the number of cases of age-related maculopathy measured at the follow-up examination among the 1232 male participants ages 43-86 who did not have age related maculopathy (ARM) at the baseline examination:

Smoking status

N

Cases of ARM

Never smokers

368

26

Ever smokers

864

79

1. Create a 2 x 2 table where one axis is smoking status and the other is age-related maculopathy status. (4 pts)
   

A. Drivers using the Accra-Tema motorway have to stop at the tollbooth to pay their tolls. Motorists arrive at the tollbooth at a rate of 200 per 8-hour-day. The single tollbooth attendant can serve, on average, 220 motorists per 8-hour-day.

a. What is the average minutes’ time a motorist waits in the queue to receive service at the toll?

b. What is the average number of motorists in the queuing system?

Management of the motorway likes to have its operators working 90% of the time. What must the arrival rate be for the tollbooth attendant to be as busy as management would like?

a) Formulate a linear programming model for this problem. EV (10marks)

b) Use solver to find optimal solution and sensitivity report.           CR (5marks)

c) Management have asked you to determine the optimal solution. Write your answer in a form of a report to be submitted to the management.         AN (5marks)

Advice the management about objective function value corresponding to your answer in (b).           

Daily requirements of 70 g of protein, 1 g calcium, 12 mg iron, and 3000 calories are needed for a balanced diet. The following foods are available for consumption with the cost and nutrients per 100 g as shown.

Protein

(g)

Calories

Calcium

(g)

Iron

Cost

GH¢

Brown Bread

12

246

0.1

3.2

0.5

Cheese

24.9

423

0.2

0.3

2

Butter

0.1

793

0.03

1

Baked Beans

6

93

0.05

2.3

0.25

Spinach

3

26

0.1

2

0.25

The objective is to find a balanced diet with minimum cost.

(a) Formulate a linear programming model for this problem.

(b) Use solver to find optimal solution and sensitivity report.     

Solve the following Linear Programming (LP) problem:

Maximize Z = 3X1 + 2X2

Subject to: X1 + X2 ≤ 6

2X1 + X2 ≤ 8

-X1 + X2 ≤ 1

X2 ≤ 2

a) Identify the feasible extreme points (maximum attractive corner) for the problem.

b) Solve the problem graphically.