Gantt Chart Format - Excel by woz18343

VIEWS: 177 PAGES: 11

More Info
									You are the supervising manager of a local general store.
  It is nearing time for the yearly inventory of mercandise.
  With the information below, develop a Gantt chart to plan out the time needed
  and the sequence of tasks to complete the inventory.
 Task ID Antecedents Time(hrs) 1 2 3
A            None                      1
B            None                      2
C            A, B                      4
D            C                         2
E            A, B                      1
F            A, B                      1
G            F                         8
H            G                         4
I            H                         1
J            I                         3
K            None                      6
L            K, J                      5

Instructions:…Complete this sheet to describe the activities and time required for your project graphically as a Gantt chart.
A. Label the four columns at the left of the spreadsheet "Task Description", "Task ID", "Antecedents", and "Time(time unit)".
B. Label columns to the right of Column D with column heading numbers beginning with 1.
           Format the width of the time columns (Format Column Width) to 2.2 to make them narrow and easier to color.
C. Identify the tasks that need to be performed to complete your project in the "Task Description" column.
D. Identify each task you define in the "Task Description" with a unique Code Reference (A, B, C, D, etc).
E. Order the task priority, identifying the Task IDs of tasks that must be completed before in the "Antecedents" column.
F. Identify the time that is budgeted to complete each task in the "Time(hrs)" column.
G. Beginning in column #1, highlight the number of cell(s) to the right of any task that does not have an Antecedent.
           For example, Task A has no Antecedent and takes 1 hour, so beginning with column #1 (its left edge is Time 0),
           highlight one cell. Next, use the Fill Color (pouring bucket) icon and then the Borders icon at the top of the screen
           to paint and border the cell(s).
           Because Task B has no antecedent, it also begins in column #1 and extends two [Time(hrs)=2] cells to the right.
H. Tasks with antecedents must begin at the line where the latest antecedent to be completed ends.
           Therefore, fill in the four cells after tasks A and B have ended (cells G8 through J8).
I. Continue coloring and bordering cells for the remaining tasks.


           A completed Gantt chart should look like Solution A. Can you complete this Gantt Chart without peaking?


ALSO PROVIDED
Situation B poses another hypothetical project and Solution B provides its corresponding completed Gantt chart.
                     Description                              Antecedent Time(hrs)    1   2    3   4    5     6   7   8   9 10 11 12 13 14 15 16 17 18
Choose an inv. date before new goods arrive           A None                 1
Give guidelines and directions to employees           B None                 2
Count inventory in storage areas                      C A, B                 4
Collect data from inventoried storage areas           D C                    2
Count the outside inventory                           E A, B                 1
Divide store into geographical areas                  F A, B                 1
Inventory each section                                G F                    8
Collect and record data                               H G                    4
Close store at end of business day                    I   H                  1
Calculate itemized sales for the day of inventory     J   I                  3
Organize invoices for actual goods received to date   K None                 6
Compare inventoried goods with goods invoices         L K, J                 5


                                                      CORRECT GANTT CHART FOR INVENTORY

                                                      Observation.
                                                      Assume that the tasks relate to your formal proposal.
                                                      Assume that you have assumptions of how much each task will cost to complete.
                                                      Assume that you have an assumption of a revenue stream that the proposal will generate that follows most of the ot
                                                      Assume that you change the width of the Gantt Chart cells using the Formatting commands (Format Column width)
                                                          to scale (same width) and so you can enter the revenue (positive numbers) or cost (negative numbers) associate
                                                          each highlighted cell into each highlighted cell.
                                                      What you then have is dollars in time periods that can easily be discounted in order to get a Net Present Value for yo
                                                      All that is necessary is that you sum the cash flows in each column, and then divide the column sums (cash flows pe
                                                         by (1+discount rate)^t, where t is the number of years that have passed since project inception.
                                                      Refer to the Excel Sheet labeled Gantt w DCF for an example.
               19 20 21 22 23




 that follows most of the other tasks.
ds (Format Column width) so cells are
gative numbers) associated with


 a Net Present Value for your proposal.
