Welcome back to QuickBooks Online 2023! In this blog post, we will continue our journey with QuickBooks Online, focusing on budgeting, exporting reports to Excel, and modifying data. Join us as we explore these essential features and learn how to create a budgeted income statement for your business. Let’s dive right in and start moving up with QuickBooks Online in 2023!
Getting Started with QuickBooks Online: To begin, make sure you have your Good Greg Guitars practice file set up in QuickBooks Online. If you’d like to compare it with the sample company, open the QuickBooks Online test drive in a separate browser or incognito window. You can access the test drive by typing “QuickBooks Online test drive” in the search engine, opening it in incognito mode on Google Chrome, or using another browser. By toggling between the accounting view and the business view, you can compare the different perspectives. Duplicate tabs by right-clicking on the tab to organize reports efficiently.
Generating Reports: Navigate to the Reports section on the left side and open the Balance Sheet Standard report. This report provides a snapshot of your business’s financial position. Additionally, close the handbook and open the Profit and Loss report from the reports menu. Adjust the date range in the Profit and Loss report to view a month-by-month breakdown, enabling a side-by-side comparison of your financial data. Once you have the desired date range, refresh the report to update the information.
Creating a Budget: To create a budget in QuickBooks Online, access the cog drop-down menu and select “Budgeting.” However, before entering the budget, we need to gather the necessary data. In previous presentations, we exported a trial balance, extracted the income statement accounts, and ensured that the balance aligns with the income statement. Now, let’s use this information as our starting point and project it over 12 months.
Modifying the Budget: To modify the budget, we’ll start by inserting a row above the existing data and adding header columns for each month. This way, we can project the budget figures accurately. Once the headers are in place, divide the prior two months’ total by two to get the average for January. Copy this formula down the column to calculate the monthly amounts. Next, copy the formula across the row to extend the budget to December. Remember, you can use absolute references, mixed references, or reference the prior period directly based on your preference. Finally, copy the entire budget across to complete the monthly breakdown.
Modifying the Budgeted Income Statement: After creating the budgeted income statement, it’s time to make adjustments based on projected changes. Let’s review each line and determine what modifications are needed. Before we proceed, let’s make the source data stand out by highlighting it with a different color and adding brackets for clarity.
Calculating Monthly Changes: To begin, let’s calculate the changes for each month. We’ll insert a row below the source data and label it as “Total” or “Year-to-Date.” The total column will sum up the monthly figures for each line. Use the SUM formula to calculate the total for each month. Copy the formula across the row to cover all months. Finally, copy the formula down to calculate the year-to-date total for each line.
Making Adjustments: As we review each line, we can consider different factors that may influence the numbers. For example, for revenue accounts, you might project an increase based on a percentage change or a step increase after a certain timeframe. Let’s assume a 5% increase from January to February and onwards. To calculate this, multiply the prior amount by 1.05 (105%). Copy the formula across the row to apply the increase to all months.
Addressing Fixed Expenses: Fixed expenses, such as utilities, may remain constant throughout the year unless there are seasonal fluctuations or specific changes in the business environment. For these expenses, you can keep the amounts the same across all months.
Calculating Expense Changes: Some expense accounts may experience variations based on specific factors. For example, if you expect a 10% increase in a particular expense account, use the formula: prior amount x 1.10 (110%). Copy the formula across the row to apply the increase to all months.
Updating the Budget: By modifying the revenue and expense accounts, you are projecting changes based on your analysis and projections. After making all the necessary adjustments, copy the formulas across and down the sheet to complete the budgeted income statement.
Adjusting Depreciation and Interest Expenses: Depreciation is an important factor to consider when projecting your budget. If you have detailed depreciation schedules, you can calculate the exact depreciation amount based on your equipment. However, for simplicity, we will assume the depreciation remains the same throughout the year.
Next, let’s focus on interest expenses. Interest is typically related to loans, and as you repay your loans, the interest expense decreases over time. To reflect this, we can project a gradual decrease in interest expenses. Let’s assume a 5% decrease starting in February. Use the formula: prior period amount multiplied by 0.95 (or 95%). Copy the formula across the row to apply the decrease to all months.
Maintaining Expense Stability: Certain expenses may remain stable throughout the year, such as miscellaneous expenses. For simplicity, we can leave these expenses unchanged.
Enhancing Presentation: Let’s take a moment to clean up the budgeted income statement and make it more visually appealing. Select the entire table and add brackets for better readability. Additionally, apply a double underline to the total row for a clear distinction.
Verifying Totals: To ensure accuracy, it’s important to verify the totals. You can double-check by summing up the totals vertically and horizontally. This cross-validation provides an additional layer of assurance for your budgeted income statement.
Analyzing Net Income: Take note of the net income figure at the bottom of the income statement. In this example, February shows a negative amount due to the large insurance expense incurred. This serves as a reminder to carefully consider cash flow implications when dealing with prepayment systems. However, net income gradually improves over time, indicating a positive trend.
Next Steps: Now that we have completed the modifications to our budgeted income statement, the next step is to enter the budget back into QuickBooks Online. We will cover this process in our next blog post. By entering the budget into QuickBooks Online, we can easily generate budget versus actual reports as time progresses, which is one of QuickBooks’ core strengths. Stay tuned for our next episode to learn more!
Conclusion: Congratulations! You have successfully modified the budgeted income statement in QuickBooks Online 2023. By adjusting revenue and expense line items, you have created a customized budget that reflects your projected changes for the year. Remember to consider factors such as step increases, cash flow basis, and maintaining relationships between revenue and expense accounts. With a well-crafted budget, you can effectively plan and analyze your business’s financial performance. Get ready for our next blog post, where we will guide you through entering the budget into QuickBooks Online. Join us as we continue exploring the incredible features of QuickBooks Online in 2023!