Excel is considered the “Microsoft Office Excel’” equivalent in the world of open source software. It has been around for decades, so it’s not like it’s a secret that the software can be used to create invoices, track expenses, create graphs and pretty much do anything that you can think of that has numbers in it.
While Excel is great for any personal or home use, it still manages to be quite the money maker when it comes to businesses. If you are looking to make some extra cash, you can use this “relatively” inexpensive yet extremely versatile solution to do so. In this article, you will discover five ways that you can use Microsoft Excel to make money.
Create an Invoice Template
Excel comes with hundreds of built-in functions that can be used to create a variety of documents, but it can be hard to find the right one for the job. Many times, you have to create your own functions to do the tasks that you need, and that is where the fun begins. The better your function, the better your document will look. Before you start coding, it is essential that you create a sample document to see how your function will look like in practice. This way, you can ensure that the end result is what you want it to be and that it will be able to be replicated over and over again.
You can use the Sample Function (Alt+F11) to create a blank document. When you do, you will see a window with several pre-populated formulas that you can simply copy and paste to create your own document with the same functionality.
Create a Purchase Order
A purchase order is essentially an “order” that has been approved by a manager or superior to purchase a certain product or service. The form usually starts with a heading that includes the company’s details, the product or service that is being ordered, the quantity, the delivery date and time, and so on. You can use Excel to create purchase orders for your managers to approve, giving you the opportunity to spend more time working and less time collecting invoices.
As the name implies, a purchase order can be created using the Purchase Order (Manual Entry Mode) feature, which you can access using the Manage Purchase Orders button (shown in the image below).
The process is pretty simple. From the list of products or services that you want to purchase, you can drag and drop the items to the “order form” to create a purchase order. You can also add comments to the order, in case there is something special about that product that you want the buyer to be aware of. When you are done, just click the OK button to confirm the order and create a new one if there is more than one item you want to purchase.
You can use the Purchase Order template (shown below) to get started. Just click the File button on the main menu and then select “New” to get started with a blank template.
Track Inventory
Many businesses, especially those who deal with selling a large variety of products or providing services to a large number of customers, rely on keeping track of their inventory. This is especially important for retailers who want to ensure that they always have enough of the products in stock to meet the demands of their customers. You can use Excel to keep track of your inventory, purchasing new products when you are low on stock and canceling orders when you run out of something.
The first step is to identify which product or service you will use to keep track of your inventory. It’s best to pick a product or service that is easy to identify and gives you an unambiguous value. Once you have chosen that, create a new Excel workbook and follow these steps:
- Create a new sheet
- Enter a header that includes the item’s description and category (if applicable)
- Enter a formula =C1*(1+ROW()-1)
- Choose a color and font for the sheet (optional)
- Enter a short formula for the cell below the header to start the inventory
The formula in step two calculates the amount of the product or service in a particular row based on the row number (1 through whatever number of rows you have). The result of this formula is then plugged into the cell below the header to continue adding inventory. The general formula for this cell would be =C1*(1+ROW()-ROW())
For example, if you have 20 rows of inventory, the formula would look like this: =C1*(1+ROW()-ROW())
To keep track of your inventory, you will need to update this cell every time you get a new order or make an existing order be restocked, or cancel an order when you run out of the product. Updating this cell, especially for retailers who do a lot of online selling, can be a major hassle because the formula is buried in the cell values. To make this process easier, you can create a short cut to access this formula and place it on a separate tab (shown below). Users can then simply enter the required data and the tab will update itself without needing to look up the formula every time.
You can create a new tab (File >> New) and click on the Options button to bring up the Excel Options dialog (shown below). Choose “Customize Ribbon” from the list of items and then click the OK button to close the dialog. Now when you create a new workbook, the Inventory tab will be on the Ribbon.
Use Predefined Pricing Structures
Pricing can be complicated, particularly for retailers who want to apply different discounts for different situations. For example, maybe you have a 25% discount for a product if the customer is a fellow retailer or a member of a certain club, and a 10% discount for every other type of customer.
In those cases, you will need to have a look at the customers and decide which pricing structure to apply based on their situation. You can use Excel to generate discounts based on your criteria by creating and using a few simple formulas.
The first step is to identify which pricing structure you will use. If you are a one-man operation and do all the pricing yourself, you can simply enter the applicable discounts in the cell below the order form (shown below).
If you are taking orders and giving them to someone else to process, you will need to provide them with the information in a usable format. That format can be something as simple as a discount code or a more detailed list of the pricing rules. The better the format, the easier it will be for the people who have to process the orders. Also, make sure that the information is current. Since this is a list of discounts and rules that are subject to change, you will need to update it whenever there is a price change or new discount code released.
Create a new Excel workbook and follow these steps:
- Enter a header that includes the item’s description and category (if applicable)
- Enter a formula =VLOOKUP(A2,PriceBook!A$2:B$6,2,FALSE)
- Select the cell below the header to start the calculation
The first part of the formula, A2, refers to the location of the cell where you want to start the calculation. The second part, PriceBook!A$2:B$6, refers to the range of cells (rows in this case) where you want to do the lookup. The third part, 2, refers to the column number where you want to look up the value. The fourth part, FALSE, is a Boolean term meaning “search this column if True”. This formula looks at the third cell in the column (B2) and returns the price (in this case, £50) for the item.
When you run this formula, you will get a message similar to this one:
This formula is entering the value into cell B2, but you have locked the column. The cells A and B below can only be used for entering data.
Use Subscription Discounts
In today’s world, subscriptions are a huge part of how businesses and individuals make money. Many companies, such as Netflix, provide monthly payment plans for on-demand access to a large number of movies and other content. As a consumer, you have likely received a marketing email from Netflix or similar companies, offering a special discount just for signing up. This type of promotion usually takes the form of a reward card that can be used to access special discounts at a variety of retailers and restaurants.