{"id":7687,"date":"2021-11-10T13:00:00","date_gmt":"2021-11-10T12:00:00","guid":{"rendered":"https:\/\/beebole.com\/blog\/?p=7687"},"modified":"2026-07-10T15:02:58","modified_gmt":"2026-07-10T13:02:58","slug":"budget-vs-actuals-template-microsoft-excel","status":"publish","type":"post","link":"https:\/\/beebole.com\/blog\/budget-vs-actuals-template-microsoft-excel","title":{"rendered":"Mastering budget vs. actuals analysis: Excel Power Query tutorial + FREE template"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">In this tutorial, learn how to create a budget vs. actuals report in Excel using Power Query. Gain insights and track financial performance effortlessly.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">As a financial controller, accountant, or CFO, you&#8217;re likely familiar with the concept of <strong>budget vs. actuals<\/strong>. You know that reporting budget vs. actuals can be both cumbersome and time-consuming, given actuals are administered on a detailed level and budget numbers are recorded on a higher level. With this tutorial, however, we hope to show you it doesn&#8217;t need to be so cumbersome.<\/p>\n\n\n<div  class=\"position-relative bbl-summarize-w-ai-block\">\n    <div class=\"bbl-swa-inner \">\n        <div class=\"bbl-swa-inner-top\">\n                            <div class=\"bbl-swa-title\">\n                    TL;DR: What you\u2019ll learn                <\/div>\n            \n                            <div class=\"bbl-swa-quote montserrat-font\">\n                    <p><span style=\"font-weight: 400;\">Learn how to build a budget vs. actuals report in Excel through Power Query, Power Pivot, and dynamic pivot tables. We&#8217;ll cover:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Structuring your source data: <\/b><span style=\"font-weight: 400;\">Set up Actual, Budget, Chart of Accounts, and Level tables so Power Query can read them correctly<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Transforming and merging data: <\/b><span style=\"font-weight: 400;\">Unpivot the budget table and merge actuals up to the same reporting level<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Building the data model: <\/b><span style=\"font-weight: 400;\">Load queries into Excel\u2019s Data Model and create relationships between tables<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Creating measures and a pivot table: <\/b><span style=\"font-weight: 400;\">Calculate Total Actual, Total Budget, and Variance, then filter by month with a timeline<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>A faster alternative: <\/b><span style=\"font-weight: 400;\">See how Beebole tracks budget vs. actuals automatically in real time with built-in budget status alerts<\/span><\/li>\n<\/ul>\n                <\/div>\n                    <\/div>\n\n        <div class=\"bbl-swa-actions d-flex justify-content-between\">\n                                                <div\n                        class=\"bbl-swa-actions-label montserrat-font bbl-wider-label\"\n                    >Explore further:<\/div>\n                            \n            <div class=\"bbl-swa-logos-outer d-flex\">\n                                <div class=\"bbl-swa-logos\">\n                                            <a\n            class=\"bbl-summarize-w-chatgpt\"\n            href=\"https:\/\/chat.openai.com\/?q=Please%20read%20the%20article%20at%20https:\/\/beebole.com\/blog\/budget-vs-actuals-template-microsoft-excel.%20Based%20on%20the%20information%20in%20this%20article,%20outline%20the%20advantages%20and%20drawbacks%20of%20tracking%20budget%20vs.%20actuals%20in%20Excel%20compared%20with%20a%20dedicated%20project%20time%20tracking%20tool.%20Stick%20to%20only%20what%20this%20article%20says%20\u2014%20no%20outside%20information%20or%20generic%20knowledge.%20Present%20the%20comparison%20clearly%20and%20concisely.\"\n            rel=\"nofollow noopener noreferrer\" target=\"_blank\"\n        >\n            <svg width=\"28\" height=\"26\" viewBox=\"0 0 28 26\" fill=\"none\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\">\n                <mask id=\"mask0_1578_5676\" style=\"mask-type:luminance\" maskUnits=\"userSpaceOnUse\" x=\"0\" y=\"0\" width=\"28\" height=\"26\">\n                    <path d=\"M27.1171 0H0.882935V26H27.1171V0Z\" fill=\"white\"\/>\n                <\/mask>\n                <g mask=\"url(#mask0_1578_5676)\">\n                    <path d=\"M10.9449 9.46396V6.99394C10.9449 6.78592 11.023 6.62986 11.2049 6.52598L16.171 3.66598C16.847 3.276 17.6531 3.09409 18.4849 3.09409C21.6049 3.09409 23.581 5.51214 23.581 8.08603C23.581 8.26799 23.581 8.47602 23.555 8.68404L18.4069 5.66798C18.095 5.48608 17.7828 5.48608 17.4709 5.66798L10.9449 9.46396ZM22.5409 19.084V13.1819C22.5409 12.8178 22.3848 12.5578 22.0729 12.3759L15.5469 8.57989L17.6789 7.35781C17.8609 7.25393 18.0169 7.25393 18.1989 7.35781L23.165 10.2178C24.5951 11.0499 25.557 12.8178 25.557 14.5337C25.557 16.5097 24.3871 18.3297 22.5409 19.0838V19.084ZM9.41092 13.8841L7.27892 12.6361C7.09702 12.5323 7.01893 12.3761 7.01893 12.1681V6.44817C7.01893 3.66625 9.15093 1.5601 12.037 1.5601C13.1291 1.5601 14.1429 1.92419 15.0011 2.57416L9.87915 5.53826C9.56725 5.72016 9.41119 5.98015 9.41119 6.34429V13.8843L9.41092 13.8841ZM14 16.536L10.9449 14.82V11.1802L14 9.46423L17.0548 11.1802V14.82L14 16.536ZM15.963 24.4401C14.8709 24.4401 13.8571 24.076 12.9989 23.4261L18.1208 20.462C18.4328 20.2801 18.5888 20.0201 18.5888 19.6559V12.1159L20.7469 13.3639C20.9288 13.4677 21.0069 13.6238 21.0069 13.8319V19.5518C21.0069 22.3337 18.8488 24.4399 15.963 24.4399V24.4401ZM9.8009 18.6421L4.83475 15.7822C3.40464 14.95 2.44277 13.1822 2.44277 11.4662C2.44277 9.46423 3.63879 7.67025 5.48467 6.91618V12.8442C5.48467 13.2082 5.64079 13.4682 5.95269 13.6502L12.4528 17.4201L10.3208 18.6421C10.1389 18.746 9.98281 18.746 9.8009 18.6421ZM9.51507 22.9061C6.57704 22.9061 4.41898 20.6961 4.41898 17.9661C4.41898 17.7581 4.44504 17.55 4.47089 17.342L9.59288 20.3061C9.90478 20.4881 10.217 20.4881 10.5289 20.3061L17.0548 16.5363V19.0063C17.0548 19.2143 16.9768 19.3704 16.7948 19.4742L11.8288 22.3342C11.1527 22.7242 10.3467 22.9061 9.51479 22.9061H9.51507ZM15.963 26C19.109 26 21.7349 23.7641 22.3331 20.8C25.2451 20.0459 27.1171 17.3159 27.1171 14.534C27.1171 12.7139 26.3372 10.946 24.9331 9.67198C25.0631 9.12594 25.1411 8.57989 25.1411 8.03412C25.1411 4.31617 22.1251 1.53399 18.641 1.53399C17.9392 1.53399 17.2631 1.63786 16.5871 1.87201C15.4169 0.727951 13.8049 0 12.037 0C8.89099 0 6.26513 2.23587 5.66691 5.19997C2.75494 5.95404 0.882935 8.68404 0.882935 11.466C0.882935 13.2861 1.66285 15.0539 3.0669 16.328C2.9369 16.874 2.85887 17.4201 2.85887 17.9659C2.85887 21.6838 5.87493 24.466 9.35895 24.466C10.0608 24.466 10.7369 24.3621 11.4129 24.1279C12.5828 25.272 14.1948 26 15.963 26Z\" fill=\"#313358\"\/>\n                <\/g>\n            <\/svg>\n        <\/a>\n                                                                    <a\n            class=\"bbl-summarize-w-perplexity\"\n            href=\"https:\/\/www.perplexity.ai\/search\/new?q=Please%20read%20the%20article%20at%20https:\/\/beebole.com\/blog\/budget-vs-actuals-template-microsoft-excel.%20Based%20on%20the%20information%20in%20this%20article,%20outline%20the%20advantages%20and%20drawbacks%20of%20tracking%20budget%20vs.%20actuals%20in%20Excel%20compared%20with%20a%20dedicated%20project%20time%20tracking%20tool.%20Stick%20to%20only%20what%20this%20article%20says%20\u2014%20no%20outside%20information%20or%20generic%20knowledge.%20Present%20the%20comparison%20clearly%20and%20concisely.\"\n            rel=\"nofollow noopener noreferrer\" target=\"_blank\"\n        >\n            <svg width=\"24\" height=\"26\" viewBox=\"0 0 24 26\" fill=\"none\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\">\n                <path d=\"M3.93943 0.132141L11.2677 6.8839V0.147583H12.6947V6.91478L20.0551 0.132141V7.83097H23.0778V18.9346H20.0641V25.7893L12.6947 19.3142V25.864H11.2664V19.421L3.94844 25.8678V18.9346H0.925781V7.82968H3.93943V0.132141ZM10.1932 9.24H2.35025V17.5256H3.94586V14.9121L10.1932 9.24ZM5.37419 15.5375V22.7242L11.2677 17.5333V10.1819L5.37419 15.5375ZM12.7346 17.4638L18.6384 22.6496V18.9346H18.6306V15.5298L12.7346 10.1768V17.4638ZM20.0641 17.5256H21.652V9.23871H13.8708L20.0654 14.8529L20.0641 17.5256ZM18.6294 7.83097V3.37355L13.791 7.83097H18.6294ZM10.2035 7.83097L5.36519 3.37355V7.83097H10.2035Z\" fill=\"#262C54\"\/>\n            <\/svg>\n        <\/a>\n                                                                    <a\n            class=\"bbl-summarize-w-gemini\"\n            href=\"https:\/\/www.google.com\/search?aep=11&#038;udm=50&#038;q=Please%20read%20the%20article%20at%20https:\/\/beebole.com\/blog\/budget-vs-actuals-template-microsoft-excel.%20Based%20on%20the%20information%20in%20this%20article,%20outline%20the%20advantages%20and%20drawbacks%20of%20tracking%20budget%20vs.%20actuals%20in%20Excel%20compared%20with%20a%20dedicated%20project%20time%20tracking%20tool.%20Stick%20to%20only%20what%20this%20article%20says%20\u2014%20no%20outside%20information%20or%20generic%20knowledge.%20Present%20the%20comparison%20clearly%20and%20concisely.\"\n            rel=\"nofollow noopener noreferrer\" target=\"_blank\"\n        >\n            <svg width=\"29\" height=\"29\" viewBox=\"0 0 29 29\" fill=\"none\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\">\n                <path d=\"M28.8828 14.4703C21.1362 14.9372 14.9348 21.1362 14.4691 28.8828H14.4125C13.9456 21.1362 7.74541 14.9372 0 14.4703V14.4137C7.74661 13.9456 13.9456 7.74661 14.4125 0H14.4691C14.936 7.74661 21.1362 13.9456 28.8828 14.4137V14.4703Z\" fill=\"#262C54\"\/>\n            <\/svg>\n        <\/a>\n                                                                    <a\n            class=\"bbl-summarize-w-grok\"\n            href=\"https:\/\/x.com\/i\/grok?q=Please%20read%20the%20article%20at%20https:\/\/beebole.com\/blog\/budget-vs-actuals-template-microsoft-excel.%20Based%20on%20the%20information%20in%20this%20article,%20outline%20the%20advantages%20and%20drawbacks%20of%20tracking%20budget%20vs.%20actuals%20in%20Excel%20compared%20with%20a%20dedicated%20project%20time%20tracking%20tool.%20Stick%20to%20only%20what%20this%20article%20says%20\u2014%20no%20outside%20information%20or%20generic%20knowledge.%20Present%20the%20comparison%20clearly%20and%20concisely.\"\n            rel=\"nofollow noopener noreferrer\" target=\"_blank\"\n        >\n            <svg width=\"32\" height=\"32\" viewBox=\"0 0 32 32\" fill=\"none\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\">\n                <path d=\"M12.3998 19.784L21.6343 12.9589C22.0871 12.6243 22.7342 12.7548 22.9499 13.2745C24.0853 16.0155 23.578 19.3094 21.3191 21.571C19.0603 23.8325 15.9173 24.3285 13.0445 23.1989L9.90625 24.6536C14.4074 27.7339 19.8733 26.9721 23.2888 23.5501C25.9981 20.8376 26.8372 17.1403 26.0526 13.8061L26.0597 13.8132C24.9219 8.91511 26.3394 6.9573 29.243 2.95385C29.3117 2.85892 29.3804 2.764 29.4492 2.6667L25.6283 6.49216V6.4803L12.3974 19.7864\" fill=\"#262C54\"\/>\n                <path d=\"M10.4902 21.4428C7.25946 18.3529 7.81648 13.5711 10.5731 10.8136C12.6115 8.77269 15.9513 7.93973 18.8667 9.16426L21.9979 7.71666C21.4338 7.30848 20.7108 6.86946 19.8812 6.56095C16.1314 5.01605 11.6421 5.78494 8.59393 8.83439C5.6619 11.7699 4.73985 16.2836 6.3232 20.1352C7.50597 23.0138 5.56708 25.0498 3.61397 27.105C2.92184 27.8335 2.22735 28.5621 1.66797 29.3333L10.4878 21.4451\" fill=\"#262C54\"\/>\n            <\/svg>\n        <\/a>\n                                                                    <a\n            class=\"bbl-summarize-w-claude\"\n            href=\"https:\/\/claude.ai\/new?q=Please%20read%20the%20article%20at%20https:\/\/beebole.com\/blog\/budget-vs-actuals-template-microsoft-excel.%20Based%20on%20the%20information%20in%20this%20article,%20outline%20the%20advantages%20and%20drawbacks%20of%20tracking%20budget%20vs.%20actuals%20in%20Excel%20compared%20with%20a%20dedicated%20project%20time%20tracking%20tool.%20Stick%20to%20only%20what%20this%20article%20says%20\u2014%20no%20outside%20information%20or%20generic%20knowledge.%20Present%20the%20comparison%20clearly%20and%20concisely.\"\n            rel=\"nofollow noopener noreferrer\" target=\"_blank\"\n        >\n            <svg width=\"32\" height=\"32\" viewBox=\"0 0 32 32\" fill=\"none\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\">\n                <path d=\"M7.66688 21.2213L13.3509 18.0321L13.4465 17.7551L13.3509 17.601H13.074L12.124 17.5425L8.87626 17.4547L6.05958 17.3376L3.33068 17.1913L2.64407 17.045L2.00037 16.1965L2.06669 15.7733L2.64407 15.3851L3.47112 15.4573L5.29884 15.5821L8.0414 15.7713L10.031 15.8883L12.9784 16.1946H13.4465L13.5128 16.0054L13.3529 15.8883L13.2281 15.7713L10.3899 13.848L7.31772 11.8155L5.70847 10.6451L4.8385 10.0521L4.39961 9.4962L4.2104 8.28292L5.0004 7.41295L6.06153 7.48512L6.33266 7.5573L7.40745 8.38435L9.70331 10.1614L12.7014 12.3694L13.1403 12.7342L13.3158 12.6094L13.3373 12.5216L13.1403 12.1919L11.5096 9.24457L9.76963 6.24649L8.99524 5.00395L8.79043 4.25882C8.71826 3.95257 8.66559 3.69509 8.66559 3.38105L9.56482 2.15997L10.0622 2.00002L11.2618 2.15997L11.7671 2.59885L12.5122 4.30368L13.7196 6.98772L15.5922 10.6373L16.1403 11.7199L16.4329 12.7225L16.5421 13.0287H16.7313V12.8532L16.8854 10.7973L17.1702 8.27317L17.4472 5.02541L17.5428 4.11057L17.9953 3.01433L18.8946 2.42135L19.5968 2.75685L20.1742 3.58391L20.0942 4.11837L19.7509 6.34987L19.0779 9.84536L18.639 12.1861H18.8946L19.1872 11.8935L20.3712 10.3213L22.3608 7.83428L23.2386 6.84728L24.2626 5.75688L24.92 5.23802H26.1625L27.0774 6.5976L26.6677 8.00203L25.3881 9.62494L24.327 11.0001L22.8055 13.0483L21.8556 14.6868L21.9434 14.8175L22.1696 14.796L25.6066 14.0645L27.4636 13.729L29.6795 13.3486L30.6821 13.8168L30.7913 14.2927L30.3973 15.2661L28.0273 15.8513L25.2477 16.4072L21.1085 17.3864L21.0578 17.4235L21.1163 17.4956L22.9811 17.6712L23.7789 17.7141H25.7315L29.3674 17.9852L30.3173 18.6133L30.8869 19.3819L30.7913 19.9671L29.3284 20.7122L27.3544 20.244L22.747 19.1478L21.167 18.7538H20.9486V18.8845L22.2652 20.1719L24.6781 22.3507L27.6996 25.1596L27.8537 25.854L27.4655 26.4021L27.0559 26.3436L24.4011 24.3462L23.3771 23.4469L21.0578 21.4944H20.9037V21.6992L21.4382 22.4814L24.2607 26.724L24.407 28.025L24.2022 28.4483L23.4707 28.7038L22.667 28.5575L21.0149 26.2383L19.3101 23.6264L17.9349 21.2857L17.7671 21.3813L16.9557 30.1219L16.5753 30.5686L15.6975 30.9041L14.9661 30.3482L14.5779 29.449L14.9661 27.672L15.4342 25.3527L15.8146 23.5094L16.1579 21.2193L16.3627 20.4586L16.349 20.4079L16.1813 20.4294L14.455 22.7993L11.8295 26.3475L9.75208 28.5712L9.25467 28.7682L8.39251 28.3215L8.47248 27.5237L8.95428 26.8137L11.8295 23.1563L13.5636 20.8897L14.6832 19.5808L14.6754 19.3916H14.6091L6.97246 24.3501L5.61289 24.5256L5.02771 23.9775L5.09988 23.0783L5.37687 22.7857L7.67273 21.2057L7.66493 21.2135L7.66688 21.2213Z\" fill=\"#262C54\"\/>\n            <\/svg>\n        <\/a>\n                                                            <\/div>\n            <\/div>\n        <\/div>\n    <\/div>\n<\/div>\n\n\n<h2 id=\"why-budget-vs-actuals\" class=\"wp-block-heading\">Why calculate budget vs. actuals<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Managers are responsible for actual amounts spent versus the corresponding budgeted amounts per category.<strong> The actual amounts<\/strong> may be derived from the accounting system, while <strong>the budget amounts<\/strong> are retrieved from the official budget as determined and agreed upon at the end of last year for the current year. A budget is a report by cost\/revenue category showing estimated numbers by month for the next year as agreed upon by management. The financial manager will report on these actual versus budget numbers, usually on a monthly basis and with a lot of manual effort. But using the built-in Power Query feature, the reporting procedure can be a breeze.<\/p>\n\n\n\n<h2 id=\"diff-budget-actuals\" class=\"wp-block-heading\">Budget vs. actual expenditure<\/h2>\n\n\n\n<div  class=\"montserrat-font my-5 mx-auto bbl_definition_snippet\">\n  <div class=\"mb-4\">\n    <div class=\"bbl-ds-item question mb-3\">\n      <h2 class=\"h4 mb-0 mt-0\">What&#8217;s the difference between budget and actual expenditure?<\/h2>\n    <\/div>\n    <div class=\"bbl-ds-item answer\">\n      <p>The key distinction between <strong>budget and actual expenditure<\/strong> is that the budget is a planned estimate of future financial activities, while actual expenditure represents the realized costs incurred in practice. <strong>The comparison between budgeted amounts and actual expenditures<\/strong>\u00a0is crucial for evaluating financial performance, identifying variances, and making informed decisions to manage resources effectively. By analyzing these differences, organizations or individuals can assess their financial performance, adjust their future budgets, and take necessary corrective actions.<\/p>\n    <\/div>\n  <\/div>\n<\/div>\n\n\n<h2 id=\"diff-budget-forecast-actuals\" class=\"wp-block-heading\">What&#8217;s the difference between budget vs. forecast vs. actuals?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">In summary, the key differences are:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Budget<\/strong>: Planned estimates of future income and expenses.<\/li>\n\n\n\n<li><strong>Forecast<\/strong>: Updated projections based on the latest information and adjustments to the budget.<\/li>\n\n\n\n<li><strong>Actuals<\/strong>: The factual data representing the actual financial results that have occurred.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">By comparing budgeted amounts, forecasts, and actuals, individuals and organizations can assess their financial performance, identify variances, and make informed decisions to manage resources effectively.<\/p>\n\n\n\n<h2 id=\"budget-actuals-same-thing\" class=\"wp-block-heading\">Is <em>budget vs. actuals analysis<\/em> and <em>budget-to-actual variance analysis<\/em> the same thing?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Yes, <strong>budget vs. actuals analysis<\/strong> and <strong>budget-to-actual variance analysis<\/strong> are essentially the same thing. Both terms refer to the process of comparing budgeted amounts to actual results and analyzing the differences or variances between them.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><em>Budget vs. actuals analysis<\/em> involves assessing financial performance by comparing the planned budgeted figures with the actual results that have been realized. It helps in evaluating how well the organization or individual has met their financial targets and objectives.<\/p>\n\n\n\n<figure data-wp-context=\"{&quot;imageId&quot;:&quot;6a64142c5c112&quot;}\" data-wp-interactive=\"core\/image\" data-wp-key=\"6a64142c5c112\" class=\"wp-block-image aligncenter size-large wp-lightbox-container\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"438\" data-wp-class--hide=\"state.isContentHidden\" data-wp-class--show=\"state.isContentVisible\" data-wp-init=\"callbacks.setButtonStyles\" data-wp-on--click=\"actions.showLightbox\" data-wp-on--load=\"callbacks.setButtonStyles\" data-wp-on--pointerdown=\"actions.preloadImage\" data-wp-on--pointerenter=\"actions.preloadImageWithDelay\" data-wp-on--pointerleave=\"actions.cancelPreload\" data-wp-on-window--resize=\"callbacks.setButtonStyles\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2026\/07\/project-budget-status-tracking-in-Beebole-700x438.png\" alt=\"Beebole&#039;s project budget status tracking report offers instant insight into project performance and budget overruns\" class=\"wp-image-14788\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2026\/07\/project-budget-status-tracking-in-Beebole-700x438.png 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2026\/07\/project-budget-status-tracking-in-Beebole-768x480.png 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2026\/07\/project-budget-status-tracking-in-Beebole.png 1147w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><button\n\t\t\tclass=\"lightbox-trigger\"\n\t\t\ttype=\"button\"\n\t\t\taria-haspopup=\"dialog\"\n\t\t\tdata-wp-bind--aria-label=\"state.thisImage.triggerButtonAriaLabel\"\n\t\t\tdata-wp-init=\"callbacks.initTriggerButton\"\n\t\t\tdata-wp-on--click=\"actions.showLightbox\"\n\t\t\tdata-wp-style--right=\"state.thisImage.buttonRight\"\n\t\t\tdata-wp-style--top=\"state.thisImage.buttonTop\"\n\t\t>\n\t\t\t<svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"12\" height=\"12\" fill=\"none\" viewBox=\"0 0 12 12\">\n\t\t\t\t<path fill=\"#fff\" d=\"M2 0a2 2 0 0 0-2 2v2h1.5V2a.5.5 0 0 1 .5-.5h2V0H2Zm2 10.5H2a.5.5 0 0 1-.5-.5V8H0v2a2 2 0 0 0 2 2h2v-1.5ZM8 12v-1.5h2a.5.5 0 0 0 .5-.5V8H12v2a2 2 0 0 1-2 2H8Zm2-12a2 2 0 0 1 2 2v2h-1.5V2a.5.5 0 0 0-.5-.5H8V0h2Z\" \/>\n\t\t\t<\/svg>\n\t\t<\/button><figcaption class=\"wp-element-caption\">Beebole&#8217;s built-in <a href=\"https:\/\/beebole.com\/help\/documentation\/reports#budget-status\">budget status report<\/a> shows you project performance in real time, notifying you of potential budget runs before they happen.<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">On the other hand, <em>budget-to-actual variance analysis<\/em> specifically focuses on identifying and analyzing the differences or variances between the budgeted amounts and the actual results. It involves calculating the variances for various income and expense categories, understanding the reasons behind the variances, and assessing their impact on the overall financial performance.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In both cases, <strong>the goal is to understand the variations between the planned budget and the actual outcomes<\/strong>, which can provide insights into areas of strength, areas needing improvement, and potential issues or opportunities for financial management and decision-making.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Therefore, while the terms may have slightly different wording, they generally refer to the same practice of comparing budgeted amounts to actual results and analyzing the variances to gain insights into financial performance. This analysis could also be part of a typical <a href=\"https:\/\/beebole.com\/blog\/finance-controller-kpis\/\" data-type=\"URL\" data-id=\"https:\/\/beebole.com\/blog\/finance-controller-kpis\/\">finance controller KPIs<\/a> dashboard.<\/p>\n\n\n\n<div\n    class=\"montserrat-font my-5 mx-auto p-4 p-lg-5 bbl_customer_story_blurb\"\n  style=\"background-image:url('https:\/\/beebole.com\/blog\/wp-content\/themes\/sage\/public\/images\/block-csb-bk.18ecf3.png')\"\n>\n  <div class=\"position-relative\">\n    \n    <div class=\"bbl-csb-text\">\n      <p data-start=\"238\" data-end=\"438\"><a href=\"https:\/\/beebole.com\/blog\/how-to-avoid-project-cost-overruns\/\"><strong>Rancho BioSciences<\/strong><\/a> runs 100+ projects at once, and spreadsheets alone weren\u2019t enough to keep budgets and actuals under control. By feeding real-time data from Beebole into their reporting, they can:<\/p>\n<p data-start=\"442\" data-end=\"489\">\ud83d\ude80Spot overruns before they become red flags<br \/>\n\ud83d\ude80Compare budgets vs. actuals with confidence in real time<br \/>\n\ud83d\ude80Build forecasts powered by accurate project data<br \/>\n\ud83d\ude80Deliver leadership the clarity they need to act fast<\/p>\n<p data-start=\"658\" data-end=\"777\">Beebole takes the heavy lifting out of spreadsheets \u2014 so your analysis is faster, cleaner, and always accurate. Real-time comparison runs right in <a href=\"http:\/\/beebole.com\/project-financials\">Beebole&#8217;s budget status report<\/a>: a budget turns yellow at 80% of spend and red once it\u2019s exceeded, so teams see variance<em> the moment it happens<\/em> instead of waiting for a report.<\/p>\n    <\/div>\n\n          <a\n        class=\"d-inline-block bbl-csb-link mt-2\"\n        href=\"https:\/\/beebole.com\/blog\/how-to-avoid-project-cost-overruns\/\"\n              >\n        Read the case study\n        <svg width=\"14\" height=\"14\" viewBox=\"0 0 14 14\" fill=\"none\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\">\n          <path d=\"M5.25 10.5L8.75 7L5.25 3.5\" stroke=\"#424240\" stroke-width=\"1.16667\" stroke-linecap=\"round\" stroke-linejoin=\"round\"\/>\n        <\/svg>\n      <\/a>\n      <\/div>\n<\/div>\n\n\n<h2 id=\"learn\" class=\"wp-block-heading\">What you&#8217;ll learn in this tutorial: Budget vs. actuals with Excel and Power Query<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Creating a budget vs. actuals report can involve various labor-intensive steps when using Microsoft Excel the old-fashioned way. Luckily, however, modern Excel contains BI tools such as Excel Power Query and Power Pivot, which make creating a budget vs. actuals template much more attainable. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>In this article, you will learn how to set up a dynamic model on budget vs. actuals in Excel using these tools. <\/strong>Apart from this being a painless process, rest assured you&#8217;ll end up with an accurate budget vs. actuals report that&#8217;s generated automatically. Let&#8217;s get started!<\/p>\n\n\n\n<h2 id=\"what-you-need\" class=\"wp-block-heading\">What you need<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Having access to Microsoft Excel is a must. Then, be sure to download the file below to follow along with the tutorial.<\/p>\n\n\n<div  class=\"my-5 mx-auto bbl_cta_assets_block bbl_cta_block bk-light\">\n\t<div class=\"bbl_cta_block-blockcontent d-block overflow-hidden position-relative rounded-4 text-decoration-none\">\n\t\t\t\t\t<div class=\"object-fit-cover position-absolute bbl-orange-dot-round\" style=\"background-image: url(https:\/\/beebole.com\/blog\/wp-content\/themes\/sage\/public\/images\/orange-dot-round.609972.svg)\"><\/div>\n    \t\t<div class=\"bbl_cta_block-row align-items-center d-flex flex-md-row justify-content-center mx-0 no-gutters position-relative row\">\n\t\t\t<div class=\"bbl_cta_block-img-col col d-flex justify-content-start pe-md-2 px-0\">\n\t\t\t\t<img\n\t\t\t\t\talt=\"Download this free Excel spreadsheet to follow along with the tutorial.\"\n\t\t\t\t\tclass=\"d-block h-auto me-md-4 mw-lg-100\"\n\t\t\t\t\theight=\"251\"\n\t\t\t\t\tloading=\"lazy\"\n\t\t\t\t\tsrc=\"https:\/\/beebole.com\/blog\/wp-content\/themes\/sage\/public\/images\/promotion-download-asset.198bac.png\"\n\t\t\t\t\twidth=\"360\"\n\t\t\t\t\/>\n\t\t\t<\/div>\n\t\t\t<div class=\"bbl_cta_block-text-col col mt-md-0 ps-md-2 px-0\">\n\t\t\t\t\t\t\t\t\t<div class=\"mb-1\"><div class=\"bbl_cta_block-label font-weight-bold lh-base mb-4\">BUDGET VS ACTUALS IN EXCEL<\/div><\/div>\n\t\t\t\t                  <div class=\"bbl_cta_block-title font-weight-bold lh-base\">Download this free Excel spreadsheet to follow along with the tutorial.<\/div>\n        \t\t\t\t\t\t\t\t\t<div class=\"mt-1 pe-lg-0 pe-md-4\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"bbl_cta_block-cta-button w-100 w-lg-auto btn btn-outline-primary text-primary link-light  me-lg-4 mb-3 mb-lg-0 mt-4 free-download-link\" href=\"https:\/\/docs.google.com\/uc?id=1szO472gaEZO29zNGL3kAPlEsevhylrK9&#038;export=download\" target=\"_blank\" rel=\"noopener\">\n\t\t\t\t\t\t\tGet my copy!            <\/a>\n          <\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/div>\n  <\/div>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\">Below, you&#8217;ll see screenshots for each of the tabs in the Excel file we&#8217;ll be working with. Those tabs are: Actual, Budget, Chart of Accounts, and Level. <\/p>\n\n\n\n<h3 id=\"h-actual\" class=\"wp-block-heading\">Actual:<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"439\" height=\"472\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/actuals-source-spreadsheet.png\" alt=\"Source sheet for actuals\" class=\"wp-image-7690\" title=\"\"><figcaption class=\"wp-element-caption\">A look at a spreadsheet tab with Actuals.<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">This sheet is formatted as a table and is downloaded from an ERP system.<\/p>\n\n\n\n<h3 id=\"h-budget\" class=\"wp-block-heading\">Budget:<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"223\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/source-budget-spreadsheet-700x223.png\" alt=\"Microsoft excel budget spreadsheet\" class=\"wp-image-7691\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/source-budget-spreadsheet-700x223.png 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/source-budget-spreadsheet-768x245.png 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/source-budget-spreadsheet.png 866w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\">Screenshot of the budget tab.<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The budget numbers are recorded in a cross-table style, so it is easy to enter data as months go by. The user records the budget numbers at a higher level.<\/p>\n\n\n\n<h3 id=\"h-coa-chart-of-accounts\" class=\"wp-block-heading\">COA (Chart of Accounts):<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"396\" height=\"435\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/Chart-of-accounts-COA-Microsoft-excel.png\" alt=\"COA chart of accounts in Microsoft Excel example source data\" class=\"wp-image-7692\" title=\"\"><figcaption class=\"wp-element-caption\">A look at the Chart of Accounts (COA), which can be used at an Actual level.<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The Chart of Accounts shows all the accounts that can be used at an Actual level and the accompanying level for use in the Budget.<\/p>\n\n\n\n<h3 id=\"h-level\" class=\"wp-block-heading\">Level:<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"337\" height=\"457\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/level-and-order-microsoft-excel-budget-vs-actuals.png\" alt=\"Table displaying hte order, type and sign to use when looking at budget vs. actuals in Microsoft Excel\" class=\"wp-image-7693\" title=\"\"><figcaption class=\"wp-element-caption\">This Level table shows the levels, orders, type, and sign.<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">This table displays the levels and accompanying Order, Type, and Sign. The Order can be used to sort the final report in the desired order and sequence. The Type shows the type of account, i.e., BS (=Balance Sheet) and PL (Profit &amp; Loss). Only Profit &amp; Loss accounts will be presented in the final report. Sign can be used to transform detailed Actual amounts into the correct format. Debits and Credits will be shown as positive numbers.<\/p>\n\n\n\n<h2 id=\"step-by-step\" class=\"wp-block-heading\">Step-by-step tutorial: How to create a budget vs. actuals report in Excel with Power Query<\/h2>\n\n\n\n<h3 id=\"converting-dataset\" class=\"wp-block-heading\">Converting the dataset<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">In order to create a dynamic report, we need to go through the following 7 steps:<\/p>\n\n\n\n<ol style=\"list-style-type:1\" class=\"wp-block-list\">\n<li>Importing the data in Power Query<\/li>\n\n\n\n<li>Transforming the data in Power Query<\/li>\n\n\n\n<li>Loading the transformed data to the Data Model<\/li>\n\n\n\n<li>Creating a calendar table<\/li>\n\n\n\n<li>Creating relationships between the tables<\/li>\n\n\n\n<li>Setting up measures<\/li>\n\n\n\n<li>Creating a dynamic pivot table<\/li>\n<\/ol>\n\n\n\n<h3 id=\"h-importing-the-dataset\" class=\"wp-block-heading\">Importing the dataset<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Place your cursor in the Actual table and select from the Ribbon: Data \u2192 Get &amp; Transform Data \u2192 From Table\/Range. A copy of your data will be placed in Power Query. &nbsp;To import the next table, you need to exit Power Query by selecting in the Ribbon: Home \u2192 Close \u2192 Close &amp; Load \u2192 Close &amp; Load to \u2026 \u2192 Only Create Connection \u2192 OK.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"307\" height=\"270\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/import-data-budget-vs-actuals.png\" alt=\"When analyzing budget vs. actuals, importing dataset is first step\" class=\"wp-image-7694\" title=\"\"><figcaption class=\"wp-element-caption\">Import data from the Actual table.<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">On the right-hand side, you will see a Queries &amp; Connections Panel with the table just loaded.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"346\" height=\"190\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/budget-vs-actuals-microsoft-excel.png\" alt=\"Queries &amp; Connections panel with table loaded\" class=\"wp-image-7695\" title=\"\"><figcaption class=\"wp-element-caption\">The Queries &amp; Connections Panel appears on the right-hand side.<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Now, move to the next tables and load them in the same fashion as the first table.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When you are done, you will see four queries appearing in the Queries &amp; Connections Panel.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"335\" height=\"311\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/Query-options-for-budget-analysis-in-Microsoft-Excel.png\" alt=\"\" class=\"wp-image-7696\" title=\"\"><figcaption class=\"wp-element-caption\">Notice four queries will appear in the Queries &amp; Connections Panel.<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Transforming the budget<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To transform the data, you need to return to the Power Query environment. You may do so by double-clicking on one of the queries in the panel.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In Power Query, you can see all the queries by clicking on the &gt;icon located on the left-hand side of the screen.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"171\" height=\"89\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/budget-analysis-Microsoft-Excel.png\" alt=\"See how the queries in Power Query when doing budget analysis\" class=\"wp-image-7697\" title=\"\"><figcaption class=\"wp-element-caption\">To see all of the queries, click on the &gt; icon.<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The Navigator Pane will open, and you&#8217;ll see the four queries.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"335\" height=\"311\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/Query-options-for-budget-analysis-in-Microsoft-Excel-1.png\" alt=\"\" class=\"wp-image-7698\" title=\"\"><figcaption class=\"wp-element-caption\">Four queries in the Navigator Pane.<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">We want to create a long table for DataBudget. Proceed as follows:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Click on DataBudget<\/li>\n\n\n\n<li>Select the first column (Level)<\/li>\n\n\n\n<li>Right-click with the mouse<\/li>\n\n\n\n<li>Choose Unpivot Other Columns<\/li>\n\n\n\n<li>Rename header Attribute into Date and change format into Date<\/li>\n\n\n\n<li>Rename header Value into Amount and change format into Decimal Number<\/li>\n\n\n\n<li>Select from the Ribbon: Home \u2192 Close &amp; Load \u2192 Close &amp; Load, and you will return to the active sheet in Excel.<\/li>\n<\/ul>\n\n\n\n<h3 id=\"h-transforming-the-actuals\" class=\"wp-block-heading\">Transforming the actuals<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The Actuals are recorded at a detailed level, while the Budget is maintained at a higher level. To be able to connect to the Budget, you must convert Actuals to this higher level. To do so, select from the Ribbon: Home \u2192 Combine \u2192 Merge queries and select DataCOA as second table.&nbsp; Click on columns Acc No in both tables. You will notice that the selection matches, and a green check mark is displayed. Click on the OK button.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"679\" height=\"605\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/Merge-queries-budget-analysis.png\" alt=\"Actuals must be converted to the higher level of budget\" class=\"wp-image-7699\" title=\"\"><figcaption class=\"wp-element-caption\">To be able to connect to the Budget, you must convert Actuals to a higher level by merging queries.<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">A new column will appear on the right-hand side. Click on the double arrow. Deselect all options in the dialog box, select Level, and click on the OK button.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"339\" height=\"254\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/how-to-compare-budget-vs-actual.png\" alt=\"Another step to transform the actuals in microsoft excel\" class=\"wp-image-7700\" title=\"\"><figcaption class=\"wp-element-caption\">Click on the double arrow, deselect all options in the dialog box, select Level, and click OK.<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Since revenues are booked as credit amounts, we want to revert them to positive numbers for reporting purposes. Furthermore, we only want to see Profit &amp; Loss accounts. We need to merge the Actuals with Level and select from the Ribbon: Home \u2192 Combine \u2192 Merge queries and select DataLevel as second table.&nbsp; Click on columns Level in both tables. Notice that the selection matches and a green check mark appears. Click on the OK button.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"660\" height=\"592\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/budget-analysis-microsoft-excel-tutorial.png\" alt=\"Continue merging data to analyze budget vs. actuals\" class=\"wp-image-7701\" title=\"\"><figcaption class=\"wp-element-caption\">It&#8217;s necessary to merge the Actuals with Level and select from the Ribbon, as shown above.<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">A new column will appear on the right-hand side. Click on the double arrow. Deselect all options in the dialog box, select Type and Sign, and click on the OK button.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Filter column Type on PL accounts by deselecting BS accounts.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Add a new column by selecting: Add column \u2192 Custom column. Enter the following details and click on OK.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"439\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/Dialog-box-for-budget-vs-actuals-analysis-microsoft-excel.png\" alt=\"Transforming data to do budget analysis in microsoft excel\" class=\"wp-image-7702\" title=\"\"><figcaption class=\"wp-element-caption\">Add a new column.<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Complete the transformation by performing the following steps:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Change the format of the Amt column to decimal number (1.2).<\/li>\n\n\n\n<li>Change the format of the Date column to Date.<\/li>\n\n\n\n<li>Select the columns that you want to keep by holding down the control key: Date, Level, and Amt.<\/li>\n\n\n\n<li>Right-click with your mouse, and from the short menu, select: Remove other columns.<\/li>\n\n\n\n<li>Move column Level to the left so it becomes the first column.<\/li>\n\n\n\n<li>Change header Amt into Amount.<\/li>\n\n\n\n<li>Exit Power Query by selecting: Home \u2192 Close &amp; Load \u2192 Close &amp; Load.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Loading data to the data model<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">In the Queries &amp; Connections Panel, you need to right-click on all the queries, and from the short menu, select: Load To\u2026 and then a dialogue box appears where you select: Add this data to the Data Model \u2192 OK.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"307\" height=\"270\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/loading-data-for-budget-vs-actuals-analysis.png\" alt=\"Adding more data to data model in Microsoft Excel\" class=\"wp-image-7703\" title=\"\"><figcaption class=\"wp-element-caption\">Now, it&#8217;s time to load all of the records for each query into the Data Model.<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">When you are done, you will see in the Queries &amp; Connections Panel that all the records for each query have been loaded into the Data Model.<\/p>\n\n\n\n<h3 id=\"h-creating-relationships-between-the-tables\" class=\"wp-block-heading\">Creating relationships between the tables<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To create relations between the tables, proceed as follows:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>From the ribbon, you select: Power Pivot \u2192 Data Model \u2192 Manage.<\/li>\n\n\n\n<li>Select: Design \u2192 Calendars \u2192 Date Table \u2192 New<\/li>\n\n\n\n<li>Select: Design \u2192 Calendars \u2192 Mark as Date Table<\/li>\n\n\n\n<li>Select: Home \u2192 View \u2192 Diagram View<\/li>\n\n\n\n<li>Create the following relationships by dragging:\n<ul class=\"wp-block-list\">\n<li>DataLevel &#8211; Level to DataCoa \u2013 Level<\/li>\n\n\n\n<li>DataLevel \u2013 Level to DataActual \u2013 Level<\/li>\n\n\n\n<li>DataLevel \u2013 Level to DataBudget \u2013 Level<\/li>\n\n\n\n<li>Calendar \u2013 Date to DataActual \u2013 Date<\/li>\n\n\n\n<li>Calendar \u2013 Date to DataBudget \u2013 Date<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"693\" height=\"742\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/how-to-show-budget-vs-actual-in-excel.png\" alt=\"Budget vs actuals analysis by creating relationships between tables\" class=\"wp-image-7704\" title=\"\"><figcaption class=\"wp-element-caption\">Create relationships between the tables.<\/figcaption><\/figure>\n\n\n\n<h3 id=\"h-creating-measures\" class=\"wp-block-heading\">Creating measures<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">You need to create the following measures by selecting: Power Pivot \u2192 Calculations \u2192 Measures \u2192 New Measure:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Measure name: Type TotalActual<\/li>\n\n\n\n<li>Formula: =SUM(DataActual[Amt])<\/li>\n\n\n\n<li>Click on Check formula<\/li>\n\n\n\n<li>Category: Number<\/li>\n\n\n\n<li>Format: Decimal number<\/li>\n\n\n\n<li>Decimal places: 0<\/li>\n\n\n\n<li>Use 1000 separator: yes<\/li>\n\n\n\n<li>Click OK<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"600\" height=\"534\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/how-to-compare-budget-vs-actual-1.png\" alt=\"creating measures in Excel to look at budget vs actuals\" class=\"wp-image-7705\" title=\"\"><figcaption class=\"wp-element-caption\">Create new measures by clicking Power Pivot \u2192 Calculations \u2192 Measures \u2192 New Measure.<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">In the same fashion, you will create the following measures:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>TotalBudget = SUM(DataBudget[Amount])<\/li>\n\n\n\n<li>Variance=[TotalActual]-[TotalBudget]<\/li>\n<\/ul>\n\n\n\n<h3 id=\"h-create-a-pivot-table\" class=\"wp-block-heading\">Create a pivot table<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Now we are ready to create a dynamic Pivot Table report. Select: Power Pivot \u2192 Data Model \u2192 Manage \u2192 Home \u2192 Pivot Table \u2192 Pivot Table \u2192 New worksheet \u2192 OK.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"481\" height=\"344\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/Budget-vs-actuals-in-Microsoft-Excel.png\" alt=\"Creating a dynamic Pivot Table report to look at budget vs. actuals in microsoft excel\" class=\"wp-image-7706\" title=\"\"><figcaption class=\"wp-element-caption\">It&#8217;s time to create a dynamic Pivot Table report.<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Use the following settings:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"329\" height=\"575\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/Settings-for-pivot-table-for-budget-vs-actuals-analysis.png\" alt=\"Settings for pivot table for budget vs actuals analysis in Microsoft Excel\" class=\"wp-image-7707\" title=\"\"><figcaption class=\"wp-element-caption\">Settings for the Pivot Table report.<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">As you can see, the calculated measures are used as values, while DataLevel \u2013 Level is used to populate the rows.<\/p>\n\n\n\n<h3 id=\"h-adding-a-timeline\" class=\"wp-block-heading\">Adding a timeline<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">In order to view the data by month, you can add a timeline. With your cursor placed in the pivot table, choose Pivot Table Analyze \u2192 Filter \u2192 Insert Timeline \u2192 Date \u2192 OK.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"236\" height=\"293\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/budget-analysis-microsoft-excel-examples.png\" alt=\"How to add a timeline to budget analysis microsoft excel\" class=\"wp-image-7708\" title=\"\"><figcaption class=\"wp-element-caption\">Add a timeline so that you can view the data by month.<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The dynamic Pivot Table, including the timeline, looks as follows:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"284\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/Dynamic-pivot-table-for-budget-analysis-in-microsoft-excel-700x284.png\" alt=\"Budget vs. actuals analysis with dynamic pivot table in Microsoft Excel\" class=\"wp-image-7709\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/Dynamic-pivot-table-for-budget-analysis-in-microsoft-excel-700x284.png 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/Dynamic-pivot-table-for-budget-analysis-in-microsoft-excel-768x312.png 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/12\/Dynamic-pivot-table-for-budget-analysis-in-microsoft-excel.png 876w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\">A look at the Pivot Table and the timeline.<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">By selecting the desired month, the accompanying numbers in the pivot table will change automatically.<\/p>\n\n\n\n<h2 id=\"analyze\" class=\"wp-block-heading\">Ready to analyze budget vs. actuals on your own?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">By using the steps described in this article, you will create the actual vs. budget report in less time using just a few formulas.&nbsp;Once the source data is changed, you can simply update the report by choosing Data \u2192 Queries &amp; Connections \u2192 Refresh All.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Or, skip the formulas and use a tool that does all the calculating for you. <a href=\"https:\/\/beebole.com\/project-financials\">Beebole\u2019s project financials reporting<\/a> tracks budget vs. actuals automatically and in real time, without a Power Query refresh. Set <a href=\"https:\/\/beebole.com\/help\/documentation\/billing#billing-rates-turn-tracked-time-into-revenue\">billing<\/a>, <a href=\"https:\/\/beebole.com\/help\/documentation\/costs#cost-rates-track-labor-costs-and-margins\">cost<\/a>, and <a href=\"https:\/\/beebole.com\/help\/documentation\/budgets#setting-up-a-budget\">budgets<\/a> once \u2014 at the organization, tag, project, or person level \u2014 and Beebole updates the variance as time is logged, with no month-end wait and no data model to maintain each reporting cycle.<\/p>\n\n\n\n<figure data-wp-context=\"{&quot;imageId&quot;:&quot;6a64142c5df88&quot;}\" data-wp-interactive=\"core\/image\" data-wp-key=\"6a64142c5df88\" class=\"wp-block-image aligncenter size-large wp-lightbox-container\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"438\" data-wp-class--hide=\"state.isContentHidden\" data-wp-class--show=\"state.isContentVisible\" data-wp-init=\"callbacks.setButtonStyles\" data-wp-on--click=\"actions.showLightbox\" data-wp-on--load=\"callbacks.setButtonStyles\" data-wp-on--pointerdown=\"actions.preloadImage\" data-wp-on--pointerenter=\"actions.preloadImageWithDelay\" data-wp-on--pointerleave=\"actions.cancelPreload\" data-wp-on-window--resize=\"callbacks.setButtonStyles\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2026\/07\/Performance-profitability-report-Beebole-700x438.png\" alt=\"Beebole&#039;s reporting functionality lets you track performance, profitability and much more at project, task, team, employee, and tag level\" class=\"wp-image-14787\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2026\/07\/Performance-profitability-report-Beebole-700x438.png 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2026\/07\/Performance-profitability-report-Beebole-768x480.png 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2026\/07\/Performance-profitability-report-Beebole-1536x960.png 1536w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2026\/07\/Performance-profitability-report-Beebole.png 1600w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><button\n\t\t\tclass=\"lightbox-trigger\"\n\t\t\ttype=\"button\"\n\t\t\taria-haspopup=\"dialog\"\n\t\t\tdata-wp-bind--aria-label=\"state.thisImage.triggerButtonAriaLabel\"\n\t\t\tdata-wp-init=\"callbacks.initTriggerButton\"\n\t\t\tdata-wp-on--click=\"actions.showLightbox\"\n\t\t\tdata-wp-style--right=\"state.thisImage.buttonRight\"\n\t\t\tdata-wp-style--top=\"state.thisImage.buttonTop\"\n\t\t>\n\t\t\t<svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"12\" height=\"12\" fill=\"none\" viewBox=\"0 0 12 12\">\n\t\t\t\t<path fill=\"#fff\" d=\"M2 0a2 2 0 0 0-2 2v2h1.5V2a.5.5 0 0 1 .5-.5h2V0H2Zm2 10.5H2a.5.5 0 0 1-.5-.5V8H0v2a2 2 0 0 0 2 2h2v-1.5ZM8 12v-1.5h2a.5.5 0 0 0 .5-.5V8H12v2a2 2 0 0 1-2 2H8Zm2-12a2 2 0 0 1 2 2v2h-1.5V2a.5.5 0 0 0-.5-.5H8V0h2Z\" \/>\n\t\t\t<\/svg>\n\t\t<\/button><figcaption class=\"wp-element-caption\">Beebole blends project time tracking and planning data to provide in-depth project and financial reports. Get deeper insight and real-time reporting across all your people and projects.<\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Implications of budget vs. actuals on project profitability<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong><a href=\"https:\/\/beebole.com\/blog\/how-to-calculate-project-profitability\/\" data-type=\"post\" data-id=\"8571\">Project profitability<\/a><\/strong> analysis involves assessing the revenue, costs, and profit associated with a project to understand if it is financially beneficial. It primarily focuses on whether the project has or will achieve the desired return on investment.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">On the other hand, <a href=\"https:\/\/beebole.com\/blog\/budget-to-actuals-variance-analysis\/\">budget versus actuals analysis<\/a> is a method of comparing the budgeted costs of a project against the actual expenses incurred. This analysis helps in tracking the financial performance of a project, identifying variances, and implementing corrective actions.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The relationship between these two stems from their focus on project financials and performance. <strong>The results from a budget versus actuals analysis can significantly influence project profitability analysis<\/strong>: if actual costs are consistently over budget, it could indicate inefficiencies or unforeseen expenses, leading to lower profitability than initially projected. Conversely, if actual costs are consistently under budget without compromising the project&#8217;s output, it could indicate cost efficiency, potentially increasing the project&#8217;s profitability.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Therefore, <strong>effective budget versus actuals analysis can enable a more accurate project profitability analysis<\/strong>. It allows project managers to monitor costs, identify cost trends, and make necessary adjustments to ensure the project remains profitable. Similarly, project profitability analysis can offer valuable insights for refining the budgeting process, leading to more accurate and realistic budgets in the future.<\/p>\n\n\n\n<div  class=\"mx-auto bbl_suggested_post_block my-5\">\n\t<a class=\"bbl_sp_block-blockcontent align-items-center d-flex flex-column flex-md-row justify-content-center\" href=\"https:\/\/beebole.com\/blog\/payroll-variance-guide-finance-hr-managers\" title=\"Payroll variance explained: A comprehensive guide for finance and HR managers\">\n\t\t<img\n\t\t\talt=\"Payroll variance explained: A comprehensive guide for finance and HR managers\"\n\t\t\tclass=\"d-block h-auto\"\n\t\t\tloading=\"lazy\"\n\t\t\theight=\"153\"\n\t\t\tsrc=\"https:\/\/beebole.com\/blog\/wp-content\/themes\/sage\/public\/images\/suggested-post.cad3bf.png\"\n\t\t\twidth=\"233\"\n\t\t\/>\n\t\t<div class=\"bbl_sp_block-text-col\">\n\t\t\t\t\t\t\t<div class=\"mb-1\"><div class=\"bbl_sp_block-label mb-3\">RELATED POST<\/div><\/div>\n\t\t\t\t\t\t<div class=\"bbl_sp_block-title\">Payroll variance explained: A comprehensive guide for finance and HR managers<\/div>\n\t\t\t\t\t\t\t<div class=\"bbl_sp_block-button mt-3\">\n\t\t\t\t\tRead more\t\t\t\t\t<svg width=\"14\" height=\"14\" viewBox=\"0 0 14 14\" fill=\"none\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\">\n\t\t\t\t\t\t<path d=\"M5.25 10.5L8.75 7L5.25 3.5\" stroke=\"white\" stroke-width=\"1.16667\" stroke-linecap=\"round\" stroke-linejoin=\"round\" \/>\n\t\t\t\t\t<\/svg>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t<\/a>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong>If you&#8217;re a financial manager or someone <a href=\"https:\/\/beebole.com\/blog\/google-sheets-pivot-tables-reporting\/\">who works with budgets and reporting regularly,<\/a> we genuinely hope this tutorial shows that reporting budget vs actuals doesn&#8217;t need to be cumbersome or time-consuming<\/strong>. <\/p>\n\n\n\n<p class=\"has-text-align-center wp-block-paragraph\"><em>&#8211;<br>Don&#8217;t miss our other <a href=\"https:\/\/beebole.com\/blog\/category\/learn-tutorials-howtos\/\">tutorials<\/a> on topics like <a href=\"https:\/\/beebole.com\/blog\/excel-power-query-for-business-intelligence\/\">Excel Power Query hacks<\/a> and <a href=\"https:\/\/beebole.com\/blog\/how-build-timesheet-automated-reports-in-excel-power-query\/\">how to build an automated time tracking dashboard<\/a>.<br>&#8211;<\/em><\/p>\n<div class=\"bbl-post-disclaimer\">The experts who have written or contributed to this article are independent from Beebole, and their contribution doesn't serve as endorsement for our company\/tool or their past\/present organizations, employers, or associates.<\/div>","protected":false},"excerpt":{"rendered":"<p>In this tutorial, learn how to create a budget vs. actuals report in Excel using Power Query. Gain insights and track financial performance effortlessly. As a financial controller, accountant, or CFO, you&#8217;re likely familiar with the concept of budget vs. actuals. You know that reporting budget vs. actuals can be both cumbersome and time-consuming, given [&hellip;]<\/p>\n","protected":false},"author":27,"featured_media":10729,"comment_status":"open","ping_status":"open","sticky":true,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[4011],"tags":[3980,3989,4013],"class_list":["post-7687","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-project-management","tag-reporting","tag-excel","tag-templates"],"acf":[],"_links":{"self":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts\/7687","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/comments?post=7687"}],"version-history":[{"count":47,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts\/7687\/revisions"}],"predecessor-version":[{"id":14797,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts\/7687\/revisions\/14797"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/media\/10729"}],"wp:attachment":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/media?parent=7687"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/categories?post=7687"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/tags?post=7687"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}