Excel for CFOs’ Post

Build a STUNNING Budget vs Actuals Dashboard Credits to Josh Aharonoff, CPA, follow him for more content. The original post ---- Build a STUNNING Budget vs Actuals dashboard 😍 This is one of my most popular dashboards, and today I’m teaching you how to build one, and giving you the chance to get your own copy for free 📽️ Watch the full tutorial for FREE 🔗 https://lnkd.in/gxHw3Kyx Let’s start with a few definitions: ➡️ What is a Budget vs Actuals report, and why does it matter? Budget vs Actuals is a report that summarizes what took place vs what you THOUGHT would take place. It’s common to show this report on a summarized basis with the most important metrics that you are tracking with your company - typically a summarized P&L, and cash flows. I’ve found this report to be the most valuable as it allows you to confirm how tight of a grip you have on what exactly is happening in your business. No…you do NOT need to show 0 variances, but you DO need to have a story for each and every number, and why you didn’t hit your target. Was it a gap in your assumptions? Was it a timing issue? Was it due to missing performance targets? Each of these questions should be answered for every variance. ➡️ How do you prepare a Budget vs Actuals dashboard? There are 2 parts to building a Budget vs Actuals dashboard…building your table, and building your visuals PART 1: THE TABLE The key here is to ensure that you are including both # variances, and % variances… Why? Because it gives CONTEXT. A $1m miss may or may not be significant, depending on what % of a miss that is. Additionally, I like to make my formulas as such for calculating # variances: ACTUAL - BUDGET for income accounts (Revenue, Gross Profit, Net Income etc.). BUDGET - ACTUAL for expense accounts (COGS, Opex, other expenses) This helps you ensure that all GOOD variances get shown as POSITIVE and all BAD variances get shown as NEGATIVE PART 2: THE CHARTS What good is a dashboard if you don’t have pretty visuals to accompany it? That’s where the doughnut chart comes in - my favorite for showing % variances. The way a doughtnut chart works is you show one % for your variance, and another % for the remaining amount to get to 100% Hit Insert > Insert a Pie or Doughnut Chart, and select doughnut I like to create 4 series - 2 series that will populate to 100% if there is a positive variance, and 2 series that will populate to 100% if it’s a negative variance. This will allow you to populate your donut chart with different colors to represent positive or negative variances === I’ve present this dashboard do dozens of founders and investors over the year, and it never fails to please the eye. What has been your experience with preparing a budget vs actuals report? Let me know in the comments and be sure to watch the full video below 👇 ---- Follow our page Excel for CFOs to learn more about Excel in Finance & Accounting.

Learn EVERY Excel keyboard shortcut Credits to Josh Aharonoff, CPA, follow him for more content. The original post ---- Learn EVERY Excel keyboard shortcut 👇 Hold down these keys and watch the magic unfold 🪄 👉 Print this out for FREE and hang it on your office wall 🔗 https://lnkd.in/eJxcZH2X These are my favorites: ➡️ ONE KEY ⬜ Esc - Exit edit mode ⬜ F1 - Help ⬜ F2 - Edit ⬜ F3 - Name Manager ⬜ F4 - Fix references ⬜ F5 - Goto ⬜ F6 - Switch ⬜ F7 - Spelling ⬜ F8 - Extend ⬜ F9 - Calculate ⬜ F10 Key tips ⬜ F11 Charts ⬜ F12 - Save as ⬜ Tab - Go to next cell ⬜ Enter - move down ⬜ End - Move to end ⬜ Alt - Key tips ⬜ Home - Move to beginning ➡️ CTRL SHORTCUTS ⬜ ` - Toggle value ⬜ 1 - Format ⬜ 9 - Hide rows ⬜ 0 - Hide columns ⬜ - - Delete row / column ⬜ + - Insert row / column ⬜ R - Cill right ⬜ T - Create Table ⬜ Y - Redo ⬜ D - Fill down ⬜ F - Find ⬜ G - Go to ⬜ H - Replace ⬜ K - Hyperlink ⬜ : - current date ⬜ PGUP - Go to previous tab ⬜ PGDN - Go to next tab ⬜ Arrows - jump to next / last active cell ⬜ Space - select entire column ➡️ ALT SHORTCUTS ⬜ F4 - Exit ⬜ = - Autosum ⬜ Down arrow - Data validation ➡️ SHIFT SHORCUTS ⬜ Arrows - Highlight cells ⬜ F11 - New worksheet ⬜ Space - Select entire row ➡️ CTRL + SHIFT SHORTCUTS ⬜ # - Date format ⬜ @ - Time format ⬜ ~ - General format ⬜ ! - Comma format ⬜ % - Percentage format ⬜ $ - Dollar Format ⬜ : - Current time ⬜ V - paste values ---- Follow our page Excel for CFOs to learn more about Excel in Finance & Accounting.

