<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[ChallengeJP - Medium]]></title>
        <description><![CDATA[Step-by-Step Tutorials for Financial Modelling and Data Analytics. - Medium]]></description>
        <link>https://medium.com/challengejp?source=rss----51290e855bf9---4</link>
        <image>
            <url>https://cdn-images-1.medium.com/proxy/1*TGH72Nnw24QL3iV9IOm4VA.png</url>
            <title>ChallengeJP - Medium</title>
            <link>https://medium.com/challengejp?source=rss----51290e855bf9---4</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 01:18:26 GMT</lastBuildDate>
        <atom:link href="https://medium.com/feed/challengejp" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Building a Yield Management and Inventory Analysis Dashboard in 6 Steps]]></title>
            <link>https://medium.com/challengejp/building-a-yield-management-and-inventory-analysis-dashboard-in-6-steps-e448ab1e5861?source=rss----51290e855bf9---4</link>
            <guid isPermaLink="false">https://medium.com/p/e448ab1e5861</guid>
            <category><![CDATA[marketing-analytics]]></category>
            <category><![CDATA[yield-management]]></category>
            <category><![CDATA[dax]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-modeling]]></category>
            <dc:creator><![CDATA[Jacek Polewski]]></dc:creator>
            <pubDate>Sun, 08 Feb 2026 13:11:00 GMT</pubDate>
            <atom:updated>2026-02-08T13:11:00.837Z</atom:updated>
            <content:encoded><![CDATA[<p>In this tutorial, I show how to build a yield management and inventory analysis dashboard in Microsoft Power BI for evaluating advertising campaign performance across inventory providers.</p><p>The walkthrough covers data modeling, reusable DAX measures for yield and inventory performance, and dashboard design for benchmarking and optimization.</p><p>It then introduces forward-looking yield forecasting using What-If parameters, allowing you to test changes in conversion efficiency and inventory costs and see how those assumptions affect profitability and provider rankings.</p><p><em>(All screenshots and examples come from the full tutorial, which you can access — along with the sample data, DAX code, and templates — via the link at the end.)</em></p><h3>Step 1. Loading and Preparing Source Data for Inventory Yield Analysis</h3><p>This step sets the foundation for all yield management and inventory analysis in the dashboard. The objective is simple: load the campaign and inventory data, confirm structural consistency, and ensure Power BI interprets key fields correctly before any modeling or DAX work begins.</p><h4>Load Core Campaign and Inventory Datasets</h4><p>The model uses three CSV files (you can find a link to the source datasets at the end of this post), each with a distinct role:</p><ul><li><strong>Campaigns</strong> define the primary unit of analysis and serve as the basis for performance comparison and optimization.</li><li><strong>Inventory providers</strong> represent the traffic sources supplying impressions, clicks, and conversions.</li><li><strong>Monthly performance data</strong> captures aggregated results at the Campaign × Inventory Provider level, including volume, cost, and revenue inputs that later drive efficiency and yield calculations.</li></ul><p>Open Microsoft Power BI Desktop and load each file using <strong>Get Data → Text/CSV</strong>. After loading, select <strong>Transform Data</strong> to open Power Query Editor.</p><h4>Clean and Validate Campaign and Provider Tables</h4><p>Use Power Query Editor to check that Microsoft Power BI correctly interprets column headers in the campaign and inventory provider tables. If the first row appears as data rather than headers, apply <strong>Transform → Use First Row as Headers</strong> and verify that all column names read clearly and consistently.</p><p>Next, review data types across all tables. Date fields should use the <strong>Date</strong> type, numeric fields such as impressions, clicks, spend, and revenue should use numeric types, and key identifiers should match across dimension and fact tables.</p><figure><img alt="Power Query Editor showing campaign and inventory provider tables cleaned using the “Use First Row as Headers” transformation." src="https://cdn-images-1.medium.com/max/1024/1*-HSPXPlPFC8U8gE-GHvRmw.png" /><figcaption>Screenshot #1. Using Power Query Editor to prepare campaign and inventory provider tables before yield and inventory analysis.</figcaption></figure><p>Once the structure and data types look correct, select <strong>Close &amp; Apply</strong> to load the cleaned data into the model.</p><h3>Step 2. Building the Data Model for Campaign and Inventory Provider Analysis</h3><p>With clean source data loaded, the next step defines the data model that supports campaign- and provider-level yield analysis.</p><h4>Create a Calendar Table for Time-Based Yield Analysis</h4><p>Rather than relying on Power BI’s automatic date hierarchy, this model uses an explicit Calendar table. A dedicated calendar provides consistent month-level attributes for benchmarking, trend analysis, and forecasting.</p><p>Create a new table in Model view and add the following DAX. AUTOCALENDAR() generates a continuous date range based on the data, while ADDCOLUMNS() enriches it with the fields used throughout the report.</p><pre>Calendar =<br>ADDCOLUMNS (<br>    AUTOCALENDAR (),<br>    &quot;Year&quot;, YEAR ( [Date] ),<br>    &quot;MonthNumber&quot;, MONTH ( [Date] ),<br>    &quot;MonthName&quot;, FORMAT ( [Date], &quot;MMM&quot; ),<br>    &quot;MonthYear&quot;, FORMAT ( [Date], &quot;MMM YYYY&quot; ),<br>    &quot;MonthStart&quot;, DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )<br>)</pre><p>After creating the table, mark <strong>Calendar</strong> as a Date table using the <strong>Date</strong> column. This step ensures time intelligence functions behave correctly and keeps date logic explicit.</p><h4>Define Relationships Between Campaigns, Providers, and Performance</h4><p>With the Calendar table in place, define the relationships that connect campaigns, inventory providers, and monthly performance data.</p><p>Create the following relationships in Model view:</p><ul><li>dim_campaign[CampaignID] → fact_campaign_performance_monthly_2025[CampaignID]</li><li>dim_inventory_provider[InventoryProvider] → fact_campaign_performance_monthly_2025[InventoryProvider]</li><li>Calendar[Date] → fact_campaign_performance_monthly_2025[MonthStart]</li></ul><p>Set all relationships to <strong>one-to-many</strong>, <strong>single-direction</strong>, and <strong>active</strong>. This structure keeps filter flow clear and avoids ambiguity when combining campaign, provider, and time-based analysis.</p><figure><img alt="Data model showing campaigns, inventory providers, monthly performance data, and a calendar table connected in a star schema for yield analysis." src="https://cdn-images-1.medium.com/max/1024/1*b_9oys8AvIGcKKJRDsMcbg.png" /><figcaption>Screenshot #2. Star-schema data model linking campaigns, inventory providers, performance data, and the Calendar table for yield and inventory analysis.</figcaption></figure><p>At this point, the model is ready for metric development: a clean fact table, explicit dimensions, and a controlled time axis optimized for yield benchmarking and forecasting.</p><h3>Step 3. Creating Core Yield and Inventory Performance Metrics with DAX</h3><p>With the data model in place, the next step is to build the metric layer that powers all yield analysis, benchmarking, and forecasting. These DAX measures translate raw campaign performance into comparable signals that work consistently across campaigns, inventory providers, and time.</p><h4>Base Volume and Financial Measures</h4><p>The model starts with a small set of base totals that aggregate raw performance data. These include impressions, clicks, conversions, spend, and revenue.</p><p>A representative example looks like this:</p><pre>Conversions =<br>SUM ( fact_campaign_performance_monthly_2025[Conversions] )</pre><p>These base measures act as the foundation for all efficiency, yield, and forecast calculations later in the dashboard.</p><h4>Core Performance and Efficiency Rates</h4><p>Next, we derive rate-based performance metrics that make campaigns and inventory providers directly comparable. These include click-through rate (CTR), click-to-conversion rate (CVR), and end-to-end conversion efficiency per impression.</p><p>All rate measures use DIVIDE() rather than direct division to avoid divide-by-zero errors and keep visuals clean:</p><pre>CTR =<br>DIVIDE ( [Clicks], [Impressions] )</pre><p>Together, these measures help isolate where performance differences originate: traffic quality, engagement, or post-click conversion behaviour.</p><h4>Cost Efficiency Measures</h4><p>These measures include cost per click (CPC), cost per acquisition (CPA), and cost per thousand impressions (CPM). They explain <em>how much inventory costs</em> before considering monetisation or profitability.</p><h4>Yield and Monetisation Metrics</h4><p>Yield measures shift the focus from efficiency to value creation. They show how effectively inventory converts spend into revenue and profit, and whether performance scales economically.</p><p>A representative yield measure is return on ad spend:</p><pre>ROAS =<br>DIVIDE ( [Revenue], [Spend] )</pre><p>Alongside ROAS, the model includes profit, profit margin, revenue per thousand impressions (RPM), and revenue per conversion (RPA). These measures form the backbone of yield optimisation and inventory monetisation analysis.</p><h4>Provider Benchmarking and Rankings</h4><p>Finally, ranking measures translate efficiency and yield metrics into clear provider-level benchmarks.</p><p>These ranking are calculated using RANKX() measures and make relative performance immediately visible and set the stage for provider comparison in the report pages built next.</p><p><em>You’ll find the full DAX measure library — including all efficiency, yield, and benchmarking measures — in the complete tutorial linked at the end of this post.</em></p><h3>Step 4. Inventory Overview and Performance Analysis Dashboard</h3><p>The report uses two complementary pages. The first provides a high-level performance snapshot across campaigns and providers. The second supports deeper provider-level comparison and benchmarking.</p><h4>Inventory Performance Overview</h4><p>The Inventory Performance Overview page acts as the entry point to the analysis. Headline KPIs summarise spend, revenue, profit, CPA, RPA, and ROAS, giving an immediate read on yield quality.</p><p>Time-series visuals show how spend, revenue, and conversions change month by month, making shifts in performance easy to spot. Additional visuals compare inventory providers across cost, conversion efficiency, and scale, combining metrics such as CPA, conversions per impression, and impressions into a single view.</p><figure><img alt="Power BI inventory performance overview dashboard showing yield KPIs, campaign filter, monthly spend and revenue trends, and inventory provider comparison." src="https://cdn-images-1.medium.com/max/1024/1*r2PXbbFMrtVubYXUoP0Fng.png" /><figcaption>Screenshot #3. Inventory Performance Overview dashboard summarising yield, cost efficiency, and provider performance across campaigns.</figcaption></figure><h4>Yield and Inventory Performance Analysis</h4><p>The second page shifts from overview to comparison. Its purpose is to make provider-level differences explicit and support benchmarking decisions.</p><p>A provider summary matrix brings together volume, efficiency, yield, and ranking measures in one place. A campaign selector allows you to focus the analysis on a single campaign or compare providers across the full portfolio.</p><figure><img alt="Power BI yield and inventory analysis dashboard with provider comparison matrix showing CPA, ROAS, conversion efficiency, and rankings by inventory provider." src="https://cdn-images-1.medium.com/max/1024/1*Uv9uTD9abh71XThLH2aMcQ.png" /><figcaption>Screenshot #4. Yield and inventory performance analysis page with provider benchmarking, efficiency metrics, and campaign-level filtering.</figcaption></figure><p>Together, these two pages establish a clear baseline: how inventory performs historically, where yield is created or lost, and which providers drive the best outcomes.</p><h3>Step 5. Yield Performance and Inventory Cost Forecasting in Microsoft Power BI</h3><p>Up to this point, the dashboard explains historical performance. This step adds forward-looking logic so you can test how changes in conversion performance or inventory costs affect yield, profitability, and provider rankings.</p><h4>What-If Parameters for Yield Scenarios</h4><p>Create two numeric What-If parameters using <strong>Modeling → New parameter</strong>:</p><ul><li><strong>Conversion Uplift %</strong> to simulate improvements or declines in conversion efficiency</li><li><strong>Cost Inflation %</strong> to simulate changes in inventory cost</li></ul><p>These parameters act as scenario controls. Positive values represent improvement or inflation, while negative values represent deterioration or discounts.</p><p>To keep forecast logic readable, convert the parameter values into reusable multipliers:</p><pre>Conversion Multiplier =<br>1 + [Conversion Uplift % Value]</pre><p>A similar multiplier applies to cost inflation. These helpers ensure forecast measures remain consistent and easy to maintain.</p><h4>Forecasting Core Performance and Yield Metrics</h4><p>With multipliers in place, create forecast versions of the baseline measures. Forecast conversions apply the conversion multiplier, while forecast spend applies the cost multiplier. From there, efficiency and yield metrics recompute naturally using the forecasted values.</p><p>A representative example:</p><pre>Forecast Conversions =<br>[Conversions] * [Conversion Multiplier]</pre><p>Forecast spend, revenue, CPA, ROAS, profit, and margin follow the same pattern. Impressions and clicks remain unchanged in this model, allowing the forecast to focus purely on performance quality and cost pressure rather than traffic assumptions.</p><p><em>You’ll find a link to the complete forecast measure set — including forecast CPA, ROAS, RPM, RPA, profit, and margin — along the full tutorial at the end of this post.</em></p><h3>Step 6. Yield Forecast and Variance Analysis Dashboard</h3><p>This page focuses on comparison: how forecasted performance differs from actuals, which metrics move most, and which inventory providers remain resilient as assumptions change.</p><h4>Forecast vs Actual Variance Logic</h4><p>The dashboard compares forecasted and actual performance using three complementary variance types:</p><ul><li><strong>Percentage variance</strong> to show relative change for volume and financial metrics such as conversions, spend, revenue, and profit</li><li><strong>Percentage-point deltas</strong> for rate-based metrics like profit margin, CTR, CVR, and conversion efficiency, where absolute changes are more meaningful than ratios</li><li><strong>Absolute deltas</strong> to quantify the raw impact on conversions, revenue, spend, and profit</li></ul><p>Together, these perspectives make it easy to see not just <em>whether</em> performance changes, but <em>how</em> and <em>where</em> the pressure comes from.</p><h4>Yield Forecast and Variance Analysis Page</h4><p>The report page brings forecast controls and variance outputs together.</p><p>Campaign selection defines the analysis context, while the <strong>Conversion Uplift %</strong> and <strong>Cost Inflation %</strong> sliders act as scenario drivers. As assumptions change, all forecast and variance measures update instantly.</p><p>A provider-level matrix compares forecasted yield and efficiency against actuals. It highlights how inventory providers respond differently to the same cost or performance shock.</p><p>Supporting visuals show how forecasted spend and revenue shift across campaigns, helping you assess whether changes concentrate risk or rebalance value across the portfolio.</p><figure><img alt="Power BI yield forecast and variance dashboard with What-If sliders for conversion uplift and cost inflation, showing provider-level forecast versus actual yield metrics." src="https://cdn-images-1.medium.com/max/1024/1*yVRAteaX4e1dGYD0-J2WWw.png" /><figcaption>Screenshot #5. Yield forecast and variance analysis dashboard combining What-If parameters with provider-level performance and yield comparison.</figcaption></figure><h4>Using Scenarios for Yield Sensitivity Analysis</h4><p>The value of this dashboard comes from structured scenarios rather than random slider movement. Typical use cases include testing pure cost pressure, performance deterioration paired with cheaper inventory, or higher costs offset by improved conversion efficiency.</p><p>Reviewing variance side by side across providers quickly reveals which inventory sources sustain yield under stress and which ones break first.</p><h3>Continue with the full tutorial and explore the model</h3><p>This article is a condensed walkthrough of a yield management and inventory analysis model built in Microsoft Power BI. The full tutorial covers every step in detail, including the complete DAX measure library and a preview of the finished yield and inventory analysis dashboard.</p><p>You’ll see how campaign performance, inventory costs, conversion efficiency, yield metrics, and forecast assumptions fit together in a single, reusable model. You can also download the full Microsoft Power BI file to adapt it to your own data or client work.</p><p>👉 <a href="https://www.challengejp.com/blog/power-bi-yield-management-inventory-analysis-dashboard-tutorial/"><strong>Full Yield Management &amp; Inventory Analysis Dashboard Tutorial</strong></a></p><p>There you can preview the interactive yield management and inventory analysis dashboard, explore the full DAX measure library behind the model, and download the complete Power BI template to use in your own projects or client work (<strong>20% off with code MEDIUM</strong>).</p><h3>Related posts</h3><ul><li><a href="https://medium.com/challengejp/creating-a-customer-churn-dashboard-in-10-steps-5ccd8ea7fe7b"><strong><em>Creating a Customer Churn and Revenue Forecast Dashboard</em></strong></a></li><li><a href="https://medium.com/challengejp/building-a-marketing-performance-forecast-dashboard-in-8-steps-4d464e5cdf4b"><strong><em>Building a Marketing Performance &amp; Forecast Dashboard</em></strong></a></li></ul><h3>About the Author</h3><p>Hi, I’m Jacek. I work with analytics, performance modelling, and forecasting, and I enjoy helping teams turn campaign and inventory data into clear, decision-ready Power BI models.</p><p>If you’re building yield management, marketing analytics, inventory optimisation, or forecasting models in Microsoft Power BI, you’ll find more tutorials, templates, and resources at <a href="https://www.challengejp.com/"><strong>challengejp.com</strong></a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/0*UMC0uIqUjLq12Mlj.jpeg" /></figure><p><em>Disclaimer: This tutorial is for educational purposes only and does not constitute financial, accounting, or professional advice.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e448ab1e5861" width="1" height="1" alt=""><hr><p><a href="https://medium.com/challengejp/building-a-yield-management-and-inventory-analysis-dashboard-in-6-steps-e448ab1e5861">Building a Yield Management and Inventory Analysis Dashboard in 6 Steps</a> was originally published in <a href="https://medium.com/challengejp">ChallengeJP</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a Project Finance & Revenue Dashboard in 8 Steps]]></title>
            <link>https://medium.com/challengejp/building-a-project-finance-revenue-dashboard-in-8-steps-68a151c901c1?source=rss----51290e855bf9---4</link>
            <guid isPermaLink="false">https://medium.com/p/68a151c901c1</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[dashboard]]></category>
            <category><![CDATA[project-finance]]></category>
            <category><![CDATA[financial-modelling]]></category>
            <category><![CDATA[dax]]></category>
            <dc:creator><![CDATA[Jacek Polewski]]></dc:creator>
            <pubDate>Sun, 01 Feb 2026 18:19:43 GMT</pubDate>
            <atom:updated>2026-02-01T18:20:50.152Z</atom:updated>
            <content:encoded><![CDATA[<p>In this tutorial, I’ll show you how to build a project finance and revenue recognition model in Power BI using project, CAPEX, and billing data. We’ll focus on capital-intensive projects where construction costs precede contractual revenue, starting with data validation and a robust calendar model.</p><p>From there, we develop the DAX measures for milestone based revenue, contract assets, and liabilities. At the end, the tutorial layers in project financing logic covering debt drawdowns, interest, and levered cash flows before using What If parameters to run sensitivity analysis across your key assumptions.</p><p><em>(All screenshots and examples come from the full tutorial, which you can access — along with the sample data, DAX code, and templates — via the link at the end.)</em></p><h3>Step 1. Loading and Reviewing Project Finance Data in Microsoft Power BI</h3><p>Start by loading the core project finance and revenue datasets into Microsoft Power BI Desktop. This step focuses on structure and validation, not transformation. The goal is to understand each table and confirm the data supports time-based analysis later in the model.</p><p>The example data (you can find a link to the source files at the end of this post) represents capital-intensive projects delivered under long-term customer contracts. CAPEX occurs during construction, while billing and revenue recognition follow different timelines. Although the scenario uses telecom-style infrastructure, the model applies to any long-lived project where financing depends on progress rather than static budgets.</p><p>Load the CSV files using <strong>Home → Get Data → Text/CSV</strong>:</p><ul><li><strong>dim_projects</strong> — project definitions and milestone dates such as Ready for Service (RFS), which later drive milestone revenue recognition and financing logic</li><li><strong>dim_contracts</strong> — customer contracts linked to projects, including terms, transaction value, and milestone versus service allocation</li><li><strong>fct_capex</strong> — monthly forecast CAPEX by project, driving construction cash outflows, cumulative investment, and debt drawdowns</li><li><strong>fct_billing</strong> — customer invoices over time, kept separate from revenue recognition to expose contract assets and liabilities</li></ul><p>Open each table in <strong>Power Query</strong> and review data types. Set all date fields to <strong>Date</strong> and all financial and percentage fields to <strong>Decimal Number</strong>. Confirm that allocation percentages use decimal values rather than whole numbers.</p><p>Before closing Power Query, scan for missing key dates, unexpected blanks, or timelines that do not align with the project scenario. When the data looks consistent, select <strong>Close &amp; Apply</strong>.</p><h3>Step 2. Building a DAX Calendar Table and a Data Model</h3><p>With the data loaded, the next step is to make it usable for analysis. Before any revenue recognition or project finance logic, the model needs two foundations: a proper Calendar table and a clean, explicit set of relationships.</p><p>The Calendar table provides a single time axis across projects, contracts, CAPEX, billing, and financing. The data model then defines how filters flow by project, contract, customer, and time. Getting this right early avoids ambiguity later, especially once cumulative calculations and forecasts come into play.</p><h4>Create the Calendar Table</h4><p>Create a new table using DAX and name it <strong>Calendar</strong>. This table scans all relevant date columns in the model to determine the full project and contract timeline, avoiding hardcoded boundaries.</p><p>After creating the table, sort <strong>Quarter</strong> by <em>QuarterNumber</em> and <strong>YearMonth</strong> by <em>MonthStart</em>. Then mark the table as a Date table using the <strong>Date</strong> column. This ensures time intelligence behaves correctly and prevents Microsoft Power BI from creating hidden date tables.</p><p><em>You’ll find a link to the Calendar DAX and other measures at the end of this post.</em></p><h4>Define Model Relationships</h4><p>With the Calendar table in place, define the relationships that connect projects, contracts, CAPEX, billing, and time. Create all relationships manually to keep filter behaviour predictable.</p><p>Use one-to-many relationships with single-direction filtering throughout:</p><p><strong>Calendar relationships</strong></p><ul><li>Calendar[Date] → fct_capex[MonthStart]</li><li>Calendar[Date] → fct_billing[InvoiceDate]</li></ul><p><strong>Project and contract relationships</strong></p><ul><li>dim_projects[ProjectID] → fct_capex[ProjectID]</li><li>dim_projects[ProjectID] → dim_contracts[ProjectID]</li><li>dim_contracts[ContractID] → fct_billing[ContractID]</li></ul><figure><img alt="Data model diagram showing a calendar table connected to projects, contracts, CAPEX, and billing for project finance and revenue recognition analysis." src="https://cdn-images-1.medium.com/max/1024/1*AXvOxBf6WYF3da51qjJtcA.png" /><figcaption>Screenshot #1. A completed data model linking projects, contracts, CAPEX, and billing through a central Calendar table.</figcaption></figure><p>This structure ensures that time filters apply consistently across CAPEX and billing, contract activity stays tied to the correct project context, and cumulative calculations behave as expected.</p><h3>Step 3. Create Core DAX Measures for CAPEX and Contract Values</h3><p>It’s now time to define the core DAX measures that anchor the rest of the dashboard — CAPEX and contract values.</p><p>These measures stay deliberately simple. Their job is to provide reliable building blocks that can be reused later for revenue recognition, debt forecasting, cash flow, and valuation.</p><h4>CAPEX Measures</h4><p>CAPEX drives the construction timeline and later determines financing needs. These measures track forecast investment, cumulative spend, and variance to budget.</p><pre>CAPEX Amount =<br>SUM ( fct_capex[Amount] )<br><br>Project CAPEX (Forecast) =<br>CALCULATE ( [CAPEX Amount], fct_capex[Scenario] = &quot;Forecast&quot; )<br>Project CAPEX Cumulative Forecast :=<br>VAR AsOfMonth = MAX ( Calendar[MonthStart] )<br>RETURN<br>SUMX (<br>    FILTER ( ALL ( Calendar ), Calendar[MonthStart] &lt;= AsOfMonth ),<br>    CALCULATE ( [Project CAPEX (Forecast)] )<br>)<br><br>Project CAPEX Budget =<br>SUM ( dim_projects[BudgetCAPEX] )<br><br>Project CAPEX Variance to Budget (Forecast) =<br>[Project CAPEX Cumulative Forecast] - [Project CAPEX Budget]</pre><p>These measures establish how much capital the project absorbs over time and whether forecast spend stays within the original budget envelope.</p><h4>Contract Value and Allocation Measures</h4><p>Next, define the commercial value of each project through its customer contracts. These measures determine how total contract value splits between milestone and service revenue, which becomes critical for revenue recognition later.</p><pre>Project Transaction Price =<br>SUM ( dim_contracts[TotalContractValue] )<br><br>Project Milestone Allocation =<br>SUMX (<br>    dim_contracts,<br>    dim_contracts[TotalContractValue] * dim_contracts[MilestonePct]<br>)<br><br>Project Service Allocation =<br>SUMX (<br>    dim_contracts,<br>    dim_contracts[TotalContractValue] * dim_contracts[ServicePct]<br>)<br><br>Projects Count =<br>DISTINCTCOUNT ( dim_projects[ProjectID] )</pre><p>At this stage, these measures do not recognise revenue or apply timing logic. They simply describe the total economic value attached to each project and how that value is structured contractually.</p><h3>Step 4. Building the Core Power BI Report Pages</h3><p>The purpose of these pages is validation rather than deep analysis — confirming that CAPEX, contract values, billing, and timelines behave correctly before adding revenue recognition and project finance logic.</p><p>Two report pages are created in this step. A <strong>Projects Overview</strong> page provides a portfolio-level snapshot, while a <strong>Projects Detail</strong> page supports validation at the individual project and contract level.</p><h4>Projects Overview</h4><p>The <strong>Projects Overview</strong> page provides a high-level view of portfolio scale and composition, balancing capital deployment against contracted value. By centralizing headline KPIs like <strong>CAPEX Forecast</strong>, <strong>Transaction Price</strong>, and <strong>Milestone/Service Allocations</strong>, the dashboard establishes the total size and structural makeup of active projects at a glance.</p><p>To visualize project-level health, a clustered bar chart compares investment costs directly against contract prices, identifying where margins might be thin. These metrics are complemented by a customer treemap to highlight revenue concentration, while dual time-series charts reveal the critical relationship between monthly billing cycles and construction spend.</p><figure><img alt="Power BI projects overview dashboard showing project CAPEX, contract value, customer concentration, billing, and monthly investment for project finance and revenue recognition analysis." src="https://cdn-images-1.medium.com/max/1024/1*ZKnLV5s7hL_nqTt7Lbmn4w.png" /><figcaption>Screenshot #2. A Projects Overview page summarising project CAPEX, contract value, customers, billing, and monthly spend.</figcaption></figure><h4>Projects Detail</h4><p>The Projects Detail page complements the overview with a more granular, project-level view. It brings together project attributes, contract timelines, CAPEX budgets, and value allocations in a single table.</p><p>This page is primarily a validation tool. It allows you to confirm that RFS dates, contract start and end dates, budgets, and allocation percentages align correctly before those fields drive revenue recognition and financing calculations in later steps.</p><figure><img alt="Power BI project detail table showing contracts, RFS dates, CAPEX budgets, total contract value, and revenue allocation by project." src="https://cdn-images-1.medium.com/max/1024/1*QAyU8u7ZaQLOJ39_8eS_dA.png" /><figcaption>Screenshot #3. A Projects Detail page combining project, contract, timeline, and value data for validation.</figcaption></figure><p>If anything looks off at this stage, it will almost always surface later as distorted revenue timing or financing results. Resolving those issues here keeps the rest of the model predictable.</p><h3>Step 5. Revenue Recognition (Milestone and Service)</h3><p>This step introduces revenue recognition. The key distinction is between <strong>billing</strong>, which reflects cash timing, and <strong>revenue</strong>, which reflects economic performance. In long-lived projects, those two rarely move together, so the model keeps them separate from the start.</p><p>In addition, my implements two revenue recognition patterns:</p><ul><li><strong>Milestone revenue</strong> — recognised at a specific point in time, typically when a project reaches <em>Ready for Service (RFS)</em>.</li><li><strong>Service revenue — </strong>recognised evenly over the contract term.</li></ul><p>Together, they produce a revenue profile that remains consistent as you filter by project, contract, or customer.</p><h4>Milestone Revenue Recognition</h4><p>Milestone revenue recognises the milestone portion of each contract once the project reaches its RFS date. From that point onward, the revenue remains fully recognised.</p><p>A representative cumulative measure looks like this:</p><pre>Project Revenue Recognized (Milestone, Cumulative) :=<br>VAR AsOf = MAX ( Calendar[Date] )<br>RETURN<br>SUMX (<br>    dim_contracts,<br>    VAR RFS = RELATED ( dim_projects[RFSDate] )<br>    VAR Amt = dim_contracts[TotalContractValue] * dim_contracts[MilestonePct]<br>    RETURN<br>        IF ( NOT ISBLANK ( RFS ) &amp;&amp; AsOf &gt;= RFS, Amt, 0 )<br>)</pre><p>This measure evaluates each contract independently, checks whether the reporting date falls after RFS, and recognises the milestone portion accordingly. Period-based revenue uses a standard cumulative-to-period pattern derived from this logic.</p><p>Service revenue follows the same structure but spreads the allocated value evenly between the contract start and end dates. Billing measures remain fully separate and accumulate invoice amounts independently of recognised revenue.</p><p><em>You’ll find a link to the full set of revenue recognition, billing, and reconciliation DAX measures, along with the complete tutorial, at the end of this post.</em></p><h4>Revenue Recognition Visuals</h4><p>The report page focuses on three complementary views: monthly recognised revenue versus billing, milestone versus service revenue over time, and cumulative recognised revenue across the project lifecycle. Together, these visuals make timing differences explicit and show how contract assets and liabilities build up and unwind.</p><figure><img alt="Power BI revenue recognition dashboard showing milestone and service revenue compared with customer billing over time for project finance analysis." src="https://cdn-images-1.medium.com/max/1024/1*B05UHilPrXqUmiz3HydJ4A.png" /><figcaption>Screenshot #4. A revenue recognition page comparing milestone and service revenue with customer billing and cumulative contract positions.</figcaption></figure><p>At this point, the model clearly separates construction progress, accounting revenue, and invoicing.</p><h3>Step 6. Project Finance and Debt Forecast Modelling</h3><p>With revenue recognition in place, the model now shifts from accounting to <strong>project finance mechanics</strong>. In this step, capital investment drives debt funding, project milestones anchor repayment timing, and assumptions remain fully adjustable through parameters.</p><p>The goal is not to recreate a bank-grade loan model. Instead, the focus stays on building a <strong>transparent, time-aware debt forecast</strong> that reacts correctly to CAPEX timing, project completion, and financing assumptions.</p><h4>Financing Assumptions with What-If Parameters</h4><p>The model uses What-If parameters to control the main sources of uncertainty in project finance. These parameters define how much CAPEX is funded with debt, the cost of borrowing, and how repayments unfold after the project reaches Ready for Service.</p><p>Typical parameters include debt leverage, interest rate, repayment tenor, and repayment delay. Each parameter feeds into dedicated assumption measures, which keeps the financing DAX readable and consistent across the model.</p><h4>Debt Draw, Repayment, and Interest Logic</h4><p>Debt drawdowns scale directly with forecast CAPEX. As construction spend ramps up, the model draws debt in proportion to the selected leverage assumption. Once the project reaches RFS, repayments begin after any defined delay and amortise evenly over the selected tenor.</p><p>Interest expense follows naturally from this structure. Each month, the model calculates interest on the prior-period outstanding balance, so changes in leverage, interest rate, or repayment timing flow through automatically.</p><p>This approach keeps financing behaviour intuitive — more CAPEX increases debt, longer tenors smooth repayments, and higher interest rates immediately affect cash flow and returns.</p><h4>Project Financing Dashboard</h4><p>The project financing page brings these mechanics together in a single view. Parameter sliders make assumptions explicit, while charts show how debt is drawn during construction and repaid once the project becomes operational.</p><p>Key visuals include monthly debt draw versus repayment, cumulative CAPEX versus outstanding debt, and monthly interest expense. A project selector allows you to analyse financing profiles at the individual project level without changing the underlying logic.</p><figure><img alt="Power BI project finance dashboard showing debt drawdowns, repayments, interest expense, and outstanding debt driven by leverage and interest assumptions." src="https://cdn-images-1.medium.com/max/1024/1*K0zimuMpDCy953mCzMb-ig.png" /><figcaption>Screenshot #5. Project financing dashboard illustrating debt funding, repayment schedules, and interest expense across the project lifecycle.</figcaption></figure><p>By the end of this step, the model produces a complete, assumption-driven debt forecast that stays aligned with project timelines and investment patterns.</p><h3>Step 7. Cash Flow, Returns, and Project Finance Metrics</h3><p>With revenue, CAPEX, and financing logic in place, the model now shifts to an <strong>investment view of the project</strong>. This step connects billing, operating costs, CAPEX, and debt into a single cash-flow timeline, making it possible to evaluate liquidity, profitability, and returns.</p><p>The objective is to answer practical project finance questions: when the project consumes cash, when it turns cash-positive, how leverage changes the equity profile, and whether returns justify the risk.</p><h4>Unlevered and Levered Cash Flow</h4><p>Unlevered cash flow shows project performance before financing. It combines customer billing, operating costs, and CAPEX to test whether the project works on its own economics.</p><p>Levered cash flow reflects the equity investor’s view. It adds debt drawdowns, subtracts repayments, and includes interest expense. This distinction allows you to compare pure project economics with the effect of financing structure.</p><h4>Earnings, EBITDA, and Profitability</h4><p>Operating expenses scale with service revenue using a parameterised OPEX assumption. From there, the model calculates EBITDA on a monthly basis and also produces a trailing-twelve-month view to smooth volatility.</p><p>These earnings measures later feed directly into leverage ratios, coverage metrics, and credit analysis, so they remain consistent across reporting and scenario testing.</p><h4>Returns: NPV, IRR, and Payback</h4><p>The model calculates NPV and IRR for both unlevered and levered cash flows. Valuation logic runs independently of the report’s visible timeline, ensuring results do not change when users zoom charts or adjust date granularity.</p><h4>Cash Flow and Returns Dashboard</h4><p>The Cash Flow and Returns page consolidates these metrics into a decision-ready view. Headline cards summarise NPV, IRR, and payback for both unlevered and levered cases. Charts show monthly and cumulative cash flows, making funding gaps and recovery points immediately visible.</p><p>Parameter sliders allow you to stress-test assumptions such as operating cost intensity and discount rate, while a project selector switches context without altering the underlying logic.</p><figure><img alt="Power BI cash flow and returns dashboard showing unlevered and levered cash flows, cumulative balances, NPV, IRR, and payback for project finance analysis." src="https://cdn-images-1.medium.com/max/1024/1*w5m5sLwSUvoZAkIv477M4A.png" /><figcaption>Screenshot #6. Cash flow and returns dashboard comparing unlevered and levered performance, cumulative cash balances, and key investment metrics.</figcaption></figure><p>By the end of this step, the model provides a complete economic picture of each project.</p><h3>Step 8. Debt Service, Credit Metrics, and Scenario Analysis</h3><p>Let’s now look on how the reporting dashboard helps assess whether a financing structure remains viable as assumptions change.</p><h4>Debt Service and Credit Metrics</h4><p>The model translates financing and cash-flow outputs into standard project finance credit metrics. These measures describe risk and resilience rather than absolute performance.</p><p>Leverage ratios compare outstanding debt to invested equity and trailing EBITDA. Coverage metrics evaluate how comfortably earnings or cash flow service interest and principal obligations. Together, they highlight whether the project can support its capital structure across the operating phase.</p><p>Because all metrics reference the same underlying debt and cash-flow logic, they update immediately when you adjust leverage, interest rate, repayment tenor, or operating cost assumptions.</p><h4>Financial Summary Dashboard</h4><p>The Financial Summary page consolidates the model into a compact decision view. One matrix summarises annual revenue, earnings, CAPEX, and cash flows. A second matrix focuses on debt balances, debt service, and credit ratios such as Debt-to-Equity and DSCR.</p><p>This layout mirrors how projects are reviewed in practice. It allows you to assess performance, funding, and risk side by side without switching between pages or visuals.</p><figure><img alt="Financial summary dashboard showing annual cash flow, EBITDA, debt service, DSCR, and leverage metrics for project finance analysis." src="https://cdn-images-1.medium.com/max/1024/1*MWino3DlRD4ABWuSuUj9aA.png" /><figcaption>Screenshot #7. A financial summary dashboard combining annual performance, debt service, and credit metrics for project finance evaluation.</figcaption></figure><h4>Scenario and Sensitivity Analysis</h4><p>With the summary view in place, the final task is structured scenario testing. Rather than adjusting parameters randomly, define a small number of coherent cases and observe how risk compounds through the model.</p><p>A base case establishes whether the project works on an unlevered basis. A levered case tests whether financing improves equity returns without overstressing coverage ratios. Stress scenarios, such as higher interest rates or operating costs, expose where leverage stops adding value.</p><h3>Continue with the full tutorial and explore the model</h3><p>This article is a condensed walkthrough of a project finance and revenue recognition model built in Microsoft Power BI. The full tutorial covers every step in detail, including the complete DAX measure library and a preview of the finished project finance dashboard.</p><p>You’ll see how CAPEX, contracts, revenue recognition, debt, and cash flows fit together in a single, time-aware model, and you can download the full Microsoft Power BI file to adapt it to your own data or client work.</p><p>👉 <a href="https://www.challengejp.com/blog/power-bi-project-financing-revenue-recognition-dashboard-tutorial/"><strong>Full Project Finance &amp; Revenue Recognition Dashboard Tutorial</strong></a></p><p>There you can preview the interactive project finance and revenue recognition dashboard, explore the full DAX measure library behind the model, and download the complete Power BI template to use in your own projects or client work (<strong>20% off with code MEDIUM</strong>).</p><h3>Related posts</h3><ul><li><a href="https://medium.com/challengejp/building-a-project-profitability-and-cost-allocation-dashboard-in-8-steps-8645f4c42885">Building a Project Cost and Profitability Dashboard</a></li><li><a href="https://medium.com/challengejp/how-to-create-a-project-finance-model-in-9-steps-9b7415b0ab11">Building a Project Finance Model in Microsoft Excel</a></li></ul><h3>About the Author</h3><p>Hi, I’m Jacek. I work with financial modelling, project finance, forecasting, and analytics, and I enjoy helping teams turn complex financial data into clear, decision-ready Power BI models.</p><p>If you’re building project finance, revenue recognition, FP&amp;A, or forecasting models in Microsoft Power BI, you’ll find more tutorials, templates, and resources at <a href="https://www.challengejp.com/">challengejp.com</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/0*NsmQwZCEBJlFVaLu.jpeg" /></figure><p><em>Disclaimer: This tutorial is for educational purposes only and does not constitute financial, accounting, or professional advice.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=68a151c901c1" width="1" height="1" alt=""><hr><p><a href="https://medium.com/challengejp/building-a-project-finance-revenue-dashboard-in-8-steps-68a151c901c1">Building a Project Finance &amp; Revenue Dashboard in 8 Steps</a> was originally published in <a href="https://medium.com/challengejp">ChallengeJP</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a Financial Planning and Analysis Dashboard in 8 Steps]]></title>
            <link>https://medium.com/challengejp/building-a-financial-planning-and-analysis-dashboard-in-8-steps-9217e7c8c175?source=rss----51290e855bf9---4</link>
            <guid isPermaLink="false">https://medium.com/p/9217e7c8c175</guid>
            <category><![CDATA[financial-planning]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[financial-modeling]]></category>
            <dc:creator><![CDATA[Jacek Polewski]]></dc:creator>
            <pubDate>Sun, 04 Jan 2026 12:12:26 GMT</pubDate>
            <atom:updated>2026-01-05T13:17:56.831Z</atom:updated>
            <content:encoded><![CDATA[<p>In this tutorial, I’ll show how to build a complete FP&amp;A dashboard in <strong>Microsoft Power BI</strong> using a realistic dataset that includes daily profit and loss activity, monthly cash flow and balance sheet data, and a detailed payroll roster. The result is a model that supports both historical analysis and forward-looking financial planning.</p><p>The walkthrough covers data loading and modeling, the creation of financial dimensions with DAX, and the development of core actuals and forecast measures that reflect how to plan and analyse financial performance.</p><p><em>(All screenshots and examples come from the full tutorial, which you can access — along with the sample data, DAX code, and templates — via the link at the end.)</em></p><h3>Step 1. Loading and Exploring the FP&amp;A Data in Microsoft Power BI</h3><p>Begin by loading the core FP&amp;A datasets into Microsoft Power BI Desktop. The files represent a growing company operating throughout 2025 and include daily profit and loss activity, monthly cash flow and balance sheet data, and a detailed payroll roster. Together, they form a realistic foundation for financial analysis and planning.</p><p>Use <strong>Home → Get Data → Text/CSV</strong> to import the daily P&amp;L ledger, monthly cash flow statement, balance sheet, and payroll roster. At this stage, focus on validation rather than transformation. Confirm that dates are recognised correctly, numeric fields use the appropriate data types, and key columns such as accounts, categories, and employee identifiers are consistent.</p><p>My sample balance sheet arrives in a wide format, with each line item stored as a separate column. To convert it to a long format is more flexible, open Transform Data, select the balance sheet table, and unpivot all columns except Month. Rename the resulting fields to Category and Amount, set the correct data types, and apply the changes.</p><figure><img alt="Power Query Editor unpivoting balance sheet data." src="https://cdn-images-1.medium.com/max/1024/1*s6kbDfqgosZSCWEaDZQjJg.png" /><figcaption>Screenshot #1. Power Query Editor showing the balance sheet transformed from wide to long format using Unpivot Other Columns.</figcaption></figure><p>Before continuing, disable automatic relationship detection. Go to File → Options and settings → Options → Current File → Data Load and uncheck <strong>Autodetect new relationships after data is loaded</strong>. All relationships will be created manually to keep the model explicit and predictable.</p><h3>Step 2. Creating a Dynamic Calendar Table for Financial Modelling</h3><p>A dedicated calendar table is essential for FP&amp;A work. Every measure in the model — whether it comes from the profit and loss, cash flow, balance sheet, or forecast — needs to align to a single, consistent time dimension. Relying on dates embedded in fact tables quickly leads to inconsistent results, especially once forecasting logic is introduced.</p><p>In this step, the calendar is created using DAX so it adapts automatically to the data. The table detects the first and last dates from the P&amp;L actuals and then extends the timeline five years beyond the last actual month. This provides enough runway for multi-year planning without any manual updates later.</p><p>In <strong>Model view</strong>, select <strong>New table</strong> and add the following DAX:</p><pre>Calendar =<br>VAR _MinDate =<br>    MINX ( ALL ( pnl_daily_2025 ), pnl_daily_2025[Date] )<br>VAR _MaxActualDate =<br>    MAXX ( ALL ( pnl_daily_2025 ), pnl_daily_2025[Date] )<br>RETURN<br>ADDCOLUMNS (<br>    CALENDAR ( _MinDate, DATE ( YEAR ( _MaxActualDate ) + 5, 12, 31 ) ),<br>    &quot;Year&quot;,         YEAR ( [Date] ),<br>    &quot;MonthNo&quot;,      MONTH ( [Date] ),<br>    &quot;Month&quot;,        FORMAT ( [Date], &quot;MMMM&quot; ),<br>    &quot;Quarter&quot;,      &quot;Q&quot; &amp; FORMAT ( [Date], &quot;Q&quot; ),<br>    &quot;YearMonth&quot;,    FORMAT ( [Date], &quot;YYYY-MM&quot; ),<br>    &quot;YearMonthNum&quot;, YEAR ( [Date] ) * 100 + MONTH ( [Date] ),<br>    &quot;MonthStart&quot;,   DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ),<br>    &quot;MonthEnd&quot;,     EOMONTH ( [Date], 0 ),<br>    &quot;IsActual&quot;,     [Date] &lt;= _MaxActualDate,<br>    &quot;IsForecast&quot;,  [Date] &gt;  _MaxActualDate<br>)</pre><p>This table creates a continuous date range, adds the standard attributes needed for financial reporting, and introduces two flags that separate historical results from forecast periods. Those flags will later control which measures appear on actuals pages and which belong to planning and forecasting.</p><p>Once the table is created, right-click it, choose <strong>Mark as date table</strong>, and select the <em>Date</em> column. This step ensures Microsoft Power BI treats the table as the primary time dimension and enables full time-intelligence functionality.</p><h3>Step 3. Building the Financial Data Model for FP&amp;A Reporting</h3><p>With the data loaded and the calendar table in place, the next step is to assemble the financial data model. In FP&amp;A work, a clean structure is critical: it ensures actuals aggregate correctly, forecasts behave predictably, and financial statements stay internally consistent as assumptions change.</p><p>The model follows a classic star-style design. Central fact tables hold transactional and monthly financial data, while supporting dimensions provide structure for slicing, grouping, and time-based analysis. The Calendar table sits at the centre, driving all time intelligence and defining the boundary between actual and forecast periods.</p><figure><img alt="Financial star schema with calendar and FP&amp;A fact tables." src="https://cdn-images-1.medium.com/max/1024/1*sE2soxPPr9hcMRfuw43BZg.png" /><figcaption>Screenshot #2. Final FP&amp;A data model showing the Calendar table connected to profit and loss, cash flow, balance sheet, and payroll fact tables using one-to-many relationships.</figcaption></figure><p>Four fact tables form the core of the model. Daily profit and loss data captures detailed revenue and cost activity, monthly cash flow and balance sheet tables provide period-end movements and positions, and the payroll roster supplies employee-level information used later for headcount and payroll planning.</p><h3>Step 4. Creating Financial Dimensions Using DAX</h3><p>With the core data model in place, the next step is to add a set of financial dimensions that shape how the statements are presented and analysed. These tables do not add new data; instead, they impose structure, ordering, and hierarchy on the raw financial facts.</p><p>In FP&amp;A reporting, this step is critical. Financial statements need to appear in a consistent accounting order, balance sheet lines must roll up cleanly into Assets, Liabilities, and Equity, and cash flow categories should always follow the same operating–investing–financing structure. Creating these dimensions in DAX gives full control over that behaviour.</p><p>One example is the balance sheet category dimension. This table defines each balance sheet line, assigns it to a section, and specifies a display order so visuals always follow standard accounting conventions.</p><pre>DimBS_Category =<br>DATATABLE (<br>    &quot;Category&quot;, STRING,<br>    &quot;Section&quot;, STRING,<br>    &quot;DisplayOrder&quot;, INTEGER,<br>    {<br>        { &quot;Cash&quot;,                &quot;Assets&quot;,      10 },<br>        { &quot;Accounts Receivable&quot;, &quot;Assets&quot;,      20 },<br>        { &quot;Net PPE&quot;,             &quot;Assets&quot;,      30 },<br>        { &quot;Accounts Payable&quot;,    &quot;Liabilities&quot;, 60 },<br>        { &quot;Debt&quot;,                &quot;Liabilities&quot;, 70 },<br>        { &quot;Paid-in Capital&quot;,     &quot;Equity&quot;,      90 },<br>        { &quot;Retained Earnings&quot;,   &quot;Equity&quot;,     100 }<br>    }<br>)</pre><p>Once created, the <em>Category</em> column is sorted by <em>DisplayOrder</em> and linked to the balance sheet fact table using a one-to-many, single-direction relationship. This ensures that balance sheet matrices always render in the correct order, regardless of filtering or drill-down.</p><p>The same pattern is applied to the profit and loss and cash flow statements. Additional DAX dimensions define P&amp;L subcategories and accounts, enforce financial statement ordering, and stabilise cash flow categories across operating, investing, and financing activities. A lightweight department dimension derived from the payroll roster supports headcount and payroll analysis by functional area.</p><p><em>Note that you will find a link to the complete set of financial dimensions and supporting DAX at the end of this post.</em></p><h3>Step 5. Developing Core Financial Measures with DAX</h3><p>With the data model and financial dimensions in place, the next step is to give the model analytical meaning. In FP&amp;A work, this happens through measures: they translate raw ledger entries, payroll records, and balance sheet positions into financial results that can be analysed, compared, and forecast.</p><p>To keep things organised, all measures are stored in a dedicated table. In Report view, use <strong>Home → Enter Data</strong>, name the table <em>Measures_Table</em>, and leave it empty. This table acts purely as a container, making it easier to manage and navigate a growing measure library as the model evolves.</p><h4>Core Actuals Measures</h4><p>The first group of measures establishes actual financial performance across the profit and loss. These calculations pull directly from the daily P&amp;L ledger and form the foundation for all later analysis and forecasting logic. Standard DAX patterns such as SUM, CALCULATE, ALL, and KEEPFILTERS ensure values aggregate correctly across accounts and time.</p><p>Below is a representative subset showing how revenue, costs, payroll, and earnings are derived:</p><pre>PL Amount =<br>SUM ( pnl_daily_2025[Amount] )<br><br>Revenue =<br>CALCULATE (<br>    [PL Amount],<br>    ALL ( DimPL_Account ),<br>    DimPL_Account[Account] = &quot;Revenue&quot;<br>)<br>COGS =<br>CALCULATE (<br>    [PL Amount],<br>    ALL ( DimPL_Account ),<br>    DimPL_Account[Account] = &quot;COGS&quot;<br>)<br>Opex =<br>CALCULATE (<br>    [PL Amount],<br>    ALL ( DimPL_Account ),<br>    DimPL_Account[Account] = &quot;Opex&quot;<br>)<br>Payroll Cost =<br>CALCULATE (<br>    [PL Amount],<br>    KEEPFILTERS (<br>        DimPL_Subcategory[Subcategory]<br>            = &quot;Payroll (incl. taxes/benefits/bonus accrual)&quot;<br>    )<br>)<br>Gross Profit =<br>[Revenue] + [COGS]Revenue =<br>CALCULATE (<br>    [PL Amount],<br>    ALL ( DimPL_Account ),<br>    DimPL_Account[Account] = &quot;Revenue&quot;<br>)</pre><p>These measures define the core structure of the profit and loss statement and are reused extensively throughout the report. Once they exist, additional indicators — such as cost per employee, revenue per employee, and margin ratios — can be layered on without changing the underlying logic.</p><p>Similar patterns are applied to cash flow and balance sheet measures. Cash flow calculations group operating, investing, and financing activity, while balance sheet measures compute end-of-period positions for assets, liabilities, and equity using month-end snapshots.</p><h3>Step 6. Actuals Analysis and Financial Reporting</h3><p>With the actuals measures in place, the focus shifts to presentation. This step turns the model into a familiar FP&amp;A reporting pack, combining the profit and loss, cash flow, and balance sheet with interactive analysis that makes trends easier to explore.</p><p>All visuals on these pages are filtered to historical data using Calendar[IsActual] = True, keeping the story anchored in actual performance.</p><h4>Financial Statements Overview</h4><p>The profit and loss matrix presents monthly results in standard statement order, showing revenue, costs, payroll, depreciation, and net income in a compact, readable layout. The cash flow matrix summarises operating, investing, and financing activity, making it easy to compare accounting profit with cash generation. The balance sheet completes the picture with month-end assets, liabilities, and equity, structured using the custom balance sheet dimension.</p><figure><img alt="Financial statements dashboard with profit and loss, cash flow, and balance sheet." src="https://cdn-images-1.medium.com/max/1024/1*P4zkq3lC2vtI8wvddwS6aQ.png" /><figcaption>Screenshot #3. Financial statements dashboard displaying the profit and loss statement, cash flow statement, and balance sheet in a unified Power BI layout.</figcaption></figure><h4>Analytical Views</h4><p>The second page focuses on interpretation rather than structure. A condensed monthly P&amp;L highlights trends over time, while an operating expense breakdown quickly shows which cost categories drive total spend. Headcount visuals add an operational lens, revealing how staffing levels shift by department and how payroll relates to revenue and productivity.</p><figure><img alt="FP&amp;A analysis page with P&amp;L trends, opex breakdown, and headcount analysis." src="https://cdn-images-1.medium.com/max/1024/1*Y1U9CVgznNKTRQWdsoYhkg.png" /><figcaption>Screenshot #4. FP&amp;A analysis page showing monthly profit and loss trends, operating expense breakdown, headcount by department, and per-employee productivity metrics.</figcaption></figure><p>At this stage, the report provides a complete view of historical financial performance. The next step introduces planning logic and forecasting assumptions that extend the model beyond actuals.</p><h3>Step 7. Financial Planning and Forecasting in Microsoft Power BI</h3><p>This step moves the model from reporting into planning. The goal is to layer a forward-looking FP&amp;A engine on top of the actuals, allowing revenue, costs, payroll, capital expenditure, and financing to be projected using a consistent set of assumptions.</p><p>Rather than hard-coding forecasts, the model is driven by parameters. These act as adjustable planning inputs, making it possible to test scenarios and immediately see how changes in growth, cost inflation, hiring, or financing affect the full set of financial statements.</p><h4>Planning Parameters and Forecast Logic</h4><p>The first building block is a set of What-If parameters. These define assumptions such as revenue growth, cost inflation, salary increases, headcount changes, tax rates, capital expenditure, and debt terms. Each parameter feeds a simple helper measure that exposes the selected value for use in DAX.</p><p>Once the assumptions are in place, the model establishes a clear boundary between actuals and forecasts. Measures identify the last actual month and ensure that forecast logic only applies to future periods, while historical results remain untouched.</p><p>Forecasts are then built incrementally. Revenue and operating costs start from recent actual averages and grow forward using the selected assumptions, spreading changes smoothly across each forecast year. Headcount and payroll projections follow the same approach, combining growth rates with monthly ramping to avoid artificial step changes.</p><p>Below is a simplified example showing how a forecasted revenue measure builds on recent actuals and applies a growth assumption:</p><pre>Revenue Forecast (only) =<br>VAR _growth = [Revenue %]<br>VAR _base   = [Revenue Base (Last 3 Actual Months Avg)]<br>VAR _year   = MAX ( &#39;Calendar&#39;[Year] )<br>VAR _month  = MAX ( &#39;Calendar&#39;[MonthNo] )<br>VAR _lastY  = [Last Actual Year]<br>RETURN<br>IF (<br>    _year &gt; _lastY,<br>    _base * ( 1 + _growth * _month / 12 ),<br>    BLANK ()<br>)</pre><p>This pattern is reused across the planning layer. Similar measures forecast COGS, operating expenses, payroll, depreciation, working capital, and debt, ensuring all three financial statements stay internally consistent as assumptions change.</p><p>You’ll find a link to my full tutorial with all measures at the end of this post.</p><h3>Step 8. Building a Financial Planning and Forecast Dashboard</h3><p>The final step turns the planning logic into an interactive FP&amp;A dashboard. This is where assumptions move from abstract parameters to practical decision levers, and where the impact of those assumptions becomes immediately visible across the profit and loss, cash flow, and balance sheet.</p><h4>Profit and Loss Forecast View</h4><p>The first forecast page centres on the projected profit and loss statement. Parameter slicers for revenue growth, cost inflation, payroll, headcount, tax, and depreciation control the model inputs, while a page-level filter on forecast periods ensures the visuals focus only on future performance.</p><p>A monthly P&amp;L matrix shows how revenue, margins, and net income evolve across the forecast horizon, responding instantly as assumptions change. An annual summary and supporting charts provide a higher-level view, making it easier to discuss trends, efficiency, and scale effects.</p><figure><img alt="Interactive P&amp;L forecast dashboard with adjustable financial parameters." src="https://cdn-images-1.medium.com/max/1024/1*MzgTQnfFwjUlQkIszDq4Sg.png" /><figcaption>Screenshot #5. Interactive profit and loss forecasting dashboard where growth, cost, payroll, and headcount assumptions drive dynamic projections.</figcaption></figure><h4>Cash Flow, Debt, and Balance Sheet Forecast</h4><p>A second page extends the forecast across cash flow and the balance sheet. Here, parameters for capital expenditure, working capital, and debt financing control liquidity and funding assumptions.</p><p>Cash flow visuals combine operating, investing, and financing activity to show how projected profitability translates into cash generation. A debt schedule tracks borrowings, repayments, and interest expense over time, while the balance sheet matrix presents forecasted assets, liabilities, and equity at each year-end.</p><figure><img alt="Cash flow, debt, and balance sheet forecast dashboard." src="https://cdn-images-1.medium.com/max/1024/1*7yTHcAWyiGcZmPbr1g7Hqg.png" /><figcaption>Screenshot #6. Forecast dashboard showing projected cash flow, debt amortisation, and balance sheet positions under adjustable planning assumptions.</figcaption></figure><h4>Scenario and Sensitivity Analysis</h4><p>Together, these views create a practical planning environment. Adjusting assumptions reveals not just whether results change, but how they change and which drivers are responsible. Watching the year-level summaries, monthly trends, and balance sheet movements side by side makes sensitivities and pressure points clear very quickly.</p><p>At this point, the model functions as a complete FP&amp;A tool: actuals for understanding past performance, forecasts for planning ahead, and scenarios for exploring risk and opportunity.</p><h3>Continue with the full tutorial and explore the model</h3><p>This article is a condensed walkthrough of the financial planning and analysis model. The full tutorial covers every step in detail, including the complete DAX measure library and a preview of the finished FP&amp;A dashboard.</p><p>You can explore how each calculation fits into the model and download the full Microsoft Power BI file to adapt it to your own data or client work.</p><p>👉 <strong>Full Financial Planning &amp; Analysis Dashboard Tutorial</strong><br> <a href="https://www.challengejp.com/blog/power-bi-financial-planning-analysis-dashboard-tutorial/">https://www.challengejp.com/blog/power-bi-financial-planning-analysis-dashboard-tutorial/</a></p><p>There you can preview the interactive financial planning and analysis dashboard, explore the driver-based FP&amp;A model and full DAX measure library, and download the complete Power BI template for your own projects or client work (<strong>20% off with code MEDIUM</strong>).</p><h3>Related posts</h3><ul><li><a href="https://medium.com/challengejp/building-a-consolidated-p-l-and-forecast-in-8-steps-504b4d775d6e">Building a Consolidated P&amp;L and Forecast Dashboard</a></li><li><a href="https://medium.com/challengejp/building-a-sales-analysis-and-forecast-dashboard-in-8-steps-d1fa1e32e43a">Building a Sales Analysis and Forecast Dashboard</a></li></ul><h3>About the Author</h3><p>Hi, I’m Jacek. I work with financial planning and analysis, forecasting, and analytics, and I enjoy helping teams turn complex financial data into clear, decision-ready models.</p><p>If you’re building FP&amp;A, budgeting, or forecasting models in Microsoft Power BI, or exploring driver-based planning and scenario analysis, you’ll find more tutorials, templates, and resources at <a href="https://www.challengejp.com/"><strong>challengejp.com</strong></a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/1*N7Owri2UUjygDqqI2l6EhA.jpeg" /></figure><p><em>Disclaimer: This tutorial is for educational purposes only and does not constitute financial, accounting, or professional advice.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9217e7c8c175" width="1" height="1" alt=""><hr><p><a href="https://medium.com/challengejp/building-a-financial-planning-and-analysis-dashboard-in-8-steps-9217e7c8c175">Building a Financial Planning and Analysis Dashboard in 8 Steps</a> was originally published in <a href="https://medium.com/challengejp">ChallengeJP</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a Sales Analysis and Forecast Dashboard in 8 Steps]]></title>
            <link>https://medium.com/challengejp/building-a-sales-analysis-and-forecast-dashboard-in-8-steps-d1fa1e32e43a?source=rss----51290e855bf9---4</link>
            <guid isPermaLink="false">https://medium.com/p/d1fa1e32e43a</guid>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[forecasting]]></category>
            <category><![CDATA[sales-analysis]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[financial-modeling]]></category>
            <dc:creator><![CDATA[Jacek Polewski]]></dc:creator>
            <pubDate>Sun, 14 Dec 2025 11:07:13 GMT</pubDate>
            <atom:updated>2026-01-03T20:26:50.820Z</atom:updated>
            <content:encoded><![CDATA[<p>In this walkthrough, we’ll build a complete <strong>sales revenue analysis and driver-based forecast dashboard in Microsoft Power BI</strong> using a realistic retail dataset.</p><p>We’ll start from raw CSVs, shape a clean star-schema model, and create core DAX measures for <strong>orders, units, revenue, returns, COGS, shipping, gross margin, and operating profit</strong>.</p><p>Then we’ll extend the model with <strong>what-if parameters</strong> so you can stress-test scenarios like: higher units sold, price changes, rising shipping costs, or worse return rates — and see the impact on revenue and profitability immediately.</p><p><em>(All screenshots and examples come from the full tutorial, which you can access — along with data, DAX code, and templates — via the link at the end.)</em></p><h3>Step 1. Load and Explore the Data</h3><p>Before any modeling or DAX, the goal of this step is simple: get the data in, make sure it looks sane, and avoid small issues that can cause bigger problems later.</p><p>The demo dataset represents a single-store online electronics retailer. It includes detailed sales and returns at the order-line level, a product catalog, and a monthly P&amp;L — everything needed for a realistic sales analysis and forecast.</p><p>Open <strong>Power BI Desktop</strong> and go to <strong>Home → Get data → Text/CSV</strong>. Load each file one by one, and as the preview window opens, take a moment to scan the columns. This is the easiest place to catch issues like dates being treated as text or numeric fields imported incorrectly.</p><p>The files used in this model are:</p><ul><li><strong>fact_sales_2025</strong> — order lines with dates, products, units, discounts, net revenue, COGS, and shipping</li><li><strong>fact_returns_2025</strong> — returned orders linked back to the original sale, so returns reduce both revenue and cost correctly</li><li><strong>dim_products</strong> — the product master, including category, subcategory, list price, and cost</li><li><strong>fact_pl_2025</strong> — monthly P&amp;L totals, including payroll and operating expenses</li></ul><p>If you want to tidy things up, select <strong>Transform data</strong> to open Power Query. This is where you can rename columns, set data types, or remove unused fields. Nothing fancy is required here — the key is consistency. When the structure looks good, click <strong>Close &amp; Apply</strong> to load everything into the model.</p><p>That gives you a clean slate. With the data loaded and under control, the next step is to create a proper calendar table so all sales, returns, and P&amp;L figures line up correctly over time.</p><h3>Step 2. Create a Dynamic Calendar Table with DAX</h3><p>Before building visuals or measures, let’s add a proper Calendar table. In Microsoft Power BI, a dedicated date dimension keeps sales, returns, and P&amp;L reporting consistent — and it also gives you future periods for forecasting without manual updates.</p><p>In <strong>Model view</strong>, select <strong>New table</strong> and paste this DAX:</p><pre>Calendar =<br>VAR _MinDate =<br>    MINX ( ALL ( fact_sales_2025 ), fact_sales_2025[Date] )<br>VAR _MaxActualDate =<br>    MAXX ( ALL ( fact_sales_2025 ), fact_sales_2025[Date] )<br>RETURN<br>ADDCOLUMNS (<br>    CALENDAR ( _MinDate, DATE ( YEAR ( _MaxActualDate ) + 5, 12, 31 ) ),<br>    &quot;Year&quot;,         YEAR ( [Date] ),<br>    &quot;MonthNo&quot;,      MONTH ( [Date] ),<br>    &quot;Month&quot;,        FORMAT ( [Date], &quot;MMMM&quot; ),<br>    &quot;Quarter&quot;,      &quot;Q&quot; &amp; FORMAT ( [Date], &quot;Q&quot; ),<br>    &quot;YearMonth&quot;,    FORMAT ( [Date], &quot;YYYY-MM&quot; ),<br>    &quot;YearMonthNum&quot;, YEAR ( [Date] ) * 100 + MONTH ( [Date] ),<br>    &quot;WeekStartMon&quot;, [Date] - WEEKDAY ( [Date], 2 ) + 1,<br>    &quot;MonthStart&quot;,   DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ),<br>    &quot;MonthEnd&quot;,     EOMONTH ( [Date], 0 ),<br>    -- Flags (compute both from the same cutoff)<br>    &quot;IsActual&quot;,    [Date] &lt;= _MaxActualDate,<br>    &quot;IsForecast&quot;,  [Date] &gt;  _MaxActualDate<br>)</pre><p>After creating it, right-click the Calendar table and choose <strong>Mark as date table</strong>, then select the <strong>Date</strong> column.</p><figure><img alt="Power BI dialog window showing how to mark the Calendar table as a date table to enable accurate time intelligence." src="https://cdn-images-1.medium.com/max/1024/1*3Dn61FH4vL7n20cxxek_LA.png" /><figcaption>Screenshot #1. Marking a date table in Microsoft Power BI to enable time-based analysis and forecasting.</figcaption></figure><p>With that done, the model has one consistent timeline (including forecast years), and the <strong>IsActual / IsForecast</strong> flags are ready for the reporting pages later.</p><h3>Step 3. Building the Data Model in Microsoft Power BI</h3><p>With the Calendar in place, it’s time to shape a model that’s easy to filter, extend, and reason about. The goal here isn’t complexity — it’s clarity. A clean star-style schema ensures that time intelligence works correctly and that sales, margin, and forecast measures behave predictably.</p><p>To keep product filtering intuitive, the model separates <strong>Category</strong> and <strong>Subcategory</strong> into their own lightweight dimensions. These are created directly from the product master, so there’s no need for additional source files.</p><p>Start in <strong>Model view</strong> and create two small DAX tables. One extracts the unique list of categories, the other creates a simple category–subcategory hierarchy. In the full tutorial, these are built using SELECTCOLUMNS and DISTINCT to keep the dimensions lean and free of duplicates.</p><p>Once those tables exist, arrange the model so dimensions sit above the fact tables. Then create the relationships that let filters flow cleanly from dimensions into facts:</p><ul><li>The Calendar table connects to sales, returns, and the monthly P&amp;L by date.</li><li>Product dimensions filter the sales and returns fact tables by ProductID.</li><li>Category and Subcategory cascade down into the product table, creating a natural hierarchy for slicing results.</li></ul><p>All relationships are one-to-many and single-directional, from dimension to fact. This avoids ambiguity and keeps calculations stable as the model grows.</p><figure><img alt="Star-style schema data model linking Calendar, Category, Subcategory, Products, Sales, Returns, and P&amp;L tables." src="https://cdn-images-1.medium.com/max/1024/1*aaunvNCOpO3y8PaMJyZFyA.png" /><figcaption>Screenshot #2. Building a star-style schema data model for retail sales analysis and forecasting.</figcaption></figure><p>At this point, the structural work is done. Filters flow correctly by time and product, and the model is ready for analysis. Next, it’s time to introduce the DAX measures that turn raw transactions into meaningful sales and profitability metrics.</p><h3>Step 4. Monthly Sales Analysis with DAX Measures</h3><p>In this step, we’ll create the DAX measures that power the monthly view: volume (orders and units), revenue (before and after returns), cost (COGS and shipping), and profitability (gross margin and operating profit).</p><p>To keep things organized, create a dedicated measures container: <strong>Home → Enter data</strong>, name it Measures_Table, and click <strong>Load</strong>. Then right-click that table and choose <strong>New measure</strong> as you build out your library.</p><p>The example below shows the core pattern — sales → returns → net sales → costs → gross margin → operating profit:</p><pre>Orders =<br>DISTINCTCOUNT ( fact_sales_2025[OrderID] )<br><br>Units =<br>SUM ( fact_sales_2025[Units] )<br><br>Sales Revenue (After Discount, Before Returns) =<br>SUM ( fact_sales_2025[NetRevenue] )<br><br>Returns Amount =<br>- SUM ( fact_returns_2025[RefundAmount] )<br><br>Net Sales =<br>[Sales Revenue (After Discount, Before Returns)] + [Returns Amount]</pre><pre>COGS (Sales) =<br>- SUM ( fact_sales_2025[COGS] )<br><br>COGS (Returns) =<br>SUM ( fact_returns_2025[COGSReturned] )<br><br>COGS (Net) =<br>[COGS (Sales)] + [COGS (Returns)]</pre><pre>Shipping (Sales) =<br>- SUM ( fact_sales_2025[ShippingCost] )<br><br>Shipping (Returns) =<br>- SUM ( fact_returns_2025[ShippingCost] )<br><br>Shipping (Total) =<br>[Shipping (Sales)] + [Shipping (Returns)]<br><br>Gross Margin ($) =<br>[Net Sales] + [COGS (Net)] + [Shipping (Total)]</pre><pre>Payroll (Actual) =<br>CALCULATE (<br>    SUM ( fact_pl_2025[Amount] ),<br>    fact_pl_2025[Account] = &quot;Payroll (incl. taxes &amp; bonus)&quot;<br>)<br><br>Other OPEX (Actual) =<br>CALCULATE (<br>    SUM ( fact_pl_2025[Amount] ),<br>    fact_pl_2025[AccountGroup] = &quot;OPEX&quot;,<br>    fact_pl_2025[Account] &lt;&gt; &quot;Payroll (incl. taxes &amp; bonus)&quot;,<br>    fact_pl_2025[Account] &lt;&gt; &quot;Logistics / Shipping&quot;<br>)<br><br>OPEX (Excl Shipping) (Actual) =<br>[Payroll (Actual)] + [Other OPEX (Actual)]<br><br>Operating Profit ($) =<br>[Gross Margin ($)] + [OPEX (Excl Shipping) (Actual)]</pre><p>A couple of conventions to keep consistent as you expand the measure library:</p><ul><li>Returns are treated as <strong>negative revenue</strong>, so refunds reduce the top line.</li><li>Costs are stored as <strong>negative totals</strong>, which makes profit ladders easier to read.</li><li>Shipping is treated as a cost on both legs — outbound and returns.</li></ul><p>The remaining KPIs — average order value, average selling price, return rates, gross margin %, and shipping cost per item — all build directly on these totals and are used throughout the dashboard.</p><p>You will find a complete measure set covering base totals and KPIs in in my full tutorial (link at the end of this article).</p><h3>Step 5. Building the Monthly Sales Analysis Dashboard</h3><p>Let’s now turn the measures into a clear monthly view. The aim here is speed and readability: a page that shows how sales and margins are moving, and lets readers drill into categories only when something needs explaining.</p><p>Rename the page to <strong>Sales analysis</strong> and, at the page level, filter <strong>Calendar[IsActual] = TRUE</strong> so the view stays focused on historical results.</p><p>Add a <strong>Matrix</strong> with <strong>Calendar[MonthStart]</strong> on the rows (formatted as yyyy-mm) and the main totals in the values area — orders, units, returns, net sales, COGS, shipping, and gross margin. Keep formatting consistent and use <em>Rename for this visual</em> if column names get too long.</p><p>Then add slicers for <strong>Category</strong>, <strong>Subcategory</strong>, and <strong>Year</strong>. Category works well as a dropdown, with Subcategory responding automatically through the model hierarchy.</p><p>Use <strong>Card (new)</strong> visuals to surface the headline KPIs: items per order, average selling price, gross margin per item, gross margin %, average shipping cost per item, and return rate by revenue. These provide context without forcing users to dig through the matrix.</p><figure><img alt="Sales analysis dashboard with monthly revenue, units, returns, COGS, shipping, and gross margin KPIs and category slicers." src="https://cdn-images-1.medium.com/max/1024/1*LDq9wqDLJpF24a00PbygOA.png" /><figcaption>Screenshot #3. Monthly retail sales analysis Power BI dashboard with KPIs and product filters.</figcaption></figure><p>To round out the page, add a treemap for gross margin per item by subcategory and a bar chart showing top-selling subcategories by units. These two visuals quickly highlight mix and concentration without overwhelming the page.</p><h3>Step 6. Monthly and Annual Sales Forecast Measures</h3><p>With the actuals working, it’s time to add a forecast layer. The idea here isn’t to build a complex statistical model, but a <strong>driver-based forecast</strong> that responds instantly to business assumptions — exactly the kind of tool finance and commercial teams expect in Microsoft Power BI.</p><p>The forecast uses two simple concepts. First, <strong>what-if parameters</strong> act as percentage adjustments for key drivers like units sold, price, costs, and returns. Second, forecast measures reference prior-year results and compound those adjustments forward so changes flow naturally through revenue, margin, and operating profit.</p><p>Start by creating numeric parameters from <strong>Modeling → New parameter → Numeric range</strong>. Each parameter represents a percentage change for items sold, average selling price, COGS, shipping cost, return rate, and salary OPEX.</p><p>The parameters are then converted into a reusable multiplier. A simple pattern looks like this:</p><pre>Items Sold Multiplier =<br>1 + &#39;Items Sold&#39;[Items Sold Value]</pre><p>That multiplier becomes a building block for the forecast measures. To control when forecast logic applies, the model uses a small helper measure that calculates how many years ahead the current date is compared to the last actual year. Actual periods return zero, while future years return 1, 2, and so on.</p><p>From there, the forecast follows a consistent pattern: take the prior-year value, shift it back with DATEADD, and apply the relevant multipliers. For example, units sold are projected by scaling last year’s units by the items-sold assumption:</p><pre>Units (Forecast) =<br>VAR n = [Years Ahead]<br>RETURN<br>IF (<br>    n = 0,<br>    BLANK(),<br>    VAR Base =<br>        CALCULATE ( [Units], DATEADD ( Calendar[Date], -n, YEAR ) )<br>    RETURN Base * POWER ( [Items Sold Multiplier], n )<br>)</pre><p>Revenue, returns, COGS, shipping, and OPEX follow the same structure, each driven by the assumptions that actually affect them. Because the logic is consistent, changes in one driver — like higher return rates or rising shipping costs — ripple cleanly through the entire model.</p><p>By the end of this step, the dataset contains two parallel worlds: actuals and forecast, both aligned on the same calendar and product dimensions. All that’s left is to surface this logic in an interactive forecast dashboard.</p><h3>Step 7. Building an Interactive Power BI Forecast</h3><p>This step focuses on turning the forecast measures into a dynamic view. The goal is to create a dashboard where assumptions can be adjusted and their impact on sales, costs, and profitability is immediately visible.</p><p>Start by adding slicers for the forecast parameters you created earlier — items sold, average selling price, COGS, shipping cost, return rate, and salary OPEX. Set each slicer to use a <strong>slider</strong> style and format the values as percentages so they read naturally. These controls become the input panel for the entire forecast.</p><p>Next, add a <strong>Matrix</strong> with <strong>Calendar[Year]</strong> on the rows. Populate the values with the key forecast measures: forecast units, net sales, net COGS, shipping, gross margin, OPEX, and operating profit. When all parameters are set to zero, this matrix should mirror the last actual year — a quick sanity check that the forecast logic is aligned with the base data.</p><figure><img alt="Power BI forecast dashboard showing sliders for units, pricing, COGS, shipping, and returns with YoY variances and profitability." src="https://cdn-images-1.medium.com/max/1024/1*XrrHfpzsp1VG663V-8P0fA.png" /><figcaption>Screenshot #4. Interactive sales forecast Power BI dashboard with what-if parameters and variance analysis.</figcaption></figure><p>To make changes easier to interpret, include a small set of year-over-year variance measures next to the forecast values. Absolute and percentage variances help highlight where growth is coming from and where cost pressure is building. With a bit of conditional formatting, positive and negative movements stand out instantly.</p><h3>Step 8. Scenario and what-if analysis</h3><p>Finally, let’s use the forecast dashboard for scenario testing and sensitivity analysis, focusing on how changes in assumptions affect revenue, cost, and profitability.</p><p>Start with a simple baseline. Set all parameters to 0% so forecast years mirror the last actual year. This gives you a clear reference point and confirms that the forecast logic is aligned with historical results.</p><p>From there, try a few controlled adjustments. Increasing items sold while keeping pricing and costs stable shows how volume-driven growth affects margins. Pushing prices up while costs rise faster highlights how inflation can quietly compress profitability. Combining aggressive volume growth with higher shipping costs and return rates illustrates the trade-off between scale and operational strain.</p><p>As the sliders move, watch three areas together: the year-level matrix, the variance columns, and the monthly trend chart. Looking at them together shows how changes flow across time, volume, and profitability.</p><h3>Continue with the full tutorial and preview the dashboard</h3><p>This article is the high-level walkthrough. If you’d like to follow the full, end-to-end build — from raw data to a finished sales analysis and forecast dashboard — you can continue with the complete tutorial here:</p><p>👉 <a href="https://www.challengejp.com/blog/power-bi-sales-analysis-forecast-dashboard-tutorial/"><strong>Full Sales Analysis &amp; Forecast Dashboard Tutorial</strong></a></p><p>There you can preview the interactive sales analysis and forecast dashboard, explore the driver-based model and DAX measure library, and download the full Power BI template (<strong>20% off with code MEDIUM</strong>) for your own project.</p><h3>Related Posts</h3><ul><li><a href="https://medium.com/challengejp/building-a-consolidated-p-l-and-forecast-in-8-steps-504b4d775d6e"><em>Building a Consolidated P&amp;L and Forecast Dashboard</em></a></li><li><a href="https://medium.com/challengejp/how-to-create-a-cash-flow-forecast-in-10-steps-1ce8b218fa99"><em>Creating a Cash Flow Forecast in Microsoft Excel</em></a></li></ul><h3>About the Author</h3><p>Hi, I’m Jacek. I work with financial modeling, forecasting, and analytics, and I enjoy helping teams turn raw data into clear, decision-ready insights.</p><p>If you’re building a sales, profitability, or forecast model in Microsoft Power BI, or experimenting with driver-based planning and scenario analysis, you can find more tutorials, templates, and resources at <a href="https://www.challengejp.com/"><strong>challengejp.com</strong></a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/1*N7Owri2UUjygDqqI2l6EhA.jpeg" /></figure><p><em>Disclaimer: This tutorial is for educational purposes only and does not constitute financial, accounting, or professional advice.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d1fa1e32e43a" width="1" height="1" alt=""><hr><p><a href="https://medium.com/challengejp/building-a-sales-analysis-and-forecast-dashboard-in-8-steps-d1fa1e32e43a">Building a Sales Analysis and Forecast Dashboard in 8 Steps</a> was originally published in <a href="https://medium.com/challengejp">ChallengeJP</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a Project Profitability and Cost Allocation Dashboard in 8 Steps]]></title>
            <link>https://medium.com/challengejp/building-a-project-profitability-and-cost-allocation-dashboard-in-8-steps-8645f4c42885?source=rss----51290e855bf9---4</link>
            <guid isPermaLink="false">https://medium.com/p/8645f4c42885</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[project-finance]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[financial-modeling]]></category>
            <category><![CDATA[dashboard]]></category>
            <dc:creator><![CDATA[Jacek Polewski]]></dc:creator>
            <pubDate>Sun, 30 Nov 2025 13:02:14 GMT</pubDate>
            <atom:updated>2025-12-14T11:13:36.527Z</atom:updated>
            <content:encoded><![CDATA[<p>Project profitability can be deceptively hard to measure. Revenue is usually easy to report, but true margins depend on how accurately salaries, overheads, and delivery effort are allocated across projects.</p><p>In this guide, you’ll build a complete <strong>project cost allocation and profitability model in Microsoft Power BI</strong>, using accounting, employee, timesheet, and expense data.</p><p>Starting from raw CSV files, the model ends with an interactive dashboard that allocates staff costs, distributes overheads, and supports what-if analysis for revenue, salaries, and hours.</p><p><em>(All screenshots and examples come from the full tutorial, which you can access — along with data, DAX code, and templates — via the link at the end.)</em></p><h3>Step 1. Load and Prepare the Data</h3><p>Before building measures or allocation logic, it’s time to get the data into shape. In Microsoft Power BI, small issues — mismatched date formats or incorrect data types — can quietly break calculations later, especially when costs and hours are being distributed across projects.</p><p>The model uses four source files:</p><ul><li><strong>accounting_pl</strong> with monthly revenue and expense lines</li><li><strong>employees</strong> with salary and role data</li><li><strong>timesheets</strong> with weekly hours recorded by project</li><li><strong>expenses</strong> with detailed project cost entries</li></ul><p>Load each file using <strong>Home → Get Data → Text/CSV</strong>. As each dataset opens in preview, take a moment to confirm that dates are recognized correctly and numeric fields are typed as numbers. Catching these issues early saves time later when relationships and allocation logic are added.</p><p>Once the files are loaded, open <strong>Transform data</strong> to apply light cleanup. The goal here isn’t heavy transformation, just consistency. In particular, convert the accounting dates into proper Date values and create an <strong>absolute amount</strong> column in the accounting table.</p><p>Because the P&amp;L includes both revenue and costs stored with different signs, working with absolute amounts simplifies later allocation logic and avoids sign confusion.</p><p>Create a custom column using:</p><pre>Number.Abs([Amount])</pre><p>Then close Power Query and apply the changes.</p><p>Before moving on, create a <strong>Calendar table</strong>. Time alignment becomes essential once revenue, expenses, salaries, and hours are analyzed together. A simple dynamic calendar built with DAX is enough for this model:</p><pre>Calendar =<br>ADDCOLUMNS (<br>    CALENDARAUTO(),<br>    &quot;Year&quot;, YEAR ( [Date] ),<br>    &quot;MonthNo&quot;, MONTH ( [Date] ),<br>    &quot;Month&quot;, FORMAT ( [Date], &quot;MMMM&quot; ),<br>    &quot;MonthStart&quot;, DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )<br>)</pre><p>After creating the table, mark it as a date table so Microsoft Power BI uses it consistently for time-based calculations.</p><p>With the data loaded, cleaned, and aligned on a shared calendar, the foundation is ready. Next, it’s time to shape everything into a clean, readable data model.</p><h3>Step 2. Build the Data Model and Relationships</h3><p>With the data prepared, it’s time to shape everything into a model that supports reliable cost allocation. The goal here is a clean structure where filters flow in one direction and calculations behave predictably — especially once salaries and overheads are distributed across projects.</p><p>This model follows a mostly <strong>star-style layout</strong>. Core dimensions like Calendar, Employees, Clients, and Projects sit at the top, filtering down into fact tables such as accounting, timesheets, and expenses. This keeps the logic intuitive: dimensions describe the business context, and fact tables hold the numbers.</p><p>Start in <strong>Model view</strong> and arrange the tables so dimensions are visually separated from facts. Then create the core relationships that tie effort, cost, and revenue together:</p><ul><li>Employees filter both <strong>timesheets</strong> and <strong>expenses</strong> by EmployeeID</li><li>Calendar filters <strong>accounting</strong>, <strong>timesheets</strong>, and <strong>expenses</strong> by date</li><li>Projects filter all fact tables that record project-level activity</li></ul><p>All relationships should be <strong>one-to-many</strong>, <strong>single-directional</strong>, and <strong>active</strong>. This ensures that selections for time, client, or project always flow cleanly into the numbers you’re analyzing.</p><p>To make filtering by client and project easier, create two lightweight dimensions using DAX. These extract clean, de-duplicated lists directly from the accounting data.</p><p>A representative example is the Projects dimension:</p><pre>DimProjects =<br>DISTINCT (<br>    SELECTCOLUMNS (<br>        accounting_pl,<br>        &quot;Project&quot;, accounting_pl[Project],<br>        &quot;Client&quot;,  accounting_pl[Client]<br>    )<br>)</pre><p>A similar pattern is used to create a simple Clients table. Once created, connect Clients to Projects, and Projects to the relevant fact tables. Finally, rename the employees table to <strong>DimEmployees</strong> to keep naming consistent across the model.</p><p>At this point, the structure should read clearly: Calendar, Clients, Projects, and Employees at the top, with accounting, timesheets, and expenses underneath.</p><figure><img alt="Diagram of a Microsoft Power BI data model with dimension tables linked to fact tables using one-to-many relationships for project cost allocation." src="https://cdn-images-1.medium.com/max/1024/1*HZnrF2CBjd7O2nksHOTMGA.png" /><figcaption><em>Screenshot #1: A star style schema connecting Calendar, Employees, Clients, and Projects to fact tables for accounting, expenses, and timesheets.</em></figcaption></figure><p>With the model wired up, filters now flow exactly where they should. The next step is to introduce baseline measures — the raw revenue, cost, and hour totals that everything else in the allocation engine builds on.</p><h3>Step 3. Create Baseline Project Metrics</h3><p>With the data model in place, it’s time to give it analytical meaning. Before allocating salaries or spreading overheads, the model needs a small, reliable set of <strong>baseline measures</strong> that consistently track revenue, direct cost, and effort across projects.</p><p>Think of these as the foundation layer. Every allocation, margin calculation, and forecast later in the model builds on them, so consistency matters more than sophistication at this stage.</p><p>To keep things organized, create a dedicated container for DAX. In <strong>Report view</strong>, select <strong>Enter data</strong>, name the table Measures_Table, leave it empty, and click <strong>Load</strong>. This table will hold all measures used in the model.</p><p>In myfull tutorial <em>(link at the end of this article)</em>, the baseline layer includes a broader library of measures but here are examples of measures that illustrate the core pattern:</p><pre>Total Revenue =<br>CALCULATE (<br>    SUM ( accounting_pl[Amount_Absolute] ),<br>    accounting_pl[Category] = &quot;Revenue&quot;<br>)</pre><pre>Total Direct Expenses =<br>SUM ( expenses[Amount] )</pre><pre>Total Hours =<br>SUM ( timesheets[Hours] )</pre><pre>Billable Hours =<br>CALCULATE ( [Total Hours], timesheets[Billable] = &quot;Y&quot; )</pre><p>Together, these measures establish a consistent analytical base: revenue, direct cost, and hours — all aligned by the same calendar, client, and project filters.</p><p>Validate that so far the model behaves as expected. Add a simple <strong>Matrix</strong> with <strong>Calendar[MonthStart]</strong> on the rows and these baseline measures in the values, then add slicers for <strong>Year</strong>, <strong>Client</strong>, and <strong>Project</strong> to sanity-check the numbers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*c46KaQ58Q3sDYxbiTQTH_g.png" /><figcaption><em>Screenshot #2: A baseline matrix showing monthly revenue, direct expenses, non-direct costs, and project hours.</em></figcaption></figure><p>With baseline metrics in place, the next step is to build the allocation logic that turns raw hours and costs into fully loaded project profitability.</p><h3>Step 4. Building the Cost Allocation Logic</h3><p>With baseline metrics in place, it’s time to build the allocation engine. This is where the model starts showing true project cost by assigning salaries and overheads based on actual delivery effort and scale.</p><p>The allocation logic has three core components: allocating employee salaries using timesheets, distributing indirect overheads using a consistent basis, and combining everything into a fully loaded project cost.</p><p>Here is an example where employee salaries are allocated by converting annual employer cost into an effective hourly rate and applying it to recorded hours. Projects that consume more delivery time absorb a larger share of staff cost:</p><pre>Allocated Employee Cost =<br>SUMX (<br>    timesheets,<br>    VAR AnnualCost = RELATED ( DimEmployees[AnnualEmployerCost] )<br>    VAR WeeklyHrs  = COALESCE ( RELATED ( DimEmployees[WeeklyHours] ), 40 )<br>    VAR HourlyRate = AnnualCost / ( WeeklyHrs * 52 )<br>    RETURN timesheets[Hours] * HourlyRate<br>)</pre><p>Indirect costs such as rent, software, and general administration are pooled and distributed across projects. To keep the model flexible, the allocation basis can be switched between <strong>hours</strong> and <strong>revenue</strong>, allowing different internal reporting perspectives without changing the underlying logic.</p><p>A small selector table captures the user’s choice, and a single measure drives the switch:</p><pre>Allocation Share =<br>VAR Basis = SELECTEDVALUE ( &#39;Allocation Basis&#39;[Basis], &quot;Hours&quot; )<br>RETURN<br>    IF ( Basis = &quot;Revenue&quot;, [Revenue Share], [Hours Share] )</pre><p>Once the allocation share is defined, distributing the overhead pool becomes straightforward. Each project receives its portion based on its share of total hours or revenue:</p><pre>Allocated Overheads =<br>[Overhead Pool] * [Allocation Share]</pre><p>The final step is to bring all cost components together into a single, fully loaded figure. This measure combines staff cost, direct expenses, and allocated overheads:</p><pre>Total Allocated Cost =<br>[Allocated Employee Cost]<br>+ [Total Direct Expenses]<br>+ [Allocated Overheads]</pre><p>This fully loaded cost measure underpins all margin and profitability calculations in the dashboard. With allocation logic working end to end, the model is ready to present results in a clear project-level profitability view.</p><h3>Step 5. Creating the Project Cost &amp; Profitability Dashboard</h3><p>With the allocation logic in place, the model can now calculate fully loaded project cost — combining salaries, direct expenses, and allocated overheads under the same time, client, and project filters. The purpose of this step is to surface those results in a way that’s easy to read and easy to trust.</p><p>Start in <strong>Report view</strong> by creating a project profit and loss matrix. Place <strong>Calendar[MonthStart]</strong> on the rows and add the key measures that define project performance: revenue, direct expenses, allocated employee cost, allocated overheads, and total allocated cost. As these appear together, the structure of a monthly P&amp;L becomes clear.</p><p>Once the monthly view is working, duplicate the matrix and switch the rows from MonthStart to <strong>Project</strong>. This creates a project-level P&amp;L that’s often the primary lens for operational and financial reviews. Adding a simple profit measure — revenue minus fully loaded cost — completes the picture and makes margin differences visible across projects.</p><figure><img alt="Power BI dashboard displaying a project-level profit and loss view with revenue, direct costs, salary allocation, overhead allocation, and total profit." src="https://cdn-images-1.medium.com/max/1024/1*YzuFQE657cPOtcaK7FigoA.png" /><figcaption><em>Screenshot #3: A project profitability matrix showing revenue, staff costs, overhead allocations, and fully loaded margins by month and by project.</em></figcaption></figure><p>To ensure the allocation logic is behaving correctly, add a small reconciliation check. Since indirect costs are distributed across projects, the total allocated overhead should tie back exactly to the non-direct totals in the accounting P&amp;L.</p><p>At this stage, the dashboard shows not just revenue and cost, but how those costs are built up and where margins are gained or lost. With project-level profitability clearly visible, the next step is to drill deeper into the drivers behind those numbers by looking at timesheets and staff contribution.</p><h3>Step 6. Analyzing Timesheets and Staff Cost Contribution</h3><p>This step focuses on how individual team members contribute effort, cost, and revenue across projects, and how those contributions affect margins.</p><p>Before breaking things down by employee, the model introduces a simple toggle that let users choose <strong>billable hours only</strong> or <strong>all recorded hours</strong> as the basis of the calculations:</p><pre>Hour Basis =<br>DATATABLE ( &quot;Basis&quot;, STRING, { { &quot;Billable Hours&quot; }, { &quot;Total Hours&quot; } } )<br><br>Selected Hour Basis =<br>SELECTEDVALUE ( &#39;Hour Basis&#39;[Basis], &quot;Billable Hours&quot; )</pre><p>With the hour basis defined, employee contribution can be calculated consistently. The core idea is proportionality: employees who contribute more hours to a project receive a larger share of that project’s revenue and cost.</p><p>A simplified example starts by calculating an employee’s share of project hours:</p><pre>Employee Share of Project Hours =<br>DIVIDE (<br>    [Employee Hours (selected basis)],<br>    [Project Hours (selected basis)]<br>)</pre><p>That share is then used to attribute revenue:</p><pre>Employee Revenue Contribution =<br>[Employee Share of Project Hours] * [Total Revenue]</pre><p>Once the model attributes revenue at the employee level, rate-based metrics become especially powerful. Revenue per hour, cost per hour, and margin per hour translate raw contribution into productivity signals that are easy to compare across roles, projects, and time periods. These measures often reveal patterns that aren’t obvious at the project level alone.</p><figure><img alt="Power BI report visualizing employee-level project hours, revenue attribution, cost allocation, and margin per hour." src="https://cdn-images-1.medium.com/max/1024/1*QOJTeas2ZVVHQvavtDUjoA.png" /><figcaption><em>Screenshot #4: An employee contribution dashboard showing hours worked, allocated cost, and revenue contribution across projects.</em></figcaption></figure><p>With employee contribution analysis in place, the next step is to extend the model beyond actuals by adding forecast parameters and building a parallel forecast layer.</p><h3>Step 7. Adding Forecast Parameters and Building the Forecast Logic</h3><p>With actuals working end to end, the next step is to introduce a forecast layer. The approach here is driver-based rather than statistical: adjustable parameters act as scaling factors that flow through revenue, costs, and profitability without changing the underlying data.</p><p>Create a set of <strong>what-if parameters</strong> from <strong>Modeling → New parameter → Numeric range</strong>. Each parameter represents a percentage adjustment to a key driver, such as revenue, salaries, hours, direct expenses, or overhead. These parameters become the user-controlled inputs for scenario testing.</p><p>Each parameter is then converted into a simple multiplier that can be reused across measures. A representative example looks like this:</p><pre>Revenue Multiplier =<br>1 + &#39;Revenue %&#39;[Revenue % Value]</pre><p>The same pattern is used for hours, salaries, expenses, and overhead. These multipliers allow forecast measures to mirror the structure of actual measures while responding dynamically to user assumptions.</p><p>Forecast metrics are built by applying the relevant multipliers to the baseline measures. For example, forecasted revenue scales actual revenue using the selected revenue assumption:</p><pre>Total Revenue (Forecast) =<br>[Total Revenue] * [Revenue Multiplier]</pre><p>The same idea extends to forecasted hours, direct expenses, allocated salaries, and overheads. Because the logic is consistent, changes cascade naturally through the model. Adjusting hours affects staff allocation, increasing salaries raises fully loaded project cost, and higher overhead assumptions flow across all projects proportionally.</p><p>Once all forecast components are defined, they are combined into forecasted fully loaded cost and profit measures, creating a complete parallel branch of the model — actuals and forecast — driven by the same structure and filters.</p><figure><img alt="Power BI interface showing a numeric parameter dialog for defining revenue, cost, and salary forecast assumptions." src="https://cdn-images-1.medium.com/max/684/1*UqGE8008l8iY45dI705cRg.png" /><figcaption><em>Screenshot #5: Creating a what-if parameter in Microsoft Power BI to drive forecast sensitivity analysis.</em></figcaption></figure><p>For an expanded forecast logic with additional reconciliation checks and sensitivity refinements, follow the link to my full tutorial at the end of this post.</p><h3>Step 8. Building the Forecast Dashboard and Sensitivity Analysis</h3><p>Start by creating a <strong>project-level forecast matrix</strong>. Place <strong>Project</strong> on the rows and add the forecast measures for revenue, direct expenses, allocated salaries, allocated overheads, fully loaded cost, and project profit. When all forecast parameters are set to zero, this view should mirror the actuals, confirming that both branches of the model are aligned.</p><p>Next, add slicers for each forecast parameter — revenue, expenses, salaries, hours, and overhead. Use slider-style controls so adjustments feel immediate and easy to interpret. As soon as a parameter changes, the matrix updates, showing how costs and profitability shift across projects.</p><p>To highlight sensitivity, add a visual that compares <strong>actual versus forecast profit</strong> by project. A simple clustered bar chart works well here, making it easy to spot which projects are most affected by changes in assumptions. Adding a variance measure as a label or tooltip further clarifies the impact.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8LWh_6jkzAVYSs7twRGKUQ.png" /><figcaption><em>Screenshot #6: A Power BI forecast dashboard showing project-level revenue, cost, and profit under adjustable forecast assumptions.</em></figcaption></figure><p>Finally, include a small set of <strong>per-hour KPI cards</strong> — forecasted revenue per billable hour, staff cost per hour, overhead per hour, and total cost per hour. These rate-based metrics translate scenario changes into numbers that are easy to discuss in planning, pricing, and resourcing conversations.</p><p>Together, these visuals turn the model into a practical decision-support tool. As assumptions change, their impact on revenue, cost, and profit becomes visible, highlighting where margins are most sensitive.</p><h3>Continue with the full tutorial and preview the dashboard</h3><p>This post is the high-level walkthrough. To explore the full, interactive version — and access every DAX measure, Power Query step, and the downloadable Microsoft Power BI file — visit the complete guide:</p><p>👉 <a href="https://www.challengejp.com/blog/power-bi-project-cost-allocation-dashboard-tutorial/"><strong>Full Project Cost Allocation &amp; Forecast Tutorial</strong></a></p><p>You can preview the fully interactive dashboard, explore each allocation step, and download the Power BI template (<strong>20% off with code MEDIUM</strong>) to adapt it to your own projects.</p><h3>Related Posts</h3><p>Check out my other Medium articles:</p><ul><li><a href="https://medium.com/challengejp/how-to-create-a-project-finance-model-in-9-steps-9b7415b0ab11"><em>Creating a Project Finance Model in Microsoft Excel</em></a></li><li><a href="https://medium.com/challengejp/building-a-consolidated-p-l-and-forecast-in-8-steps-504b4d775d6e"><em>Building a Consolidated P&amp;L and Forecast Dashboard</em></a></li></ul><h3>About the Author</h3><p>Hi, I’m Jacek — I love financial modeling, forecasting, and helping people understand their numbers and make better decisions with data.</p><p>If you’d like help creating a profitability dashboard or want to explore analytics and forecasting models, feel free to reach out or browse more resources and templates at <a href="https://www.challengejp.com/"><strong>challengejp.com</strong></a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/0*gfSuq762lzGYQCZY.jpeg" /></figure><p><em>Disclaimer: This tutorial is for educational purposes only and does not constitute financial, accounting, or professional advice.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8645f4c42885" width="1" height="1" alt=""><hr><p><a href="https://medium.com/challengejp/building-a-project-profitability-and-cost-allocation-dashboard-in-8-steps-8645f4c42885">Building a Project Profitability and Cost Allocation Dashboard in 8 Steps</a> was originally published in <a href="https://medium.com/challengejp">ChallengeJP</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a Marketing Performance & Forecast Dashboard in 8 Steps]]></title>
            <link>https://medium.com/challengejp/building-a-marketing-performance-forecast-dashboard-in-8-steps-4d464e5cdf4b?source=rss----51290e855bf9---4</link>
            <guid isPermaLink="false">https://medium.com/p/4d464e5cdf4b</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[marketing-analytics]]></category>
            <category><![CDATA[forecasting]]></category>
            <category><![CDATA[digital-advertising]]></category>
            <category><![CDATA[data-analytics]]></category>
            <dc:creator><![CDATA[Jacek Polewski]]></dc:creator>
            <pubDate>Sun, 16 Nov 2025 13:06:21 GMT</pubDate>
            <atom:updated>2025-11-30T22:03:17.546Z</atom:updated>
            <content:encoded><![CDATA[<p>This short tutorial shows how to analyze marketing campaign performance using Microsoft Power BI. We’ll import your data, build a clean data model, and create DAX measures for CAC, churn, ARPU, and revenue so you can track performance and project customer growth.</p><p>By the end, we’ll add driver-based forecast parameters and assemble a dashboard that highlights channel efficiency, spend mix, and growth scenarios that you can adapt to your own data.</p><p><em>(All screenshots and examples come from the full tutorial, which you can access — along with data, DAX code, and templates — via the link at the end.)</em></p><h3>Step 1 — Import the Campaign Data</h3><p>In <strong>Microsoft Power BI</strong>, go to <strong>Home → Get Data → Text/CSV</strong>, choose marketing_data_2025.csv, and click <strong>Transform Data</strong> to open <strong>Power Query</strong>. You should see <strong>Date, Channel, Campaign Name, Impressions, Clicks, Conversions, Spend</strong>. No cleaning needed — just rename the table <strong>CampaignData</strong>, then <strong>Close &amp; Apply</strong> to load it.</p><figure><img alt="Power Query preview of marketing_data_2025.csv with Date, Channel, Campaign Name, Impressions, Clicks, Conversions, Spend" src="https://cdn-images-1.medium.com/max/1024/1*TMfYO9yU744HdAsfWaqr5Q.png" /><figcaption>Inspecting campaign metrics in Power Query before loading into Microsoft Power BI.</figcaption></figure><p><em>Note: CSV keeps the demo simple, but in production you can connect directly to your database or warehouse. You can download the sample data files using the link at the end of this tutorial.</em></p><h3>Step 2 — Create the Model Foundations</h3><p>Create a lightweight model so the marketing KPIs calculate cleanly across time, campaigns, and channels.</p><p>Add a <strong>Calendar</strong> table (generate actuals + five forecast years) and <strong>mark it as the Date table</strong>. Then create lookup tables for <strong>Campaign</strong> and <strong>Channel</strong> so you can filter visuals without touching the fact table.</p><p><strong>Example — DimChannel (DAX)</strong></p><pre>DimChannel =<br>DISTINCT(<br>    SELECTCOLUMNS(&#39;CampaignData&#39;, &quot;Channel&quot;, &#39;CampaignData&#39;[Channel])<br>)</pre><p>In <strong>Microsoft Power BI</strong> Model view, link (1→many):</p><ul><li>Calendar[Date] → CampaignData[Date]</li><li>DimCampaign[Campaign Name] → CampaignData[Campaign Name]</li><li>DimChannel[Channel] → CampaignData[Channel]</li></ul><p>This star schema lets DAX measures (CTR, Conversion Rate, CAC) respect filters by month, campaign, and channel.</p><figure><img alt="Microsoft Power BI data model linking Calendar, Campaign, and Channel to CampaignData" src="https://cdn-images-1.medium.com/max/1024/1*R5_49iwirmBWRar-U4HhZQ.png" /><figcaption>Building a clean star schema for marketing performance analysis and forecasting.</figcaption></figure><h3>Step 3 — Create Marketing Performance Indicators in DAX</h3><p>Create the core measures that power the dashboard: totals for <strong>Impressions, Clicks, Conversions, Spend</strong>, plus <strong>CTR, Conversion Rate</strong>, and <strong>CAC (Actual)</strong>. These respond to any date, channel, or campaign filter and become the backbone for your visuals.</p><p><strong>Example — Aggregator (Impressions)</strong></p><pre>Impressions (Total) =<br>SUM(&#39;CampaignData&#39;[Impressions])</pre><p><strong>Example — CAC (Actual)</strong></p><pre>CAC (Actual) =<br>DIVIDE([Spend (Total)], [Conversions (Total)])</pre><p>With these in place, you can compare efficiency across channels and over time, and set up the next step’s report to track performance month by month.</p><h3>Step 4 — Build a Report to Analyze Campaign Performance</h3><p>Create your first report page by visualizing performance across months and channels. Start with a monthly matrix showing <strong>Impressions, Clicks, Conversions, Spend, CTR, Conversion Rate, and CAC</strong>, then duplicate it and switch the rows to <strong>Channel</strong> for at-a-glance comparison.</p><p>Add a <strong>Channel slicer</strong>, a <strong>Treemap</strong> to compare CAC across channels, and a <strong>Line + Stacked Column</strong> chart to track spend and channel mix over time. This layout reveals which channels scale efficiently and how performance shifts month to month.</p><figure><img alt="Power BI page showing marketing KPIs by month and channel" src="https://cdn-images-1.medium.com/max/1024/1*QrxkEBWDUiLBoYpg9Kc-rw.png" /><figcaption>Visualizing CTR, CAC, and spend trends before moving into forecasting.</figcaption></figure><h3>Step 5 — Set Up Parameters for Customer Acquisition Projections</h3><p>Create the inputs that drive your forecast by adding numeric-range parameters for <strong>CAC</strong>, <strong>Customer MRR</strong>, <strong>Monthly Budget</strong>, <strong>Monthly Churn</strong>, and <strong>Users at Month 0</strong>. These values control how many new users you acquire, how many churn, and how much monthly revenue each forecast month generates.</p><p>Place all parameters as <strong>sliders</strong> on a Budget page to build an interactive assumptions panel that updates the forecast instantly.</p><figure><img alt="Creating forecast parameters for CAC, Budget, MRR, and Churn in Power BI" src="https://cdn-images-1.medium.com/max/680/1*hfKEx5puWNRAC89b7oG-QA.png" /><figcaption>Input assumptions used to power the customer growth and revenue forecast.</figcaption></figure><h3>Step 6 — Forecast Customer Growth &amp; Revenue</h3><p>Use your parameters to drive a simple monthly forecast:</p><p><strong>Monthly Budget ÷ CAC</strong> gives projected new users, <strong>Monthly Churn</strong> reduces the active base, and the remaining users roll forward into the next month. Multiplying the end-of-month users by <strong>Customer MRR</strong> produces the revenue forecast for each period.</p><p>Here’s the actual DAX that calculates monthly new users:</p><pre>New Users (Monthly, Forecast) = <br>VAR fs = [Forecast Start (Month)]<br>RETURN<br>IF (<br>    ISINSCOPE ( &#39;Calendar&#39;[Start of Month] ),<br>    IF ( MAX(&#39;Calendar&#39;[Start of Month]) &lt; fs, BLANK(),<br>         DIVIDE ( &#39;Monthly Budget&#39;[Monthly Budget Value], &#39;CAC&#39;[CAC Value] ) ),<br>    SUMX (<br>        VALUES ( &#39;Calendar&#39;[Start of Month] ),<br>        VAR m = &#39;Calendar&#39;[Start of Month]<br>        RETURN IF ( m &lt; fs, BLANK(),<br>                    DIVIDE ( &#39;Monthly Budget&#39;[Monthly Budget Value], &#39;CAC&#39;[CAC Value] ) )<br>    )<br>)</pre><p>Together with the churn and end-of-month user measures, this builds the monthly forecast you’ll visualize on the Budget page. Any change to CAC, Budget, Churn, or MRR updates the projection instantly.</p><h3>Step 7 — Completing the Budget Dashboard with Key Customer Metrics</h3><p>Build a compact <strong>Budget</strong> page that ties your assumptions to outcomes. Add a monthly Matrix (Rows: <em>Start of Month</em>; Values: <strong>Budget (Forecast), Users (BOM), New Users, Churn, Users (EOM), Revenue (EOM)</strong>). Use a Year slicer, then tweak CAC, MRR, Churn, and Budget to see projections update instantly.</p><p>Add a <strong>Customer Metrics</strong> multi-row card to summarize unit economics: <strong>Average Lifetime (months &amp; years), Customer Annual Revenue, LTV, Net LTV, and ROI</strong>. This gives a quick read on whether your acquisition plan makes sense at current CAC and churn.</p><figure><img alt="Power BI budget page with monthly forecast table, parameter sliders, and customer metrics card" src="https://cdn-images-1.medium.com/max/1024/1*Qyv_6PNhfczwVZGx09hGvw.png" /><figcaption>Monthly budget matrix with assumptions slicers and an LTV/ROI summary in Microsoft Power BI.</figcaption></figure><h3>Step 8 — Forecast, Annual Totals, and Sensitivity</h3><p>Turn the monthly forecast into a clean year-over-year view. Add a Matrix grouped by Year and include annual rollups such as beginning-of-year users, total new users, churn, end-of-year users, and annual revenue. This gives you a clear picture of how your assumptions compound across a full budget cycle.</p><p>For a quick trend read, plot <strong>Net User Gain</strong> in an Area chart to see how acquisition minus churn evolves month by month. Then pressure-test your model with a simple sensitivity pass.</p><p>For example, a CAC of 110 dollars with 3 percent churn may yield a healthy LTV, but nudging CAC up or increasing churn quickly erodes ROI. The sliders make it easy to experiment without touching DAX.</p><h3>Continue with the full tutorial and preview the dashboard</h3><p>This post is the high-level walkthrough. To dive into the full build — including every DAX measure, Power Query step, forecast logic, and the downloadable Microsoft Power BI file — visit the complete guide:</p><p>👉 <a href="https://www.challengejp.com/blog/power-bi-marketing-performance-forecast-dashboard/"><strong>Full Marketing Performance &amp; Forecast Dashboard Tutorial</strong></a></p><p>There you can preview the interactive dashboard, explore the full acquisition and revenue model, and download the Power Point file and data package (<strong>20% off with code MEDIUM</strong>) to use in your own reporting workflow.</p><h3>Related Posts</h3><p>Check out my other Medium articles:</p><ul><li><a href="https://medium.com/challengejp/creating-a-customer-churn-dashboard-in-10-steps-5ccd8ea7fe7b"><em>Building a Customer Churn Analysis and Forecast Dashboard</em></a></li><li><a href="https://medium.com/challengejp/how-to-create-a-marketing-investment-plan-in-8-steps-7ac7f89d25af"><em>Creating a Marketing Investment Plan in Microsoft Excel</em></a></li></ul><h3>About the Author</h3><p>Hi, I’m Jacek — I build financial, marketing, and forecasting models that help teams make better decisions with data.</p><p>If you’d like support building your Microsoft Power BI dashboards or want to explore more templates and modeling frameworks, you can reach me or browse more tutorials at <a href="https://www.challengejp.com/"><strong>challengejp.com</strong></a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/0*Pj3lAvtSDCAfVsh8.jpeg" /></figure><p><em>Disclaimer: This tutorial is for educational purposes only and does not constitute financial, accounting, or professional advice.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4d464e5cdf4b" width="1" height="1" alt=""><hr><p><a href="https://medium.com/challengejp/building-a-marketing-performance-forecast-dashboard-in-8-steps-4d464e5cdf4b">Building a Marketing Performance &amp; Forecast Dashboard in 8 Steps</a> was originally published in <a href="https://medium.com/challengejp">ChallengeJP</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a Consolidated P&L and Forecast Dashboard in 8 Steps]]></title>
            <link>https://medium.com/challengejp/building-a-consolidated-p-l-and-forecast-in-8-steps-504b4d775d6e?source=rss----51290e855bf9---4</link>
            <guid isPermaLink="false">https://medium.com/p/504b4d775d6e</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[financial-reporting]]></category>
            <category><![CDATA[financial-modeling]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-analytics]]></category>
            <dc:creator><![CDATA[Jacek Polewski]]></dc:creator>
            <pubDate>Sun, 02 Nov 2025 11:26:04 GMT</pubDate>
            <atom:updated>2025-11-30T22:03:59.013Z</atom:updated>
            <content:encoded><![CDATA[<p>In this guide, we’ll append accounting data from each entity, map local accounts into a standard P&amp;L structure, and convert everything into a single reporting currency with FX logic — all in Microsoft Power BI.</p><p>Then we’ll build a monthly P&amp;L with Gross/Operating Profit, add MoM variance analysis, drill into subsidiary and client profitability, and finish with a 12-month forecast driven by adjustable Revenue, COGS, OPEX, and FX assumptions.</p><p><em>(All screenshots and examples come from the full tutorial, which you can access — along with data, DAX code, and templates — via the link at the end.)</em></p><h3>Step 1 — Load &amp; Append Data</h3><p>We’ll start by loading accounting files from each subsidiary and combining them into one dataset.</p><p>In <strong>Microsoft Power BI</strong>, import your source files via <strong>Home → Get Data → Text/CSV</strong> and load all four transaction files (US, UK, JP, DE). Then open <strong>Power Query</strong> to review column types and sample values — this ensures every file loads consistently before you consolidate them.</p><p>Next, choose <strong>Append Queries as New</strong> and select all four Fact tables. This creates a single combined dataset, which you can rename Data_Consolidated. From here on, we’ll treat it as our master ledger for revenue, COGS, and operating expenses across every entity.</p><figure><img alt="Appending multiple subsidiary accounting files in Power Query" src="https://cdn-images-1.medium.com/max/1024/1*753KgPywFhx9LX5XlUlnBw.png" /><figcaption>Combining multi-entity financial data into one consolidated dataset using Power Query.</figcaption></figure><h3>Step 2 — Map Accounts &amp; Build the Data Model</h3><p>Next, load the FxRates and MapAccounts tables and switch to <strong>Model view</strong>. These lookup tables let us standardize local accounts into a single chart of accounts and convert each currency into the reporting currency.</p><p>Create a Calendar table using a simple DAX expression so you can aggregate by month and support future periods. Mark it as the official Date table.</p><p>Then, in <strong>Power Query</strong>, add a LocalKey column to both the consolidated data and the account mapping table (Subsidiary + LocalAccountCode). This becomes the join key that links each transaction to the correct standardized account category.</p><p>Back in Model view, connect:</p><ul><li>MapAccounts[LocalKey] → Data_Consolidated[LocalKey]</li><li>FxRates[Currency] → Data_Consolidated[Currency]</li><li>Calendar[Date] → Data_Consolidated[TxnDate]</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LT2tYvVlkC-vVuGK8L0VzA.png" /><figcaption>Building a Power BI data model for multi-subsidiary financial consolidation with FX and chart-of-accounts mapping.</figcaption></figure><h3>Step 3 — Build the Monthly P&amp;L</h3><p>Now let’s turn raw transactions into a clean Profit &amp; Loss view.</p><p>Start by converting every local-currency amount into the reporting currency. In Microsoft Power BI, add a calculated column to pull in the FX rate, then multiply it by the transaction amount to create a reporting currency value.</p><p>Next, create a small <strong>Measures</strong> table and add measures for reporting amount, account sign logic (Revenue positive / COGS &amp; OPEX negative), and actual-period totals.</p><p>With those basics in place, insert a Matrix visual: put Year-Month on rows, CommonCategory (Revenue, COGS, OPEX) on columns, and your Actual Amount measure as values. You now have a consolidated monthly P&amp;L across every subsidiary.</p><figure><img alt="DAX columns calculating FX rate and reporting currency amount" src="https://cdn-images-1.medium.com/max/1024/1*CCtJEUivxddX7gtzqf8Yag.png" /><figcaption>Converting local currency to a single reporting currency in Power BI for consolidated P&amp;L reporting.</figcaption></figure><h3><strong>Step 4 — Add Gross &amp; Operating Profit</strong></h3><p>Next, calculate the core profitability metrics: Revenue, COGS, OPEX, Gross Profit, and Operating Profit — plus their margins. These build directly on the signed reporting amounts from the previous step.</p><p><strong>Example — Revenue (Actual)</strong></p><pre>Revenue (Actual) =<br>CALCULATE(<br>    [Amount (Reporting, Signed)],<br>    Calendar[IsActual] = TRUE,<br>    MapAccounts[CommonCategory] = &quot;Revenue&quot;<br>)</pre><p>Once the measures are in place, add them to your monthly matrix. You’ll now see consolidated Revenue, Gross Profit, and Operating Profit — with margin trends that highlight how the business performs across time.</p><p>To drill into performance by entity, create a simple Subsidiary table and use it as a slicer. This lets you toggle between consolidated results and individual-country P&amp;Ls.</p><figure><img alt="Creating a consolidated Profit &amp; Loss statement in Microsoft Power BI with margin metrics" src="https://cdn-images-1.medium.com/max/1024/1*1hms8glg5vyBVo9OtgSosw.png" /><figcaption>Power BI P&amp;L matrix with Revenue, COGS, OPEX, Gross Profit, and Operating Profit.</figcaption></figure><h3>Step 5 — Add Variance Analysis &amp; Client Profitability</h3><p>To surface month-on-month movement, add MoM DAX measures for Revenue, COGS, OPEX, Gross Profit, and Operating Profit using Power BI’s DATEADD function to pull prior-month values and calculate change.</p><p><strong>Example — Revenue MoM Δ</strong></p><pre>Revenue MoM Δ (Actual) =<br>[Revenue (Actual)] - CALCULATE([Revenue (Actual)], DATEADD(Calendar[Date], -1, MONTH))</pre><p>Add these MoM measures to your P&amp;L matrix to highlight growth or cost swings. Apply basic conditional formatting to instantly spot negative variances.</p><p>Next, create a simple Clients table (via VALUES) and add client views to see <strong>top contributors</strong> and <strong>least-profitable clients</strong>. This breaks performance down beyond subsidiaries and reveals where margin strength — or leakage — really sits.</p><figure><img alt="P&amp;L with month-over-month variance and client profitability view" src="https://cdn-images-1.medium.com/max/1024/1*8GKqNpdlLt7158J4LocCCQ.png" /><figcaption>Analyzing monthly variances and identifying top and least profitable clients in consolidated P&amp;L reporting</figcaption></figure><h3>Step 6 — Complete the P&amp;L Dashboard</h3><p>With the core measures ready, build an executive-ready dashboard. Start with a <strong>Line + Clustered Column</strong> chart to show Revenue, COGS, and OPEX by month, with Operating Profit as a line overlay. This gives a clean view of growth, cost structure, and profitability in one place.</p><p>For smoother trend insight, add a simple 3-month moving average for Revenue. Then create a second chart comparing subsidiaries side-by-side for the year — useful for spotting regional performance gaps.</p><p>Round it out with client visuals: a Top Clients view for revenue concentration, and a Bottom Clients view to surface low-margin accounts. Add year and subsidiary slicers to let users explore the data without touching DAX.</p><figure><img alt="Microsoft Power BI P&amp;L dashboard with subsidiary comparison and client insights" src="https://cdn-images-1.medium.com/max/1024/1*zAAb3BE71JGrY-yljP4psQ.png" /><figcaption>Visualizing consolidated financial performance and subsidiary profitability with a Power BI dashboard.</figcaption></figure><h3>Step 7 — Add Forecast Logic &amp; Parameters</h3><p>To turn the P&amp;L into a planning model, add <strong>parameters</strong> that control next-year assumptions for Revenue, COGS, OPEX, and FX. These act like input drivers so you can test scenarios without touching formulas.</p><p>Then build forecast measures that start from prior-year actuals, apply the YoY assumptions, and adjust non-US values for currency movement.</p><p><strong>Example — Revenue forecast logic</strong></p><pre>Revenue (Forecast) =<br>VAR YoY = SELECTEDVALUE(&#39;YoY Revenue Change %&#39;[YoY Revenue Change %], 0)<br>VAR FxAdj = [FX Adj Factor (Non-US)]<br>RETURN<br>CALCULATE(<br>    SUMX(<br>        VALUES(Subsidiary[Subsidiary]),<br>        [Revenue (PY Actual)] * (1 + YoY) * FxAdj<br>    ),<br>    Calendar[IsForecast] = TRUE()<br>)</pre><p>Repeat the same structure for COGS and OPEX, then calculate Gross Profit, Operating Profit, and margins for forecast months.</p><figure><img alt="Creating a parameter for forecast modeling in Microsoft Power BI" src="https://cdn-images-1.medium.com/max/679/1*UHUA3BMdn0_i5MFrcHMOxg.jpeg" /><figcaption>Adding driver-based forecast parameters in Power BI to model revenue, cost, and FX scenarios.</figcaption></figure><h3>Step 8 — Build the Forecast Dashboard &amp; Sensitivity Tools</h3><p>Create a Forecast page and add your forecast measures to a monthly matrix. Then add slicers for the YoY Revenue, COGS, OPEX, and FX assumptions so you can run scenarios directly in <strong>Microsoft Power BI</strong> — no editing DAX required.</p><p>Add a Line + Column chart to compare forecast Revenue, COGS, and OPEX against Operating Profit. For smoother trend insight, you can include a rolling average. Then add a yearly view using <strong>Actual + Forecast</strong> stitched measures so the transition between historicals and forecast periods is seamless.</p><p>With the sliders in place, you now have a forecast model you can adjust live — a simple way to test growth, cost discipline, and currency scenarios across subsidiaries.</p><figure><img alt="Power BI forecast dashboard with scenario sliders and monthly projections" src="https://cdn-images-1.medium.com/max/1024/1*qzNcUy-sbJ5B_-ff-5Ok5g.png" /><figcaption>Building a driver-based financial forecast Power BI dashboard for scenario and sensitivity analysis.</figcaption></figure><h3>Continue with the full tutorial and preview the dashboard</h3><p>This post is the high-level walkthrough.</p><p>To dive into the full build — including every DAX measure, Power Query step, calendar logic, and the downloadable Microsoft Power BI file — visit the complete guide:</p><p>👉 <a href="https://www.challengejp.com/blog/power-bi-consolidated-pl-forecast-tutorial-template/"><strong>Full Consolidated P&amp;L &amp; Forecast Dashboard Tutorial</strong></a></p><p>There you can preview the interactive dashboard, explore the consolidated model across subsidiaries and currencies, and download the full Power BI file and data template (20% off with code <strong>MEDIUM</strong>) to use in your own reporting stack.</p><h3>Related Posts</h3><p>Check out my other Medium articles:</p><ul><li><a href="https://medium.com/challengejp/how-to-create-a-merger-and-acquistion-model-in-8-steps-f245bc89c114"><em>Building a Mergers and Acquistions Financial Model</em></a></li><li><a href="https://medium.com/challengejp/how-to-create-a-cash-flow-forecast-in-10-steps-1ce8b218fa99"><em>Creating a Cash Flow Forecast in Microsoft Excel</em></a></li></ul><h3>About the Author</h3><p>Hi, I’m Jacek — I build financial models and analytics systems that help teams make better decisions with data.</p><p>If you’d like support building your Microsoft Power BI dashboards or want to explore more templates and forecasting frameworks, you can reach me or browse more tutorials at <a href="https://www.challengejp.com/"><strong>challengejp.com</strong></a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/0*AwiK-08zn7ZFfdrz.jpeg" /></figure><p><em>Disclaimer: This tutorial is for educational purposes only and does not constitute financial, accounting, or professional advice.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=504b4d775d6e" width="1" height="1" alt=""><hr><p><a href="https://medium.com/challengejp/building-a-consolidated-p-l-and-forecast-in-8-steps-504b4d775d6e">Building a Consolidated P&amp;L and Forecast Dashboard in 8 Steps</a> was originally published in <a href="https://medium.com/challengejp">ChallengeJP</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Creating a Customer Churn and Revenue Forecast Dashboard in 10 Steps]]></title>
            <link>https://medium.com/challengejp/creating-a-customer-churn-dashboard-in-10-steps-5ccd8ea7fe7b?source=rss----51290e855bf9---4</link>
            <guid isPermaLink="false">https://medium.com/p/5ccd8ea7fe7b</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[revenue-forecasting]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[customer-retention]]></category>
            <dc:creator><![CDATA[Jacek Polewski]]></dc:creator>
            <pubDate>Sun, 26 Oct 2025 19:01:03 GMT</pubDate>
            <atom:updated>2025-12-05T15:11:40.604Z</atom:updated>
            <content:encoded><![CDATA[<p>Customer churn tells you how many customers are leaving. But when paired with revenue forecasting, it tells you <em>why</em> your business is growing — or shrinking — and how to take control of it.</p><p>In this guide, you’ll design a complete churn and revenue forecast solution in <strong>Microsoft Power BI</strong>, starting from raw CSV data and ending with an interactive dashboard that helps you test growth scenarios and pricing assumptions in real time.</p><p><em>(All screenshots and examples come from the full tutorial, which you can access — along with data, DAX code, and templates — via the link at the end.)</em></p><h3>Step 1 — Start with clean customer data</h3><p>Every analysis begins with data quality. If you’ve tried to calculate churn using messy exports or inconsistent date formats, you know how quickly errors cascade. In Microsoft Power BI, well-structured data isn’t optional — it’s the foundation.</p><p>We’ll use four CSV files:</p><ul><li><strong>signups_data</strong> — when and how customers joined</li><li><strong>cancellations_data</strong> — when they left</li><li><strong>pricing_plan_lookup</strong> — plan name → monthly price</li><li><strong>customer_lookup</strong> — customer profiles and IDs</li></ul><p>Import via <em>Home → Get Data → Text/CSV</em>, then open Power Query to clean up. Standardize date fields by converting Date/Time to pure Date.</p><figure><img alt="Screenshot showing the Power Query Editor where Date/Time fields are converted to Date-only columns." src="https://cdn-images-1.medium.com/max/1024/1*kRJj0ytwSuEOtsDq5fhLkw.png" /><figcaption>Transforming Date/Time into Date only columns in Power Query</figcaption></figure><p><em>(Tip: the complete Power Query setup, with screenshots and applied steps, is included in the </em><a href="https://www.challengejp.com/blog/power-bi-customer-churn-revenue-forecast-tutorial/"><em>full tutorial</em></a><em>.)</em></p><h3>Step 2— Build a dynamic calendar table</h3><p>Once the base data is clean, create a <strong>Calendar</strong> table. This is one of the most overlooked parts of a solid Power BI model; without it, time-based calculations — like churn by month or monthly revenue — are impossible.</p><p>With a short DAX expression, generate a <strong>dynamic</strong> calendar that starts at your earliest signup or cancellation and extends five years beyond the last event to support forecasts.</p><figure><img alt="DAX formula view in Microsoft Power BI showing how to generate a dynamic calendar table extending beyond actual data for forecasts." src="https://cdn-images-1.medium.com/max/1024/1*hR_PIG-xYZQITNCMu5DeUQ.png" /><figcaption>Autogenerated calendar created with DAX</figcaption></figure><p>Mark it as your official Date table. It becomes the single timeline connecting all your data — past and future — and forms the backbone for churn, retention, and forecast logic.</p><h3>Step 3 — Connect the tables with relationships</h3><p>The model is only as smart as its relationships. These links tell Microsoft Power BI how tables communicate — tying customer details, signups, cancellations, and calendar dates into one coherent story.</p><figure><img alt="Power BI Model View displaying relationships between customer lookup, signups, cancellations, and calendar tables." src="https://cdn-images-1.medium.com/max/1024/1*pv1Vh2M_H760qUsUmHKM5A.png" /><figcaption>Relationship view showing one-to-many links</figcaption></figure><p>This star schema ensures every churn rate or retention metric pulls from the correct customer and date. It’s the moment flat CSVs become an analytical model.</p><h3>Step 4— Add cohorts for retention analysis</h3><p>Understanding <em>who</em> churns — and <em>when</em> — is where cohort analysis shines. By assigning each customer to a signup month (their <em>cohort</em>), you can track how long they stay before canceling.</p><p>Create calculated columns in customer_lookup such as:</p><ul><li>first_signup_date – the customer’s first signup</li><li>signup_cohort_date – normalized to the first day of that month</li><li>signup_cohort_label – formatted as “YYYY-MM”</li></ul><figure><img alt="Power BI Data View showing calculated DAX columns that assign customers to monthly signup cohorts." src="https://cdn-images-1.medium.com/max/1024/1*nbfFAKGoG6C0jPqpP5Ra4g.png" /><figcaption>Creating DAX columns for customer cohorts</figcaption></figure><p>This turns raw timestamps into meaningful segments — letting you compare January 2023 signups with March 2024, or evaluate whether recent acquisition pushes attract more churn-prone customers.</p><h3>Step 5 — Create DAX measures that bring the model to life</h3><p>Now comes the analytical core. DAX measures translate data into business KPIs. Start with:</p><ul><li><strong>New Signups</strong></li><li><strong>Churned Customers</strong></li><li><strong>Active Customers</strong> (cumulative signups minus cumulative cancels)</li><li><strong>Churn Rate</strong> (churned ÷ beginning-of-month active)</li></ul><p>A simple but powerful example:</p><pre>Churn Rate :=<br>DIVIDE ( [Churned Customers], [BOM Active Customers] )</pre><blockquote><em>This measure calculates the monthly churn rate as the percentage of customers lost compared to the number active at the start of the month — a clear snapshot of customer health.</em></blockquote><p>These measures turn your dataset into a living report — tracking inflow, outflow, and retention trends over time.</p><p><em>You can find the complete DAX library, including cumulative metrics and forecast logic, in the </em><a href="https://www.challengejp.com/blog/power-bi-customer-churn-revenue-forecast-tutorial/"><em>full tutorial</em></a><em>.</em></p><h3>Step 6 — Visualize growth and churn</h3><p>With measures ready, make the data speak visually:</p><ul><li>A <strong>monthly matrix</strong> shows inflows, outflows, active users, and churn %.</li><li>A <strong>yearly matrix</strong> summarizes long-term performance.</li><li><strong>Slicers</strong> let you filter by year or pricing plan.</li><li>A <strong>Line + Column chart</strong> overlays new signups against churn rate — so you can spot whether rapid growth hides retention issues.</li></ul><figure><img alt="Power BI report page displaying matrices, slicers, and charts tracking customer signups, churn, and active base over time." src="https://cdn-images-1.medium.com/max/1024/1*zBTFyE-kyITaRRyrseQQzQ.png" /><figcaption>Interactive page with churn and growth visuals</figcaption></figure><p><em>(If you’d like to follow along using the same dataset, the </em><a href="https://www.challengejp.com/blog/power-bi-customer-churn-revenue-forecast-tutorial/"><em>original tutorial</em></a><em> includes a live preview of the dashboard and download links.)</em></p><p>At this point, your Power BI report starts looking like an executive dashboard. You can spot spikes in churn, dips in signups, or months of steady growth — without touching a spreadsheet.</p><h3>Step 7 — Deep dive: cohort retention</h3><p>Monthly churn gives you the “what.” Cohorts explain the “why.”</p><p>Cohort analysis tracks how customers who joined in the same month behave over time. Maybe January 2023 users stick around twice as long as June 2024 users. That insight can reshape acquisition, onboarding, or pricing.</p><figure><img alt="PAMatrix visual with conditional formatting showing customer retention percentages by cohort and months since signup." src="https://cdn-images-1.medium.com/max/1024/1*2ezjDiOq-yJTDY6INsqPlQ.png" /><figcaption>Retention heatmap highlighting which cohorts hold customers longer</figcaption></figure><p>Use conditional formatting to create a color gradient — dark shades for high retention, light for decay. This instantly spotlights where loyalty is rising or fading.</p><h3>Step 8 — Plot retention curves by plan</h3><p>The heatmap gives an overview; retention curves reveal the timeline.</p><p>Use an area or line chart with <strong>months since signup</strong> on the X-axis and <strong>retention %</strong> on the Y-axis, with <strong>pricing plan</strong> as the legend.</p><figure><img alt="An Area Chart comparing retention curves across pricing plans, with slicers for signup dates and cohorts." src="https://cdn-images-1.medium.com/max/1024/1*liuV1mBd0syZzc_XBHaoEQ.png" /><figcaption>Retention curves comparing subscription plans</figcaption></figure><p>This view surfaces behavioral differences by plan — perhaps the premium plan retains longer due to onboarding quality, or the entry plan churns faster because it attracts trial users.</p><h3>Step 9 — Introduce forecasting parameters</h3><p>Once you understand the past, it’s natural to ask, <em>What if things change?</em></p><p>Add <strong>parameters</strong> to simulate future growth: churn rates, plan mix, starting and target signups, growth duration, and monthly revenue per plan. These interactive controls turn your Power BI dashboard into a scenario tool.</p><figure><img alt="Microsoft Power BI dialog box showing creation of numeric parameters for churn rate, growth, and pricing plan assumptions." src="https://cdn-images-1.medium.com/max/720/1*U6AT7V_lkymsJDc1ddXSLw.png" /><figcaption><strong>Defining numeric parameters for forecasts</strong></figcaption></figure><p>Adjust churn from 5 % to 8 %, or shift plan share — and see projected customers and revenue update instantly. Decision-makers get answers without touching DAX.</p><h3>Step 10 — Visualize future growth and revenue</h3><p>Finally, merge historical actuals with forecast measures into one page:</p><ul><li><strong>Forecasted new signups and churn</strong> by month</li><li><strong>Projected active customers</strong> over time</li><li><strong>Monthly Recurring Revenue (MRR)</strong> by plan</li><li><strong>Annual summaries</strong> for executive snapshots</li></ul><figure><img alt="Power BI dashboard displaying forecasted monthly customers, churn, and revenue by pricing plan with parameter slicers." src="https://cdn-images-1.medium.com/max/1024/1*pciGxCRVTanRvQuVWnsPng.png" /><figcaption>Forecast dashboard with interactive growth and revenue projections</figcaption></figure><p>This isn’t just a dashboard — it’s a decision model. Leaders can ask “what if” and <em>see</em> the answer.</p><h3>Why this approach works</h3><p>The power here isn’t the charts — it’s the integration of logic and data. By modeling cohorts, churn, and forecasts together, you move from <strong>descriptive analytics</strong> (what happened) to <strong>prescriptive analytics</strong> (what could happen next).</p><p>It’s the difference between reporting churn after it’s too late and <strong>forecasting it before it happens</strong>.</p><h3>Continue with the full tutorial and preview the dashboard</h3><p>This post is the high-level walkthrough. To explore the interactive version — and access every DAX formula, Power Query step, and the downloadable Power BI file — visit the full guide:</p><p>👉 <a href="https://www.challengejp.com/blog/power-bi-customer-churn-revenue-forecast-tutorial/"><strong>Full 12-Step Customer Churn &amp; Revenue Forecast Tutorial</strong></a></p><p>You can <strong>preview the full dashboard live</strong> in the embedded demo and <strong>download the Power BI template (20% off with code Medium)</strong> to use it in your own projects.</p><h3>Related Posts</h3><p>Check out my other Medium articles:</p><ul><li><a href="https://medium.com/challengejp/how-to-create-a-subscription-model-forecast-in-8-steps-4518d9483704"><em>Creating a Subscription Model Forecast in Microsoft Excel</em></a></li><li><a href="https://medium.com/challengejp/building-a-marketing-performance-forecast-dashboard-in-8-steps-4d464e5cdf4b"><em>Building a Marketing Performance &amp; Forecast Dashboard</em></a></li></ul><h3>About the Author</h3><p>Hi, I’m Jacek — I love spreadsheets and data analytics, and I enjoy helping people make better decisions through financial planning and data-driven insights.</p><p>If you need help building a Power BI dashboard or want to explore data analytics and forecasting models, feel free to reach out or explore more tutorials and templates at <a href="https://www.challengejp.com/"><strong>challengejp.com</strong></a><strong>.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/0*TkudpCsABh9qiIga.jpeg" /></figure><p><em>Disclaimer: This tutorial is for general informational and educational purposes only and is not a substitute for professional advice.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5ccd8ea7fe7b" width="1" height="1" alt=""><hr><p><a href="https://medium.com/challengejp/creating-a-customer-churn-dashboard-in-10-steps-5ccd8ea7fe7b">Creating a Customer Churn and Revenue Forecast Dashboard in 10 Steps</a> was originally published in <a href="https://medium.com/challengejp">ChallengeJP</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Create a Cash Flow Forecast in 10 Steps]]></title>
            <link>https://medium.com/challengejp/how-to-create-a-cash-flow-forecast-in-10-steps-1ce8b218fa99?source=rss----51290e855bf9---4</link>
            <guid isPermaLink="false">https://medium.com/p/1ce8b218fa99</guid>
            <category><![CDATA[excel]]></category>
            <category><![CDATA[financial-modeling]]></category>
            <category><![CDATA[startup-finance]]></category>
            <category><![CDATA[investment-analysis]]></category>
            <category><![CDATA[cash-flow-forecasting]]></category>
            <dc:creator><![CDATA[Jacek Polewski]]></dc:creator>
            <pubDate>Sun, 12 Jan 2025 13:06:38 GMT</pubDate>
            <atom:updated>2025-05-10T09:11:01.832Z</atom:updated>
            <content:encoded><![CDATA[<p>This tutorial explains how to build a cash flow forecast using Microsoft Excel.</p><p>I’ll start by setting up the model’s main assumptions and linking them to monthly revenue and cost calculations. Then, I’ll walk through forecasting capital needs, estimating returns, and testing scenarios.</p><p>The final steps cover how to link the cash flow forecast to other core financial statements — like the balance sheet and profit and loss — and how to incorporate debt if needed.</p><h3><strong>Step 1. </strong>Define Key Assumptions</h3><p>Start by creating a sheet showing your cash flow model’s main assumptions — especially the key drivers of revenue and cost.</p><p>Place this sheet at the top of your model so users can easily understand and update it.</p><p>Add a column for notes to briefly explain the basis for each input when sharing the file.</p><figure><img alt="A list of a cash flow forecast assumptions." src="https://cdn-images-1.medium.com/max/1024/1*XURX4X7Lx_QhuhF6VdgSvw.png" /></figure><h3><strong>Step 2. </strong>Link Assumptions to Revenue and Costs</h3><p>Link your assumptions to a sheet showing how they drive monthly cash flow. Present the logic clearly so others can trace how each figure is calculated.</p><p>For example, you might forecast sales volume over time and multiply it by average price to calculate monthly revenue.</p><figure><img alt="A spreadsheet with monthly user revenue projections." src="https://cdn-images-1.medium.com/max/1024/1*c1DMjpXMQ5PzgvEYA6Gttw.png" /></figure><h3><strong>Step 3. </strong>Use Clear, Simple Formulas</h3><p>Assume your audience has moderate Excel skills, and keep formulas clean.</p><p>In most cases, you can build a working forecast using standard functions like <strong>SUM</strong>, <strong>IF</strong>, <strong>SUMIF</strong>, and <strong>COUNTIF</strong>.</p><p>Prioritise clarity and transparency over complexity.</p><figure><img alt="An example of using Excel SUMIF formula." src="https://cdn-images-1.medium.com/max/821/1*V7hFTBA-xqWK4DPUJIJYSQ.png" /></figure><h3><strong>Step 4. </strong>Build a Cash Flow Summary</h3><p>Aggregate the output into a summary view that shows how key drivers shape the overall result.</p><p>For example, list annual customer numbers, revenues, operating costs, capex, and net cash flow. This makes the forecast presentation-ready.</p><figure><img alt="An annual summary of a cash flow forecast." src="https://cdn-images-1.medium.com/max/823/1*RZQe6LV3iGfuxBqfHm492w.png" /></figure><h3><strong>Step 5. </strong>Estimate Funding Needs and Use of Funds</h3><p>Identify the peak cash deficit to estimate how much funding the business will need.</p><p>Then, clearly show how the funds will be spent — across categories like opex, marketing, product development, etc.</p><p>A visible “use of funds” summary builds trust with stakeholders and potential investors.</p><figure><img alt="Breakdown of equity requirement and the use of funds." src="https://cdn-images-1.medium.com/max/1024/1*Bc2SHTUxV73f9h--6zaqog.png" /></figure><h3><strong>Step 6. </strong>Calculate Return on Investment</h3><p>There are several ways to value a business. One is to apply a revenue or EBITDA multiplier to future earnings or cash flows and discount them back to present value.</p><p>You can also use a <strong>Discounted Cash Flow (DCF)</strong> method with year-specific discount rates to reflect risk over time.</p><figure><img alt="Discounted cash flow calculations and the return on investment calculations." src="https://cdn-images-1.medium.com/max/723/1*pBgajTkMqj4KfYo2YvVJLQ.png" /></figure><h3><strong>Step 7. </strong>Add NPV, IRR, and Other Metrics</h3><p>Include key metrics such as:</p><ul><li>Net Present Value (NPV)</li><li>Internal Rate of Return (IRR)</li><li>Equity requirement</li><li>Break-even point</li></ul><p>Use Excel’s built-in IRR and NPV formulas. Break-even outputs show when the business starts generating positive returns.</p><figure><img alt="An example of calculating IRR and NPV in Microsoft Excel." src="https://cdn-images-1.medium.com/max/1015/1*XSG5yexagMN3J9yqQHXhfg.png" /></figure><h3><strong>Step 8. </strong>Test Scenarios and Sensitivities</h3><p>Create different input combinations to test upside and downside outcomes.</p><p>Focus on a few sensitive drivers like revenue growth, churn, or pricing. Use data tables or dropdowns to switch between cases quickly.</p><figure><img alt="Cash flow assumptions with scenarios." src="https://cdn-images-1.medium.com/max/1024/1*E1EuF8ONZmnhBxD2_V1vCQ.png" /></figure><h3><strong>Step 9. </strong>Include Financial Statements</h3><p>Add formal statements to support your forecast:</p><ul><li>Cash Flow Statement</li><li>Profit &amp; Loss Statement</li><li>Balance Sheet</li></ul><p>Ensure they’re interlinked — for example, net profit from P&amp;L should flow into retained earnings in the balance sheet.</p><figure><img alt="An example of a profit and loss statement in Microsoft Excel." src="https://cdn-images-1.medium.com/max/1024/1*326S1wkagnz0CO_x1TpKig.png" /></figure><h3><strong>Step 10. </strong>Model Debt and Repayments</h3><p>If debt is involved, show repayments over time. Split them into:</p><ul><li>Interest expense (affecting P&amp;L)</li><li>Principal repayments (affecting cash and liabilities)</li></ul><p>Use Microsft Excel’s <strong>PMT</strong> function to calculate consistent loan repayments if applicable.</p><figure><img alt="A spreadsheet with interest and debt repayment calculations." src="https://cdn-images-1.medium.com/max/889/1*g88XFcFECbZVKUmYJupl5A.png" /></figure><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FMZP4xHw5yzU%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DMZP4xHw5yzU&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FMZP4xHw5yzU%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/7776397f66437d6cf560ff748bd3c739/href">https://medium.com/media/7776397f66437d6cf560ff748bd3c739/href</a></iframe><h3>📥 Download My Cash Flow Forecast Template</h3><p>To learn more — or to download the Excel file used in this tutorial — visit:<br> 👉 <a href="https://www.challengejp.com/blog/cash-flow-forecast-excel-tutorial/">https://www.challengejp.com/blog/cash-flow-forecast-excel-tutorial/</a><br>Use code <strong>MEDIUM</strong> for 20% off your first order.</p><h3>About the Author</h3><p>Hi, I’m Jacek — I love spreadsheets and enjoy helping people with their financial planning and data.</p><p>If you need help building a cash flow forecast or want to explore Excel-based modelling, feel free to reach out or explore more tutorials and templates at <a href="https://www.challengejp.com/">challengejp.com</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/0*4j20JiI8wgQiR6hV.jpeg" /></figure><p><em>Disclaimer: This tutorial is for general informational and educational purposes only and is not a substitute for professional advice.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1ce8b218fa99" width="1" height="1" alt=""><hr><p><a href="https://medium.com/challengejp/how-to-create-a-cash-flow-forecast-in-10-steps-1ce8b218fa99">How to Create a Cash Flow Forecast in 10 Steps</a> was originally published in <a href="https://medium.com/challengejp">ChallengeJP</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Create a Financial Model with Debt Financing in 8 Steps]]></title>
            <link>https://medium.com/challengejp/how-to-create-a-financial-model-with-debt-financing-in-8-steps-65e0a638fb12?source=rss----51290e855bf9---4</link>
            <guid isPermaLink="false">https://medium.com/p/65e0a638fb12</guid>
            <category><![CDATA[excel-templates]]></category>
            <category><![CDATA[financial-modeling]]></category>
            <category><![CDATA[cash-flow-forecast]]></category>
            <category><![CDATA[excel-tutorial]]></category>
            <category><![CDATA[debt-financing]]></category>
            <dc:creator><![CDATA[Jacek Polewski]]></dc:creator>
            <pubDate>Sun, 15 Dec 2024 13:06:01 GMT</pubDate>
            <atom:updated>2025-05-10T09:28:42.127Z</atom:updated>
            <content:encoded><![CDATA[<p>This tutorial discusses creating a financial model with debt financing in Microsoft Excel. It starts with forecasting sales, revenue, costs, and cash flow.</p><p>Then, it covers using debt to fund shortfalls and finishes with estimating equity needs and return on investment.</p><h3><strong>Step 1. </strong>Forecast Sales and Revenue Growth</h3><p>Begin your spreadsheet by projecting monthly sales. Then multiply by average price or unit revenue to calculate top-line income.</p><p>Include growth rates or pricing assumptions as inputs so you can test multiple scenarios.</p><figure><img alt="spreadsheet with revenue and cost forecast" src="https://cdn-images-1.medium.com/max/1024/1*opbQEPh_9HQ8VB1SBZeHFQ.png" /></figure><h3><strong>Step 2. </strong>Estimate Costs and Monthly Cash Flow</h3><p>Organize your forecast into key cost categories to better track spending and understand how it evolves over time.</p><p>Separate your forecast into:</p><ul><li><strong>Variable costs</strong> (e.g. cost of sales),</li><li><strong>Fixed costs</strong> (e.g. salaries, admin),</li><li><strong>Capital expenses</strong> (e.g. software, equipment).</li></ul><p>Keeping these categories distinct makes it easier to update and analyse how costs scale with revenue.</p><figure><img alt="financial model annual summary with assumptions" src="https://cdn-images-1.medium.com/max/1024/1*rtNAD_1a0ePhDnt7_Hpsfg.png" /></figure><h3><strong>Step 3. </strong>Calculate Total Funding Need and Capital Mix</h3><p>Use the monthly cash flow forecast to calculate <strong>cumulative cash balance</strong>. The lowest point represents the peak funding need.</p><p>Early-stage businesses often rely more on equity. Debt becomes viable when revenue is more stable and predictable.</p><figure><img alt="graph showing mix of debt and equity financing" src="https://cdn-images-1.medium.com/max/800/1*2gpirWIwHUslvqRLII_3hQ.png" /></figure><h3><strong>Step 4. </strong>Build a Debt Schedule and Repayment Plan</h3><p>Set up a clear debt schedule to track how borrowed funds are drawn, repaid, and reduced over time.</p><p>Create a table with:</p><ul><li>Opening balance,</li><li>New debt drawn,</li><li>Repayments (split into interest and principal),</li><li>Closing balance.</li></ul><p>Calculate interest on the <strong>opening balance</strong>, and reduce principal from total repayments to keep the debt schedule accurate.</p><figure><img alt="spreadsheet with debt calculations" src="https://cdn-images-1.medium.com/max/1024/1*IyaR3gwNBZJ7pxiy6muGGA.png" /></figure><h3><strong>Step 5. </strong>Estimate Return on Equity and Exit Value</h3><p>Subtract debt funding from the total capital need to estimate the equity required.</p><p>Calculate exit value using an EBITDA multiplier. Then adjust for outstanding liabilities and cash to arrive at projected returns using Microsoft Excel’s <strong>IRR</strong> and <strong>NPV</strong> formulas.</p><figure><img alt="example of return on investment with debt financing" src="https://cdn-images-1.medium.com/max/1024/1*PzcsFMbbp80lSeEAFJhg1g.png" /></figure><h3><strong>Step 6. </strong>Use Covenants to Test Debt Capacity</h3><p>Use debt covenants as checkpoints to avoid overleveraging. Common ratios include:</p><ul><li><strong>DSCR</strong>: earnings vs debt payments.</li><li><strong>Debt-to-Equity</strong>: capital structure health.</li><li><strong>Debt-to-EBITDA</strong>: repayment risk.</li></ul><p>If any ratios fall outside industry norms, adjust the model accordingly.</p><figure><img alt="example of debt covenants calculations" src="https://cdn-images-1.medium.com/max/1002/1*VBpc4vsxpW1L9RlBz620-A.png" /></figure><h3><strong>Step 7. </strong>Apply Excel Formulas for Debt Modelling</h3><p>Microsoft Excel offers built-in financial functions that make it easier to model debt, repayments, and returns accurately:</p><ul><li><strong>PV</strong> — calculates the present value of future revenue.</li><li><strong>PMT</strong> — estimates equal loan repayments.</li><li><strong>IRR / NPV</strong> — assesses returns and business value.</li></ul><p>These functions help simplify complex financial modeling under different assumptions.</p><figure><img alt="example of using pv formula to calculate debt" src="https://cdn-images-1.medium.com/max/977/1*byaFX5BUfqUiLvc2IpbYkA.png" /></figure><h3><strong>Step 8. </strong>Link Forecast to Financial Statements</h3><p>Summarize your forecast by mapping it to the three core financial statements:</p><ul><li><strong>Profit &amp; Loss</strong> — tracks revenue, costs, and profit.</li><li><strong>Cash Flow</strong> — shows operating, investing, and financing activities.</li><li><strong>Balance Sheet</strong> — reflects assets, liabilities, and equity, including debt movements.</li></ul><p>Make sure all three statements are linked, so changes in one flow through the rest.</p><figure><img alt="balance sheet with annual assets and liabilities" src="https://cdn-images-1.medium.com/max/1024/1*dhwvxWqYEEs4pWXuWxZRhA.png" /></figure><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FXEFj8rTQ_2o%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DXEFj8rTQ_2o&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FXEFj8rTQ_2o%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/4931a2f2f2cbf6d0933ec82b1d406b2f/href">https://medium.com/media/4931a2f2f2cbf6d0933ec82b1d406b2f/href</a></iframe><h3>📥 Download My Financial Model with Debt Financing Template</h3><p>To learn more — or to download the Excel file used in this tutorial — visit:<br> 👉 <a href="https://www.challengejp.com/blog/financial-model-debt-financing-excel-tutorial/">https://www.challengejp.com/blog/financial-model-debt-financing-excel-tutorial/</a><br>Use code <strong>MEDIUM</strong> for 20% off your first order.</p><h4>About the Author</h4><p>Hi, I’m Jacek — I love spreadsheets and enjoy helping people with their financial planning and data.</p><p>If you need help building a debt-funded forecast or want to explore Excel-based financial modelling, feel free to get in touch or explore my tutorials and templates at <a href="https://www.challengejp.com/">challengejp.com</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/0*U7Gl9WRnr2150HB8.jpeg" /></figure><p><em>Disclaimer: This tutorial is for general informational and educational purposes only and is not a substitute for professional advice.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=65e0a638fb12" width="1" height="1" alt=""><hr><p><a href="https://medium.com/challengejp/how-to-create-a-financial-model-with-debt-financing-in-8-steps-65e0a638fb12">How to Create a Financial Model with Debt Financing in 8 Steps</a> was originally published in <a href="https://medium.com/challengejp">ChallengeJP</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>