olumn sums (cash flows per period)
HAVING A BIRTHDAY PARTY!!!                              1   2   3   4   5   6   7   8   9 10 11 12 13 14 15
Task Description             Task   Antecedent   Time
Decide on date for party      A        None        1
Book bouncy castle            B          A         1
Send invitations              C          A         4
Receive replies               D          C         7
Buy toys and balloons         E          D         1
Buy food                      F          D         3
Blow up balloons              G          E         2
Make food                     H          F         1
Decorate room                  I       H, G        1
Get bouncy castle             J          B         1
Have party                    K         J, I       1
Clear up                      L          K         4
Send back bouncy castle       M          K         1
Send out thank-you letters    N          L         3
Give away duplicate gifts     O          M         3
16 17 18 19 20 21 22 23 24 25 26 27
HAVING A BIRTHDAY PARTY!!!
Task Description        Task ID   Antecedents   Time   1   2   3   4   5   6   7   8   9   10 11 12
Decide on date for        A          None         1
Book bouncy castle        B             A         1
Send invitations          C             A         4
Receive replies           D             C         7
Buy toys and balloons     E             D         1
Buy food                   F            D         3
Blow up balloons          G             E         2
Make food                 H             F         1
Decorate room              I          H, G        1
Get bouncy castle          J            B         1
Have party                K            J, I       1
Clear up                   L            K         4
Send back bouncy          M             K         1
Send out thank-you        N             L         3
Give away duplicate       O             M         3
13 14 15 16 17 18 19 20 21 22 23 24 25
YOUR GANTT CHART
Who?          What?                       When ?           How        How
                                                           Long?      Much?