The 3 most POPULAR Excel Lookup functions Credits to Josh Aharonoff, CPA, follow him for more content. The original post ---- the 3 most POPULAR Excel Lookup functions 🔍 Learn which one to use and when 👇 📽️ Watch the full tutorial for FREE 🔗 https://lnkd.in/eypZkd2A Lookup functions are REALLY popular in excel… because they allow you to “lookup” a value from a dataset, based off of criteria that you enter. Most people only focus on Vlookup, without realizing that there is a far more powerful lookup functions available called Xlookup and Index / Match. Let’s explore these 3 lookup functions and become a pro: 1️⃣ VLOOKUP 💡 How it works ��� Searches VERTICALLY in the first column of a specified range and returns a value in the same row from a column you specify. 🧮 Syntax → =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) ✅ Pros →Easy to use for vertical lookups, Supported in all versions of Excel. ❌ Cons → Limited to vertical searches, Searches must start in the first column of the range. 🤔My take → VLOOKUP is probably the most common lookup function, but it’s sooo limited. Learn to ditch this function and focus on the other 2 below! 2️⃣ XLOOKUP 💡 How it works → Searches for a value in an array or range in EITHER DIRECTION and returns a value from a corresponding array or range. 🧮 Syntax → =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found] ✅ Pros → Can search in any direction, Allows for return of an array and provides an option for a default value if no match is found, is very efficient. ❌ Cons → Only available in Excel for Office 365, Excel 2019, and later versions, can be complex 🤔My take → XLOOKUP solves all the issues that VLOOKUP and HLOOKUP has, and little by little will be taking over the excel lookup universe. 3️⃣ INDEX / MATCH 💡 How it works → Like XLOOKUP, INDEX / MATCH searches for a value in an array or range in EITHER DIRECTION and returns a value from a corresponding array or range. 🧮 Syntax → =INDEX(RANGE,ROW, COLUMN) =MATCH(VALUE,LOOKUP RANGE, MATCH TYPE) Combine the 2: =INDEX(RANGE, MATCH(VALUE,LOOKUP RANGE, MATCH TYPE), MATCH(VALUE,LOOKUP RANGE, MATCH TYPE)) ✅ Pros → Very simple syntax, and like XLOOKUP you can search in any direction. ❌ Cons → The formula may become a bit lengthy 🤔My take → I love INDEX / MATCH, and use it’s my go to lookup function…but I can’t say it’s that much better than XLOOKUP, it’s more a personal preference. === That’s my take on the 3 most popular excel lookup functions. What about you? What’s your favorite lookup function? ---- Follow our page Excel for CFOs to learn more about Excel in Finance & Accounting.

Excel Errors Cheat Sheet Credits to Mynda Treacy, follow her for more content. The original post ---- Excel errors driving you crazy? DIV/0!, N/A, VALUE!, REF!, NAME?... The list goes on. We’ve all been there – working on an important spreadsheet and suddenly hit with those frustrating Excel errors. What if I told you there's a simple way to handle these errors like a pro? Introducing the Excel Errors Cheat Sheet – your go-to guide for diagnosing and fixing common Excel errors quickly and effectively. 🔍 What’s inside? - IFNA: Seamlessly manage N/A errors. - ISERROR: Detect any error with ease. - IFERROR: Create flawless reports. - ERROR.TYPE: Identify specific error types. - Debugging Tips: Step-by-step guide to evaluate and trace formulas. Don’t let Excel errors slow you down. Equip yourself with the knowledge to troubleshoot and resolve them effortlessly. Download the FREE full-size Excel Errors Cheat Sheet in the best resolution here: https://lnkd.in/dM3wmQ-e ❓What’s the most annoying Excel error you encounter, and how do you usually deal with it? ---- Follow our page Excel for CFOs to learn more about Excel in Finance & Accounting.

