1 The following table shows the allocation of work assignmen

1) The following table shows the allocation of work assignments and resources needed for an investment firm to make a decision on investing in company XYZ.

Create Resources Loading Chart

Task A Task B Task C Profolio Managers Week 1(S) Week3 (P) Week 1 (P)Week2 (S) Week 1 (P) Week 2 (P)Week2 (S) Associates Analysts (P) Primary (S)-Secondary

Solution

Let\'s understand the question first.

We are given the types of resources, i.e. portfolio manager, associate and analyst, the type of tasks they need to work on, i.e. Task A, B or C, the week in which this task needs to get completed, i.e. Week 1, 2 or 3 and the number of person-weeks required per week

From the second table shown in the question, it is clear that we need 2 associates in Week 2, 2 analysts in Week 1 and 5 analysts in Week 2 to complete the assignment.

So overall we need

(a) 1 portfolio manager
(b) 2 associates &
(c) 5 analysts

for this assignment.

In order to create the resource loading chart now, lets us MS-Excel. Thee attached snapshot of the MS-Excel based resource loading chart below shows the following:

1. All the resources, from portfolio manager 1 to analyst 5, are listed in Column B
2. Weeks 1 to 3 are list in Row 4
3. Since each resource needs to work on more than one task, lets put down tasks A, B and C in column C for each resource.
4. Now put 1 or 0 for each task and each week of a given resource based on table 2 of the question. So, for example, portfolio manager spends 1 person-week in Week 1 on Task A, which is shown in field D6 of the sheet. Similarly populate the entire chart.
5. Now do sum of person weeks for each resource over all the tasks. So, for example, field D5 is sum(D6:D8) and field F21 is sum(F22:F24) and so on.
6. Now, use \"Conditional Formatting\" feature of Excel on all the rows where you have done the sum, to color fields in either red or green depending upon whether the resource is under utilized (when sum = 0) or when it is optimally utilized (when sum = 1).
7. This creates the resource loading chart.

Resources	Tasks	Weeks
		Week 1	Week 2	Week 3
Portfolio Manager1		1	0	1
Task A	1	0	0
Task B	0	0	0
Task C	0	0	1
Associate1		1	0	1
Task A	0	0	0
Task B	1	0	0
Task C	0	0	1
Associate2		0	0	1
Task A	0	0	0
Task B	0	0	0
Task C	0	0	1
Analyst1		1	1	0
Task A	1	0	0
Task B	0	1	0
Task C	0	0	0
Analyst2		1	1	0
Task A	1	0	0
Task B	0	1	0
Task C	0	0	0
Analyst3		0	1	0
Task A	0	0	0
Task B	0	0	0
Task C	0	1	0
Analyst4		0	1	0
Task A	0	0	0
Task B	0	0	0
Task C	0	1	0
Analyst5		0	1	0
Task A	0	0	0
Task B	0	0	0
Task C	0	1	0