New Perspectives Excel 2019 | Modules 9-12: SAM Capstone Project 1a

#newperspectivesexcel2019 #module9-12 #9-12samcapstone #np #new

To Get this Solution Contact us on Email and WhatsApp is given in Video
WhatsApp no : +923146535108
Email address: solution000777@gmail.com

Whats'app link: wa.link/h8kz99

Facebook I'd link:   / 199qwadxsd  

Insta I'd link: https://www.instagram.com/aadiii_005?...

TikTok I'd link: https://www.tiktok.com/@aadiii_0005?_...

Snap I'd link: https://www.snapchat.com/add/aadiniaz...

LinkedIn I;d link: https://www.linkedin.com/in/jawad-nia...



Projects worksheet → Unprotect sheet.

Clear filter in State column.

Sort by Project Start (oldest → newest).

CopyStats macro:

Edit VBA → change Range("G3:L22") → Range("I3:L22").

Save & close.

Run macro.

Rename new sheet → Project Stats.

AddRow macro button:

Insert Form Control button in I1 (left of Print Stats).

Assign AddRow macro.

Resize (0.3" H × 1" W).

Align with Print Stats button.

Text = Add Project.

Data Validation (C4:C37):

List from I4:I7.

Input msg: Project Category → Enter a project category.

Error alert: Stop → Invalid Category → Enter a category listed in column I.

Correct invalid data: Circle invalid → fix to Site development.

Summary formulas (I3:L7):

J4: COUNTIF(Projects[Category], I4) → Fill J5:J7.

K4: SUMIF(Projects[Category], I4, Projects[Estimate]) → Fill K5:K7.

L4: AVERAGEIF(Projects[Category], I4, Projects[Estimate]) → Fill L5:L7.

Projects by State PivotTable → Refresh.

PivotTable:

Layout = Outline Form.

Group Project Start = Months.

Insert Timeline Slicer (Project Start) → Move to A34:F40 → Show all months.

GETPIVOTDATA in I17:J22 (Montana → Wyoming).

Project Payments PivotTable: Add Category to Rows.

Add calculated fields:

Amt Owed = Estimate – Paid to Date.

% Difference = (Estimate – Paid to Date) / Estimate.

Format % Difference → % (2 decimals).

Change headers → Amount Owed, % Owed.

Add Slicer (State) → Move G3:I11 → Filter to Nebraska (NE).

Projects by Category PivotTable → Add State as Columns.

Create 100% Stacked Column PivotChart → Move A11:G25.

Monthly Sales (CSV import via Power Query):

Import Support_EX19_CS9-12a_Sales.csv.

Remove Projects + Notes cols.

Load to A3 table.

Style = Green, Table Style Medium 2.

Format Sales ($mil) = Currency, 0 decimals.

Employees data (Access import):

Import Employees + Offices → Data Model.

Insert PivotTable at A3 (Employees sheet).

PivotTable (Employees):

Row = OfficeCity.

Values: Title (count), YearsEmployed (avg).

Format avg years = Number (1 decimal).

Power Pivot → Relate Employees + Offices on OfficeID.

Expansion Loan: Insert WordArt (Green, Accent 1; Shadow).

Text = Expansion Loan.

Fill = Black, Text 1, Lighter 25%.

Size 24 pt.

Move D1:F1.

Fix Errors:

L16 → SUM(B16:K16).

B19 → =B18/C4.

Fill C19:K19.

Remove trace arrows.

CUMIPMT in B16 (Year 1 interest) → Fill C16:K16.

CUMPRINC in B17 (Year 1 principal) → Fill C17:K17.

Straight-line depreciation:

B23 = SLN(cost, salvage, life).

Fill C23:K23.

Declining balance depreciation:

B29 = DB(cost, salvage, life, B28).

Fill C29:K29.

SYD depreciation:

B34 = SYD(cost, salvage, life, 1).

B35 = SYD(cost, salvage, life, 10).

Investments & Projections:

B18 = NPV(B17, B5:B14).

B20 = IRR(B5:B14).

Monthly Revenue Projections chart:

Add Trendline → Logarithmic.