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:
- Create the Header Row; in cell A1 type "Tasks", in cell B1 type "Start Date", in cell C1 type "Days", in cell D1 type "End Date"
- Lists the tasks; in cell A2 type "Create Budget Template", in cell A3 type "Send Template to Managers", continue with the rest of the tasks in column A.
- The first task starts on 1/1/2018 and has 10 days to complete; in cell B2 type "1/1", in cell C2 type "10"
- Fill in the days required to complete each task; in cell C3 type "1", in cell C4 type "10", continue with the rest of the values in column C.
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:
- Create a new sheet in the by clicking the " + " in the bottom left corner next to the tab labeled "Sheet1", this should create "Sheet2"
- List the dates of the holidays to be excluded; in cell A1 type "1/15", in cell A2 type "2/19"
- Save the dates entered as a list called "HolidayList" (note: it is important to be consistent when naming a list and referencing that list in a function—spaces are not allowed and list names are case sensitive)
- Select cells A1 through A2
- On the left side of the ribbon to the left of the function box is the Name Box (it should say "A1"; in the Name Box type "HolidayList" [if you do not do this, you will get #Name?]
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:
- In cell B3 enter the function "=D2"
- Click and drag the bottom right corner of cell B3 to cell B8 to fill the function (the values won't make sense till we create an End Date function)
- The workday.intl function will reference Start Date and Days, then we will use the weekend value "1" to exclude Saturday and Sunday, and finally exclude the dates on our HolidayList; in cell D2 enter the function "=workday.intl(B2,C2,1,HolidayList)"
- Click and drag the bottom right corner of cell D2 to cell D8 to fill the function. (the values in both Columns B and D should now make sense)
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:
- In Cell E2 enter the function "=D2-B2"
- Click and drag the bottom right corner of cell E2 to cell E8 to fill the function
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:
- Select cells A1 through D8 then on the "Insert" Ribbon Tab in the "Insert Chart" area click the drop-down area for the "Insert Column or Bar Chart" select the 2-D Bar "Stacked Bar" option (should be the middle option with horizontal bars)
- On the "Chart Tools" Ribbon tab select the "Format" tab.
- On the far left of the ribbon is a drop-down menu (says Chart Area to start), select "Horizontal (Value) Axis" then click the "Format Selection" button Below (a Chart Settings Pane should open on the right hand side of the screen.
(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:
- Click the picture of 3-bar chart, which will take you to "Axis Options"
- For the sub-category - "Bounds" enter the beginning and end dates for the minimum and maximum; minimum type "1/1/2018" and maximum type "2/21/2018"
- For the sub-category - "Units" enter Major "2.0" and Minor "1.0"
- Then in the number drop down make sure category is selected as "Date" and type is in the "3/14" Month/Day format
- Next to the picture of the 3-bar chart at the top is a picture of a box with a 4-Directional arrow in it, select this to adjust the text orientation
- In the drop-down menu for text direction select "Rotate all text 270°"
- In the Chart Settings Pane click the drop down menu at the top (should say "Axis Options") and select "Vertical (Category) Axis"
- Select the 3-bar chart options and open the Axis Options.
- Check the box that says "categories in reverse order"
- In the Chart Settings Pane click the drop-down menu at the top and select "Series 'Start Date' "
- Click the paint bucket options (to the left of the 3-bar chart)
- Under "Fill" make the selection "No Fill"
- In the Chart Settings Pane click the drop-down menu at the top and select "Series 'End Date' "
- Click the paint bucket options
- Make the selection "No Fill"
- In the Chart Settings Pane click the drop-down menu at the top and select "Series 'Days' "
- Click the 3-bar chart
- If you scroll to the table data the cells C2:C8 have a blue box selecting them
- Drag the box to select the values E2 through E8 by clicking and dragging on the edge of the box
(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)