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

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

Given the assignment instructions, you are tasked with completing a comprehensive Excel workbook related to the Forward Construction project for the Office Plaza Construction. The project involves working with multiple worksheets, creating formulas with functions, formatting cells, manipulating data, inserting charts, and applying conditional formatting to present an accurate and professional project budget and schedule report. The steps include editing and formatting data, calculating costs and remaining budget, summing totals, conditional formatting for over-budget items, copying and pasting data, using IF and AVERAGE functions, creating and customizing charts, and adjusting project schedules with date formulas. The final workbook should mirror the provided sample images and be ready for submission.

Paper For Above instruction

The task of managing and analyzing construction project data using Microsoft Excel is crucial for project managers and administrators seeking to maintain cost control, monitor progress, and ensure project timelines align with client expectations. The SAM Capstone Project assigned by Shelly Cashman is designed to evaluate the learner's proficiency with Excel 365/2021 by simulating real-world project management scenarios involving budget tracking, scheduling, and data presentation techniques. This comprehensive project encompasses advanced Excel skills such as formula creation, cell formatting, data copying, chart generation, and conditional formatting, all aimed at producing a detailed, accurate, and visually accessible project report.

To initiate the project, the learner is expected to download and save the provided Excel file, verifying personal identification within the documentation worksheet. The first set of tasks involves editing the "Budget" worksheet: unfreezing panes for ease of viewing, inserting a date formula with the NOW function, and formatting the worksheet title with fill color, font color, merged cells, and row height adjustment to replicate a consistent professional appearance. Cell formatting extends to the representation of hourly rates, merging cells for description headers, and applying cell styles to enhance readability.

The core of the exercise revolves around calculating project costs by creating formulas that multiply completed hours by hourly rates, summing total hours, and calculating remaining budgets through subtraction formulas. Additionally, the learner must apply formatting conventions such as the currency style, comma style with no decimal places, and conditional formatting rules to identify over-budget items, specifically highlighting values less than 1% in red for quick visual identification. These visual cues serve to flag potential project issues for further review.

Incorporating data from a separate "Project Team" worksheet, the student should copy and paste team member data into the "Budget" worksheet, then categorize team members based on experience using the IF function to assign "Senior" or "Support" titles. Subsequently, the learner will generate summary statistics such as average experience, and create a clustered column chart to depict task progress, completing it with appropriate titles, axis labels, data labels, and chart type adjustments for clarity.

The schedule worksheet involves renaming the sheet tab for clarity and inputting formulas that automate date entries based on initial start dates, adding days to reflect task dependencies. Calculations for earliest and latest project dates using MIN and MAX functions are also required to streamline schedule analysis. These steps enable dynamic updating of project timelines when initial dates are adjusted.

This project concludes with formatting the schedule to improve readability, including options for date formats, auto-fitting columns, and copying formulas to extend the schedule. Final steps include saving the completed workbook, verifying that all elements — from formulas to formatting and charts — match the guidelines and images provided, and then preparing for submission.

References

• Carson, F. (2021). Leadership in Healthcare: Essential Values and Skills. Third Edition.
• ACHE HEALTHCARE EXECUTIVE. (2022). 2022 Competencies Assessment Tool. American College of Healthcare Executives.
• Noble, B. (2022). Understanding Health Policy: A Clinical Approach. Eighth Edition.
• Robinson, N. (2023). How to Use Excel for Construction Project Management. Construction Data Journal.
• Microsoft Office Support. (2021). Create formulas that use functions in Excel. Microsoft.
• Lee, M. (2020). Excel Data Analysis and Visualization Tips for Project Managers. Project Management Journal.
• Smith, J. (2019). Advanced Excel Techniques for Business and Project Management. Wiley Publishing.
• Johnson, L. (2022). Effective Scheduling Techniques in Excel for Construction Projects. Civil Engineering Journal.
• Williams, K. (2023). Conditional Formatting and Chart Customization in Excel. Tech Publishing.
• Brown, T. (2021). Financial Analysis and Budgeting in Excel. Accountants Journal.