Shelly Cashman Excel 365/2021 | Modules 1-3: SAM Capstone Project 1b

# Forward Construction

## CREATE FORMULAS WITH FUNCTIONS

### GETTING STARTED

· Open the file **SC_EX365_2021_CS1-3b_ FirstLastName_1.xlsx**, available for download.

· Save the file **SC_EX365_2021_CS1-3b_ FirstLastName_1.xlsx**as

**SC_EX365_2021_CS1-3b_**

*FirstLastName*_2.xlsxo Edit the file name by changing “1” to “2”.

o If you do not see the **.xlsx** file extension, do not type it. The file extension will be added for you automatically.

· With the file **SC_EX365_2021_CS1-3b_ FirstLastName_2.xlsx**open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

o If cell B6 does not display your name, delete the file and download a new copy.

### PROJECT STEPS

1. Liang Bandy is a senior project manager at Forward Construction, a firm that works on construction projects for business and commercial buildings. Liang has created a workbook summarizing the Office Plaza Construction project for the Meadowood Office Center. She asks for your help in completing the workbook.

Go to the *Budget* worksheet. **Unfreeze** the panes since Liang does not need to display those rows as she scrolls the worksheet.

2. In cell K2, enter a formula using the **NOW **function to display today’s date. Apply the **Short Date **number format to display only the date in the cell.

3. Format the worksheet title as follows to use a consistent design throughout the workbook:

a. Fill cell C2 with the **Teal, Accent 6, Lighter 40%** shading color.

b. Change the font color to **White, Background 1**.

c. **Merge and center** the contents of cell C2 across the range C2:I2.

d. Use **AutoFit**to resize row 2 to its best fit.

4. Format the hourly rate data as follows to suit the design of the worksheet and make the data easier to understand:

a. **Italicize**the contents of cell L2 to match the formatting in cell J2.

b. Apply the **Currency**number format to cell M2 to clarify that it contains a dollar amount.

5. Format the data in cell B4 as follows to display all of the text:

a. Merge the cells in the range B4:B14.

b. Rotate the text up in the merged cell so that the text reads from bottom to top.

c. **Middle-align**and **center** the text.

d. Remove the border from the merged cell.

e. Resize column B to a width of **6.00**.

6. Format the data in row 4 as follows to show that it contains column headings:

a. Change “Description” to use **Code Description** as the complete column heading.

b. Apply the **Accent 6** cell style to the range C4:I4.

c. Use **AutoFit **to resize column E to its best fit.

7. In column F, Liang wants to include the actual dollar amount of the work completed. Enter this information as follows:

a. In cell F5, enter a formula without using a function that multiplies the completed hours (cell **E5**) by the hourly rate (cell **M2**) to determine the dollar amount charged for blueprint drafting. Include an absolute reference to cell M2 in the formula.

b. Use the Fill Handle to fill the range F6:F14 with the formula in cell F5 to include the charges for the other work.

c. Format the range F6:F14 using the **Comma Style** format and no decimal places to match the formatting in column G.

8. Liang needs to show how much of the estimate remains after the completed work. Provide this information as follows:

a. In cell H5, enter a formula without using a function that subtracts the completed amount billed (cell **F5**) from the estimated amount (cell **G5**) to determine the remaining amount of the estimate for blueprint drafting.

b. Use the Fill Handle to fill the range H6:H14 with the formula in cell H5 to include the remaining amount for the other work.

c. Format the range H6:H14 using the **Comma** **Style** format and no decimal places to match the formatting in column G.

9. Liang also wants to show the remaining amount as a percentage of the completed amount. Enter this information as follows:

a. In cell I5, enter a formula that divides the remaining dollar amount (cell **H5**) by the estimated dollar amount (cell **G5**).

b. Copy the formula in cell I5 to the range I6:I15, pasting only the formula and number formatting to display the remaining amount as a percentage of the actual amount for the other work and the total.

10. Calculate the totals as follows:

a. In cell E15, enter a formula using the **SUM** function to total the actual hours (range **E5:E14**).

b. Use the Fill Handle to fill the range F15:H15 with the formula in cell E15.

c. Apply the **Accounting**number format with no decimal places to the range F15:H15.

11. Liang also wants to identify the work for which Forward Construction has billed more than the full estimate amount.

In the range I5:I14, use conditional formatting Highlight Cells rules to format values less than 1% (0.01) in **Light Red Fill with Dark Red Text**.

12. Liang imported data about the project team working on the Office Plaza Construction project and stored the data on a separate worksheet, but wants to include the data in the *Budget* worksheet. Copy and paste the data as follows:

a. Go to the *Project Team* worksheet and copy the data in the range B2:G13.

b. Return to the *Budget* worksheet. Paste the data in cell K3, keeping the source formatting when you paste it.

13. Liang needs to list the position for each team member. Those with five or more years of experience have a Senior position. Otherwise, they have Support position. List this information as follows:

a. In cell O5 on the *Budget* worksheet, enter a formula that uses the **IF** function to test whether the number of years of experience (cell **N5**) is greater than or equal to **5**.

b. If the team member has five or more years of experience, display **“Senior”**in cell O5.

c. If the team member has less than five years of experience, display **“Support”**in cell O5.

d. Copy the formula in cell O5 to the range O6:O14, pasting the formula only.

e. Use **AutoFit**to resize column O to its best fit.

14. Liang wants to include summary statistics about the project and the team members. Include this information as follows:

In cell E17, enter a formula that uses the **AVERAGE**function to average the number of years of experience (range **N5:N14**).

15. Make the Line chart in the range C19:I33 easier to interpret as follows:

a. Change the chart type to a **Clustered Column chart**.

b. Enter **Task Progress** as the chart title.

c. Add a primary vertical axis title to the chart, entering **Hours** as the axis title text.

d. Add data labels to the **Outside End** of each column.

16. Delete row 35 since Liang has adjusted the chart.

17. Go to the *Schedule*worksheet. Rename the *Schedule*worksheet tab to **Work Schedule** to use a more descriptive name.

18. Each type of work starts on a different date because the types depend on each other. Enter the starting dates for the remaining work as follows:

a. In cell D6, enter a formula without using a function that adds **1** day to the value in cell **C6**.

b. In cell E6, enter a formula without using a function that adds **2** days from the value in cell **C6**.

c. In cell F6, enter a formula without using a function that adds **3** days to the value in cell **C6**.

d. In cell G6, enter a formula without using a function that adds **3** days to the value in cell **C6**.

[Mac Hint: The date will display in the format m/d/yy.]

19. Copy the formulas for inspections to the rest of the schedule as follows:

a. Copy the formula in cell D6 to the range D7:D8.

b. Copy the formula in cell E6 to the range E7:E8.

c. Copy the formula in cell F6 to the range F7:F8.

d. Copy the formula in cell G6 to the range G7:G8.

20. In cell C9, enter a formula that uses the **MIN** function to find the earliest date in the project schedule (range **C6:G8**).

21. In cell C10, enter a formula that uses the **MAX** function to find the latest date in the project schedule (range **C6:G8**).

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.