In business, strategic planning is vital and requires accurate knowledge to make significant decisions. Adopting the appropriate tools for recording, tracking, analyzing, and storing data will help you make the best choices for your business. One such tool is the spreadsheet. A spreadsheet is an electronic file collection of data arranged as rows and columns in a grid that can be manipulated and used for calculations. 

If you are like most small-business owners, you’re always seeking ways to improve efficiency and increase your business’ profits without adding more stress and costs. Microsoft Excel (www.microsoft.com) is an excellent solution for your inventory needs.

Creating a Spreadsheet

Spreadsheets are an effective and efficient tool to help you logically format, process, and manipulate data.

Create a workbook:

1. Open Excel. 2. Select Blank workbook or press Ctrl+N.

To manually enter data:

1. Select an empty cell, such as A1, and then type text or a number. 2. Press Enter or Tab to move to the next cell.

Tracking Inventory

Here’s where spreadsheets become useful for a small pottery businesses, a gallery, or simply for a potter who wants to keep track of materials on hand, inventory available for online sales, completed or in-progress sales, and shipping. A spreadsheet allows virtually endless columns for categorizing and sorting data. Excel allows you to create a spreadsheet or download a premade template to help you manage inventory.

Inventory consists of raw materials and products that will be used or sold in the future. You produce pottery to sell, and unsold pieces are part of the inventory. Inventory is part of the business balance sheet and classified under current assets. Inventory can be Finished Pottery Pieces, Work in Progress, or Raw Material. To ensure inventory records are accurate and up to date, businesses periodically take an inventory count at the end of each quarter or year. Any difference between the counted inventory and inventory on a balance sheet is called Shrinkage. These issues arise because of numerous reasons like breakage, loss, stolen inventory, etc.

Use a spreadsheet to track inventory information such as a material’s SKU, bar code, or reorder point; or track a product’s description, quantity in stock, and value. You can also include expiration dates for glazes and images. 

List out categories and computations that best fit your needs. Some commonly used categories that you might want to include in the columns of your Excel spreadsheet are: 

  • SKU
  • Barcode or QR code
  • Description
  • Purchased Units
  • Balanced Units
  • Reorder quantity
  • Cost
  • Inventory value
  • Reorder flag

Inventory Calculations

You can add formulas and calculations to your inventory spreadsheet so the software does the all math for you. Some examples include: Quantity in Stock, Purchase Costs, Inventory Value, and Quantity in Reorder. 

Inventory formula example: Let’s say your studio has an opening inventory balance of $60,000 for the month of July. During the remaining financial year, the studio has made purchases amounting to $50,000, and during that time, on the studio’s income statement, the cost of goods sold is $40,000. Below is the data table:

Ending inventory is calculated using this formula:

Ending Inventory = Beginning Inventory + Inventory Purchases – Cost of Goods Sold

Using our example above this would be: 

  • Ending inventory = 60,000 + 50,000 – 40,000
  • Ending inventory = 70,000

Advanced Tracking: Sales Analysis

A sales analysis can assure your studio/business sales are meeting your expectations. Conducting a sales analysis can help you examine your actual sales to a minimum quota or a sales forecast. A sales analysis requires only fundamental mathematical functions like adding, subtracting, and dividing. Obtain data that incorporates sales for the period analyzed and the sales projection for the same period. 

Create a new Excel spreadsheet. Type or import your data. Click on the cell that has the data you want to show in a chart format. Set up the presentation or look of your chart. On the Standard toolbar, click on the Chart Wizard icon. The Chart Wizard window shows the chart types on the left side and the chart sub-type samples on the right side. Click on the chart type you want to use and select the chart subtype. Click on the Press and Hold to View Sample button to see a preview of your chart. Click on Next to go to the next window. Choose the format you want to display your data. Select Rows if you require to show your data in rows. Otherwise, select Columns if you need to illustrate the data in a column format. Click the Next button.

Select chart options. This window consists of the following five tabs: Titles, Axes, Gridlines, Legend, Data Labels, and Data Table. Go to each tab, and create your selections. Then click Next.

Create chart locations. Choose the As new sheet option if you want to insert your sales analysis chart into a new worksheet. Click the As object in selection if you desire to place the chart on your current worksheet. Click Finish.

The following is the example for creating an area chart in an Excel spreadsheet. Similarly, you can also create a pie chart or bar chart according to your preferences.

Advantages and Disadvantages of Spreadsheets

Organized Data: Spreadsheets are frequently used for collecting and organizing data. Data can easily be arranged in orderly columns and rows and sorted by information type. A vast array of data may be overwhelming to view in its crude state; tools within the program allow the user to create presentations where the data is analyzed and plugged into pie charts or tables for easy viewing and interpretation.

Nonetheless, the downside is only the information that the user chooses for analysis is included in these presentations, and therefore, other pertinent information that may influence decision making might be excluded, unintentionally. To make reporting of data more user friendly and comprehensive, companies are electing to use reporting tools such as Tableau (www.tableau.com) and Qlik (www.qlik.com/us), instead of relying solely on the spreadsheet.

Streamlined Computations: You don’t want to spend the whole time at work doing repetitive calculations. The great appeal of spreadsheets is that the program does all the math for the user. Let me repeat that: the software program does all the math for you! Once a formula is written and the program has a set command, complex computations can easily be computed for the related data that has input. 

For example, if the spreadsheet is set up to calculate your gross profit and you determine that variables such as cost per unit, shipping costs, or sales discount have changed, once you make updates to those variables, the software automatically recalculates the new gross profit based on the new information.

The difficult part for many users is that the calculations must be entered into the spreadsheet as formulas, which requires learning the correct syntax for each type of calculation needed. If the syntax is incorrect, the program will not calculate the correct information when the calculations are run. Additionally, if users input the wrong data, even in only one cell of the spreadsheet, all related calculations and cells will be affected and have incorrect data.

User Access: Multiple users within your studio might need access to the same documents. If using Microsoft Excel, the spreadsheets can be shared, but only one user can change data at a time. If local copies are made and updated, other users will not have access to the new data. To solve this problem, Google Sheets (www.google.com/sheets/about) offers file sharing and allows multiple users to access and update a single spreadsheet. In both cases, there is no file history. Another spreadsheet downside is the lack of file security. Typically, spreadsheets are not very secure and therefore are at greater risk for data corruption or information mismanagement. Files that contain sensitive financial information may not be safe from hackers, even if password protected.

Mamta Gholap, a frequent contributor to Pottery Making Illustrated, earned her MBA in finance, and is passionate about handbuilding with clay.

Topics: Ceramic Artists

Related Content