Top 21 Excel Formulas Credits to Nicolas Boucher, follow him for more content. The original post ---- Top 21 Excel Formulas 👉 Download the High-Resolution PDF: https://lnkd.in/euz2N8jE 10x your productivity with these 21 Excel must known formulas: 1. SUM Adds all numbers in a range Example: =SUM(A1:A9) Add values from cell A1 to A9 2. AVERAGE Calculates the average of numbers in a range Example : =AVERAGE(B1:B9) Average of B1 to B9 3. ROUND Rounds a number to a specified number of digits Example: =ROUND(C1;2) Round C1 to 2 decimal place s 4. MIN Identifies the lowest number in a range Example: =MIN(D1:D9) Find the minimum in D1 to D9 5. MAX Finds the highest number in a range Example: =MAX(D1:D9) Get the maximum value in D1 to D9 6. COUNT Counts the number of cells that contain numbers Example: =COUNT(F1:F9) Count numeric cells in F1 to F9 - It only counts the numerical inputs 7. INDEX Returns a value in table based on row & column number Example: =INDEX(K1:L10,2,3) For the value in the 2nd row & 3rd column from the range K1:K10 8. MATCH Searches for a value and returns its relative position Example: =MATCH(L1,M1:M9,0) To find L1's position from the range M1:M9 9. VLOOKUP Searches for a value in the first column of a range Example: =VLOOKUP (G1,H1:H10,2,FALSE) To find G1 in H1:H10 10. HLOOKUP Searches for a value in the top row of a table - Number of rows that contains the data you want to retrieve Example: =HLOOKUP(I1,J1:S2,2,FALSE) To find I1 in the top row J1:S2 11. COUNTIF/COUNTIFS Counts cells that meet a condition Example: =COUNTIF (N1:N9;">5") To count cells greater than 5 12. COUNTA Counts non-empty cells Example: =COUNTA(O1:O9) To count all non-empty cells in a range 13. SUMIF / SUMIFS Counts non-empty cells Example: =SUMIF(P1:P9,">5") Add cells that meet a condition 14. IF / NESTED IFS Performs conditional tests Example: =IF(R1>5;"Yes";"No") For simple conditions Note: IF: 1 variable IFS: multiple variables =IF(test, true, false) =IF(test1, true, IF(test2, true, false)) Nested IF =IFS(test1, true, test2, true, test3, true) 15. AVERAGEIF / AVERAGEIFS Calculates the average for cells that meet criteria =AVERAGEIF(Q1:Q9;">5") Average of values over 5 16. Logical Operators (AND, OR) Combines multiple conditions Examples: =AND(S1>5;S2<2) Both conditions are true => Output is true =OR(S1>5;S2<2) Just 1 condition is true => Output is true 17. CONCATENATE Joins two or more text strings Example: =CONCATENATE(T1," ",T2) Combine first name and last name in one cell 18. LEFT/RIGTH Extracts characters from the start/end of a text string 19. MID Extracts characters from the middle of a text string 20. TEXT Converts a value to text in a specific number format 21. IFERRROR Returns a custom result if a formula errors out ---- Follow our page Excel for CFOs to learn more about Excel in Finance & Accounting.

Excel Regex Cheat Sheet Credits to Mynda Treacy, follow her for more content. The original post ---- Get started with the NEW RegEx Functions in Excel with our free cheat sheet. RegEx is your game-changer for cleaning messy text. Download the cheat sheet in hi-res along with the example Excel file and watch the step-by-step video today, and simplify your text tasks: https://bit.ly/excelregex Use the cheat sheet as a quick reference guide with: - step by step writing guide for RegEx - common tokens - new RegEx Function examples Transform how you work with text in Excel. ❓ How has mastering Excel’s advanced tools helped you at work? ---- Follow our page Excel for CFOs to learn more about Excel in Finance & Accounting.

