BA 206 Excel Assignment #2: Gantt Chart

 

 

Summary: We are going to be building a project plan schedule with a Gantt Chart using Excel.  In this project you are creating the company budget and putting the timeline into a Gantt Chart format.  All of the tasks will be listed in Column A, their start dates in Column B (will be calculated based on the previous tasks end date, the number of days to complete the task in Column C, and finally in Column D we will use the "workday.intl" function to calculate when the End date is, excluding weekends and holidays (For example if there are 3 days to complete a task that begins on a Friday, day 1 is Friday and day 2 is Monday).

The tasks to be completed are:

Create Budget Template (Beginning 1/1 - 10 days to complete)

Send Template to Managers (1 day to complete)

Managers Complete Dept. Budgets (10 days to complete)

Collect Completed Budgets (2 days to complete)

Compile Dept. Results (5 days to complete)

Produce Company Budget (5 days to complete)

Present Final Budget (1 day to complete)

Step 1:

Note: There are 2 Holidays in the months of Jan and February that we will not work on; Martin Luther King Day (January 15th) and President's Day (February 19th).  We will exclude these dates by creating a list that will be used in the function in Column D.

 Step 2:

Note: Going back to Sheet1 we will now create 3 formulas to fill out the rest of our table. After the first task the next task will start on the end date of the previous task.  So for the task Start Date in Cell B3 we have the same value as the End Date in cell D2.  Then we will use the "workday.intl" function and our List "HolidayList" to calculate each tasks End Date.  The function requires 4 values; a date, number of days, a numeric value corresponding to Weekend Days to be excluded, and finally a List to exclude Holiday Dates.

Step 3:

Note: Our End Date as you will notice will be more than the specified number of days away.  We need to calculate a new total to be referenced in or Gantt Chart so that the weekends and holidays will be accounted for.  We can subtract the dates in Columns B and D to get the new Value.

Step 4:

Note: Now we will create a Stacked Bar Graph and format it to create a Gantt Chart. We will create it using the original "Days" Column then later change the data range to accommodate the adjusted value for simplicity.

Step 5:

(Note: If you click off the chart in the worksheet the format chart area will change, you can click back on the chart to return to formatting the chart--- click the chart option drop down menu to return to the specific aspect you were working on)

Step 6:

(Note: Very important not to click on the work sheet elsewhere or the data ranges will no longer be highlighted --- return back to the "Series 'Days' " selection if the highlights become unselected)