Responsible        Task         Task      Antecedent                                           0        1        2         3        4        5        6        7        8        9       10       11              12
    Party       Description        ID                                                  0             1/12     1/6       1/4      1/3      5/12     1/2      7/12     2/3      3/4      5/6     11/12        1
                                                            Time in     Cash Inflow
                                                            Months    (Cash Outflow)
                                                             Below        Below
                                                                             0
                                                                             0
                                                                            (1)               -1
                                                                            (2)                        -2
                                                                            (3)                                 -3
                                                                            (4)                                           -4
                                                                             1                                                      1
                                                                             2                                                               2
                                                                             3                                                                        3
                                                                             4                                                                                 4
                                                                             0
                                                                             0
                                                                             0
                                                                             0
                                                                             0
                                                                             0
                                                                             0

              Subtotal of Period Cash Flows                                 0              (1.00)   (2.00)   (3.00)    (4.00)   1.00     2.00     3.00     4.00     0.00     0.00     0.00     0.00             0.00

              Cumulative Cash Flows at each period's end                                   (1.00)   (3.00)   (6.00)   (10.00)   (9.00)   (7.00)   (4.00)   0.00     0.00     0.00     0.00     0.00             0.00

                       Discount Rate                         0.18
              Present Value of Period Cash Flows                                           (1.00)   (1.97)   (2.92)    (3.84)   0.95     1.87     2.76     3.63     0.00     0.00     0.00     0.00             0.00

              Cumulative Net Present Value of Cash Flows                                   (1.00)   (2.97)   (5.89)    (9.73)   (8.78)   (6.92)   (4.15)   (0.52)   (0.52)   (0.52)   (0.52)   (0.52)        (0.52)
                                                                                                                                                                                                        ^CumNPV^

              The expected increase in wealth is the Cumulative NPV.
              If Assumptions are reasonable and Cumulative NPV is positive, do the project.
              The Internal Rate of Return is the Discount Rate that causes the Cumulative Net Present Value to equal zero.
              The Time 0 Column suggests that the first cash outlay occurred on Jan 31.

              A Gantt Chart ideally should specifically explain Who (is responsible), What (activities are necessary),
                            When (do the activities begin, what is the sequence, and how long will each take), How long (should each activity take once it is begun, and
                            How Much (does the activity affect cash flow: cash reciepts are positive values, cash disbursements are negative values).
<< Time (mos)
<< Time(yrs)
                  GANTT CHART of Adult Day Care Proposal

Who?             What?                                                                        When ?        How         How
                                                                                                            Long?       Much?

Responsible                                     Task                 Task                     Antecedent                Periods >               0            1              2          3           4           5           6
      Party                                  Description                        ID                                                         0              1/12        1/6           1/4         1/3         5/12        1/2
                                                                                                              Time in     Cash Inflow
                                                                                                              Months    (Cash Outflow)
                                                                                                              Below          Below


                           CRITICAL OPERATING ASSUMPTIONS >>         Expected # of Customers / day
                           CRITICAL OPERATING ASSUMPTIONS >>         Expected # of operating days/quarter
                           CRITICAL OPERATING ASSUMPTIONS >>         Expected Revenue / customer-day
                           CRITICAL OPERATING ASSUMPTIONS >>         Expected Expense / customer-day
Management       Secure City Government Approval                     A                        none              1           (24,023)     (24,023)
Management       Get plans approved by city                          B                        E                 1             (500)                                 (500)
Marketing Dept   Get Building Logo Designed                          C                        H                 2           (10,000)                                                                                 (5,000)
Management       Start hiring and training new and exsisting staff   D                        K                 2           (140,000)


Architect        Architect Fees                                      E                        A                 1           (100,000)                (100,000)
Architect        Hire Contractor                                     F                        E                 2           (30,000)                              (15,000)      (15,000)
Contractor       Get all permits                                     G                        B                 1            (3,000)                                             (3,000)
Contractor       Foundation work / flooring / plumbing               H                        F, G              2           (75,000)                                                        (37,500)    (37,500)
Contractor       Framing / structural support                        I                        H                 2           (100,000)                                                                               (50,000)
Contractor       Electrical work / roofing and insulation            J                        C, I              3           (80,000)
Contractor       Drywalls and windows                                K                        C, I              2           (50,000)
Contractor       Interior work / painting / carpeting                L                        K                 2           (80,000)
Contractor       Decorating / furnishing                             M                        K                 2           (30,000)
Contractor       Landscaping                                         N                        H                 1           (40,000)                                                                                (40,000)
Operations       Operating Revenues from Adult Day Care              O                        M                 21          295,380
Operations       Operating Expenses                                  P                        M                 21          (104,850)
Management       Sale of Building in 3 years                         Q                        P                 3          1,200,000




Subtotal of Period Cash Flows                                                                                               628,007       (24,023)    (100,000)    (15,500)      (18,000)    (37,500)    (37,500)    (95,000)


Cumulative Cash Flows at each period's end                                                                                                (24,023)    (124,023)   (139,523)     (157,523)   (195,023)   (232,523)   (327,523)




Present Value of Period Cash Flows                                   Disount Rate                              0.22                       (24,023)     (98,357)    (14,995)      (17,127)    (35,095)    (34,518)    (86,009)


Cumulative Net Present Value of Cash Flows                                                                                                (24,023)    (122,380)   (137,374)     (154,501)   (189,596)   (224,114)   (310,123)


                                                                     Internal Rate of Return =               0.296948
       7           8           9          10          11          12
    7/12        2/3         3/4         5/6        11/12      1




 (5,000)
                                    (70,000)    (70,000)




(50,000)
            (26,667)    (26,667)    (26,667)
            (25,000)    (25,000)
                                    (40,000)    (40,000)
                                    (10,000)    (10,000)    (10,000)




 (55,000)    (51,667)    (51,667)   (146,667)   (120,000)    (10,000)


(382,523)   (434,190)   (485,856)   (632,523)   (752,523)   (762,523)




 (48,976)    (45,252)    (44,508)   (124,270)   (100,004)     (8,197)


(359,100)   (404,352)   (448,860)   (573,130)   (673,134)   (681,330)

								
To top