Learn the most POWERFUL Excel function Credits to Josh Aharonoff, CPA, follow him for more content. The original post ---- Learn the most POWERFUL Excel function SUMPRODUCT 📽️ Watch the FULL VIDEO over here: 🔗 https://lnkd.in/eqY38Jik SUMPRODUCT is so powerful, it replaces the need for these functions: ❌SUMIFS ❌INDEX/MATCH ❌XLOOKUP ❌COUNTIFS ❌AVERAGEIFS Let's go through step-by-step to learn how it works ➡️ THE SIMPLE USE CASE Most people use SUMPRODUCT with a very basic use case - take the PRODUCT of 2 fields…and SUM them together. A perfect example is if you have a pipeline export from a CRM that showcases the close likelihood. To get the weighted average you would simply enter the following: =SUMPRODUCT(contracts, close likelihoods) But SUMPRODUCT is much more powerful than that ➡️ THE LOGIC SUMPRODUCT allows you to SUM the values that meet one or more CONDITIONS… and those CONDITIONS can pretty much be anything. Let’s assume we have a 3x3 grid of values with the following X and Y axis. Our X ↔ axis will showcase Jan Feb and March Our Y ↨ axis will showcase Revenue, Gross Profit, and Net Income We can take all of our values inside our 3x3 grid and sum the amounts that intersect with a TRUE condition for our X axis for Y axis For example…to get the Jan amount for Gross Profit, we would write =SUMPRODUCT((VALUES)*(MONTHS=”JAN”)*(ACCOUNTS=”GROSS PROFIT”)) Notice how this formula works? It’s taking the values inside these parenthesis and placing them in arrays. It then MULTIPLIES these arrays against each other, where the arrays with conditions get converted to 1 for true, and 0 for false. Here’s how to now scale this formula for other functions… ➡️ SUMIFS Let’s say we wanted to get the values for Gross Profit from Jan - Feb… using the same structure, we can rewrite as such: =SUMPRODUCT((VALUES)*(MONTHS>1/1/2024)*(MONTHS≤2/29/2024)*(ACCOUNTS=”GROSS PROFIT”)) ➡️SUMIFS OR Let’s say we wanted to get the values for Gross profit OR Net Income from Jan to Feb… for that, we’d ADD conditions together, and wrap them around a new parenthesis like such: =SUMPRODUCT((VALUES)*(MONTHS>1/1/2024)*(MONTHS≤2/29/2024)*((ACCOUNTS=”GROSS PROFIT”)+(ACCOUNTS=”NET INCOME”)) ➡️ AVERAGEIFS To get the average, it’s fairly simple - but first, we need to get the count. Using the example above, all we need to do is remove the (VALUES) array, so that we’re just left with 1s and 0s from our conditions. We then take the values from our example above and divide them by this count, like this: =SUMPRODUCT((VALUES)*(MONTHS>1/1/2024)*(MONTHS≤2/29/2024)*((ACCOUNTS=”GROSS PROFIT”)+(ACCOUNTS=”NET INCOME”)) / =SUMPRODUCT((MONTHS>1/1/2024)*(MONTHS≤2/29/2024)*((ACCOUNTS=”GROSS PROFIT”)+(ACCOUNTS=”NET INCOME”)) === The SUMPRODUCT function has been a total game-changer for me and is something I use extensively in my dashboards course. ---- Follow our page Excel for CFOs to learn more about Excel in Finance & Accounting.

9 Excel Hacks Credits to Amit Kumar, follow him for more content. The original post ---- Everybody tells you to use EXCEL. But nobody tells you how to save time and reduce errors while using EXCEL. Here's a no-fluff guide to 9 essential Excel shortcuts that genuinely save time and reduce errors. Excel shortcuts covered: - Jump to the Referenced Cell - Trace Dependencies - Toggle Formulas/Results - Edit Links - Convert Text to Numbers - Dynamic VLOOKUP - Sum Across Sheets - Selective Summation - Workbook-wide Find/Replace Whether you're consolidating data from multiple sheets or filtering through reports with precision, understanding these hacks means less time wrestling with Excel and more time getting accurate, actionable insights. Let's dive into making Excel work harder for you, not the other way around. ____ Any other hack you would add? ---- Follow our page Excel for CFOs to learn more about Excel in Finance & Accounting.

