Using Excel for Accounting: Small Business Recordkeeping

Name: Using Excel for Accounting: Small Business Recordkeeping
Author: Bob Akbar

Learn the tools and knowledge you need for your small business’s recordkeeping

Preview the first lesson free — get full access to all 3 lessons.

Course: On-Demand

In this lesson, I will show you forecasting. Small businesses often complain that they do not have the resources of the data to do proper forecasting, and that is simply not the case. If you will notice, there's only five items per year that I've put in here: Parts Sales, Service Revenue for our revenues; Utilities, Rent and Inventory for our expenses. And we are going to simply add up the income. We're going to add up the expenses. And we're going to figure out if there's a profit or loss by deducting the expenses from the income. And there we are. We can also use the AutoFill to extend this for all of our years. And we can even do a conditional format that will show us if we're going to have a profit or a loss. If it's greater than zero, we would like a green, that shows a profit. And if it's less than zero, we would like it light red with dark red to show a loss. So far so good when it comes to our existing data. This is reactive so far, now let's be proactive. And you have to hold the Control key while you drag your mouse across, and keep your Control key down while you select the one row. In this one, we're going to do Parts Sales. So we have the year selected and we have their numbers selected. Now we're going to go to Data, we're going to go to Forecast Sheet, and there it is. For 2023, Excel predicts that we will be at 20,000 for Parts Sales. Next, we're going to do the same thing, going to highlight our years. Hold down the Control key while we highlight the sales revenue. Let go of the Control key, go up to our data, and do another forecast. Here we are, so about 1550 to 1575. And as you can see, we don't need to be exact. There's a range where this prediction is happening, the lower confidence, the upper confidence, and the middle forecast. So I feel pretty confident with 15,750. Let's do the same thing for our expenses. Right about 27,000. Again, these are not guarantees, these are forecastings. The numbers do not need to be super precise. Our rent, I'm just going to guess that it's going to go up another $50. All right, let's do inventory. We're going to highlight the years, because we need something that is in sequence for Excel to be able to use, and then we're going to hold the Control key and do our inventory. Data, forecast, there it is! Right under 20,000. Again, we don't have to be exact with these. These are forecasts. There we are. All I'm going to do is AutoFill the formulas, and our 2023 forecast is we will be making 5,000 in profits. This is very easy to do for small businesses. I am surprised the more small businesses do not do this, perhaps if they see how easy it is. You need five input points, or as many as you wish, to be able to forecast what you think is going to be your outlook for the following year. Now, please keep in mind, the more data points you have for the previous years, the more accurate the forecasting will be, and the further out you go for the number of years you are forecasting, the less accurate it will be. Thanks for watching.

Intermediate Provider : Bob Akbar 3 Lessons · 25m in Arabic, German, English, Spanish, French, Portuguese, Chinese

Course Description

There is often a mystique around required bank forms. Small businesses sometimes feel like these are difficult to compose, and even more difficult to understand. In these lessons we will break down required bank forms into simple parts that anyone can use. We will look at Income Statements, Balance Sheets, and Statements of Cash Flow, explaining what of each of these is, what they are used for, and how to construct one yourself. It’s also important for small businesses to able to understand and interpret their financial data. We will explore different methods for visually displaying the information contained within financial forms. We’ll see that by separating data sets into charts and tables it can be easier to understand what is happening, and what we can expect. We will discuss why forecasting is so important for small businesses, before breaking the forecasting process down into simple steps accessible for any business. This course will explore small business record keeping with Excel. We’ll learn about required accounting forms, visual representations of data, and how forecasting can empower small businesses when accounting with Excel.

What You'll Learn

Identify required bank forms used in small business accounting
Understand the content of required bank forms and how and where they are used
Construct Income Statements, Balance Sheets, and Statements of Cash Flow yourself
Visualize financial data using charts and tables for easier interpretation
Apply the forecasting process by breaking it into simple, accessible steps

Key Takeaways

Required bank forms can be broken down into simple parts that anyone can use and understand.
The course covers Income Statements, Balance Sheets, and Statements of Cash Flow, explaining what each is, what it is used for, and how to construct one.
Separating data sets into charts and tables can make it easier to understand financial information and what to expect.
Forecasting is important for small businesses and can be broken down into simple steps accessible for any business.
The course explores small business record keeping with Excel, covering accounting forms, visual data representations, and forecasting.

Frequently Asked Questions

What does this course cover?

This course explores small business record keeping with Excel, covering required accounting forms, visual representations of data, and how forecasting can empower small businesses when accounting with Excel. It looks at Income Statements, Balance Sheets, and Statements of Cash Flow.

Who is this course for?

It is aimed at small businesses that want to compose, understand, and interpret required bank forms and their financial data using Excel.

What skills will I gain from this course?

You will build skills in Accounting, Accounting Records, Bookkeeping, Small Business Accounting, Small Business Financials, and Small Business Software.

What lessons are included?

The course includes three lessons: Required Bank Forms, How to Visualize Forms, and Forecasting.

Will I learn how to forecast for my business?

Yes. The course discusses why forecasting is important for small businesses and breaks the forecasting process down into simple steps accessible for any business.