Submission requirement: submit one shareable URL to your final deliverable. Use a link that the teacher can open, such as Google Drive, OneDrive, GitHub, Canva, YouTube, or another workspace. This assignment is graded manually.

Context

You are a Financial Analyst at a fast-growing FMCG company. The executive team has requested a consolidated financial model to evaluate historical performance and a potential new capital investment. Using the principles, formulas, and Excel best practices learned throughout this course, you will build a dynamic, client-ready Excel workbook from scratch.

Scenario Details

Your model must address three distinct areas of the business across separate sheets:

Data Cleansing and Mapping: Organize raw financial data entries and map them to their correct financial categories.
P&L Generation: Build a structured Profit and Loss (P&L) statement for the years 2021, 2022, and 2023, featuring automatic totals and Year-on-Year (YoY) percentage variations.
Capital Investment Decision: Model a $100,000 project expansion using financial functions to evaluate a bank loan schedule and project profitability (IRR/NPV).

Detailed Instructions

Task 1: Database Setup and Mapping (Tab: "1_Database_Mapping")

Set up a mapping key table. Map at least 15 raw general ledger expense and revenue rows into simplified reporting categories (e.g., Revenues, COGS, SG&A Expenses, Depreciation, Tax) using VLOOKUP or INDEX & MATCH.
Ensure no hardcoded mapping values are used; the mapping must dynamically pull from your reference lookup table.

Task 2: P&L Statement and YoY Analysis (Tab: "2_PL_Statement")

Structure a professional P&L statement.
Populate the historical values (2021, 2022, and 2023) from the mapped Database sheet using SUMIF or SUMIFS.
Calculate gross profit, operating profit, and net income lines using standard formulas.
Calculate Year-on-Year (YoY) percentage variations (e.g., 2021-2022, 2022-2023). Use the IFERROR function to cleanly handle potential division-by-zero or missing data errors.
Use absolute cell references (the $ sign) where appropriate to ensure formulas can be copied across columns without breaking.

Task 3: Loan Schedule & Project Investment Analysis (Tab: "3_Investment_Analysis")

Set up a loan schedule for a $100,000 capital expenditure loan with a 5-year term and a 6% annual interest rate. Calculate the constant annual payment and split each year's payment into principal and interest components.
Compute the project's Internal Rate of Return (IRR) and Net Present Value (NPV) at a 7% discount rate based on estimated free cash flows of: Year 0: -$100,000; Year 1: $25,000; Year 2: $30,000; Year 3: $35,000; Year 4: $40,000; Year 5: $45,000.

Task 4: Formatting, Layout, and Best Practices

Apply clean custom formatting: format all currencies cleanly (e.g., thousands separators, no unnecessary decimals), and format all growth rates as percentages (%).
Apply Freeze Panes to keep headers visible when scrolling.
Ensure no automatic green error flags (arrows) are visible on calculated cells.
Practice the "Beauty Saving" technique: save and submit the workbook with the active cursor placed in cell A1 of the first tab.

Submission Requirements

Submit a single Excel workbook (.xlsx format) containing the three fully formulated tabs. Ensure all formulas remain live (do not paste values over calculations).