XMATCH Cheatsheet 📄 Credits to Financial Modeling World Cup world cup, follow them for more content. The original post ---- XMATCH Cheatsheet 📄 Download a free high-resolution PDF on our website (link in the picture)! The XMATCH function searches for a specified value in a range or array and returns the relative position of the item found. 1️⃣ Arguments: lookup_value: The value you want to search for. lookup_array: The range or array where the lookup value will be searched. [match_mode] (optional): Specifies the type of match. Default is 0 (exact match). 0 or omitted: Exact match. -1: Exact match or next smallest item. 1: Exact match or next largest item. [search_mode] (optional): Specifies the direction of the search.  Default is 1 (search from top to bottom or left to right). 1 or omitted: Search from top to bottom or left to right. -1: Search from bottom to top or right to left. 2️⃣ Return Value: The position of the matched item relative to the lookup array. Returns #N/A if no match is found. 3️⃣ Notes: -XMATCH is a newer version of the MATCH function in Excel, introduced in Excel 2019 and Excel for Microsoft 365. -It offers more flexibility than the MATCH function by allowing for approximate matching and specifying the search direction. -XMATCH can handle both vertical and horizontal lookup arrays. 4️⃣ Tips: -Use XMATCH for advanced lookup scenarios where you need more control over the matching criteria and search direction. -Experiment with different match modes and search modes to tailor the function to your specific requirements. -Combine XMATCH with other functions like INDEX and IFERROR for more powerful lookup formulas. ---- Follow our page Excel for CFOs to learn more about Excel in Finance & Accounting.

8 Popular Accounting Tasks that you can do in Excel Credits to Josh Aharonoff, CPA, follow him for more content. The original post ---- 8 Popular Accounting Tasks that you can do in Excel Learn each to 10x your productivity 🚀 1️⃣ PivotTables Have a large table? Odds are you can benefit by creating a Pivot Table out of it. PivotTables allow you to: ✅ Quickly query data sets ✅ Understand relationships ✅ Format data in a way that’s easy to understand To get started, take a table and hit Insert > PivotTable 2️⃣ Lookups Lookups are the most common functions in Excel. My favorites? These 2 will yield maximum flexibility in both directions: ✅ INDEX / MATCH ✅ XLOOKUP My least favorites? This one is outdated and is limited to only one direction: ❌ VLOOKUP 3️⃣ Conditional Formatting Formatting your file is a big part of using spreadsheets… but what if you wanted to format things dynamically? Say…based on a CONDITION? That’s the beauty of Conditional Formatting. I use this all the time with Budget vs Actuals. To get started, simply select your data set and hit Home > Conditional Formatting. 4️⃣ Charts & Graphs Charts are a big part of data management… and Excel has some of the best charting capabilities out there. My favorites are a Donut chart 🍩, and a combo chart 📈📊 To get started, select your data and click Insert > Charts 5️⃣ Data Validation Create a dropdown, and restrict the ability to enter only certain values in a cell. Extra points if you create a dynamic dropdown that extends via a table and named range! Set Data Validation by clicking Data > Data Validation 6️⃣ Power Query Total game-changer…and few realize just how powerful it is. One of my favorite tasks is to take a report like a Profit & Loss…and UNPIVOT the dates. This makes it infinitely easier to manipulate and pull into dashboards. Get started by hitting Data > From table/range (or any other source) 7️⃣ Sort & Filter Take your data set and present it however you want - filter for key accounts, and sort ascending, descending, or any custom rule that you prefer. Get started by selecting your table and hit Home > Sort & Filter 8️⃣ Use a Checklist Got a month-end close process? Create a checklist in Excel and check off each task as you complete it. Get started by hitting Developer > Insert > Check Box (enable the developer ribbon first). === Those are my 8 favorite features for accountants in Excel. What are yours? ---- Follow our page Excel for CFOs to learn more about Excel in Finance & Accounting.