Frame Board Sold Analysis

Welcome to the optical sales analysis spreadsheet. This spreadsheet through all of the tabs will serve as proper frame board management and budget management so that we can maintain the proper cost of goods in our dispensary. Let's get started! 

First, we have our initial analysis tab. The purpose of the initial analysis tab is to assist in evaluating the effectiveness of the frame board mix. First by evaluating the brands and how much board space they require versus the total quantity sold in the previous twelve months. 

Next, by comparing the retail pricing breakdown of the entire board to help evaluate pricing, in order to achieve a practice-specific average frame price, while maintaining an appropriate mixture of frame pricing throughout the optical. Then we are able to compare the demographic breakdown by gender and children's frames to ensure the board mix mirrors that of our patient base. Finally, we’ll compare the total number of ophthalmics versus sunglasses to make sure we have an appropriate mix of the two.

To properly use this spreadsheet, we’ll start at the top, under the frame board analysis section. In the first section labeled frame board analysis, enter the corresponding brand-specific information, beginning with the brand or collection, such as Ray-Ban, Prada, or State Optical. 

In the next column, column B, enter the total number of frames currently on hand for that specific brand. You’ll notice here the colors start to change on this spreadsheet. We’ll explain that as we get a little bit further in, but for now let's just continue entering in our information. 

Utilizing your EHR or inventory management system, enter the total number of frames sold for that particular brand in column C.

Next, in column D, enter the required minimum for that specific brand, if applicable. This is extremely important to properly control how much we spend on our frames in any given month. If we start to fall too far below the required board minimums, when your frame rep comes in, you’ll have to play catch up financially by buying more frames than what you can potentially afford or should be spending on that brand in any given month. 

Then move over to the time in inventory column, which is a column I and put how long each frame has been in your practice, on your frame boards, in a number of months. 

After completing steps one through five, you will notice column E, labeled turn rate, will be populated with value and color-coded, either green or red, depending on if the turn rate for that particular brand is above or below 2.75, which is our recommended turn rate benchmark. 

Turn rate can be defined as the number of times an individual brand is turned over or sold in any given time period compared to the total number that we have on hand in our inventory.  

In this case, you can see that Ray-Ban has a turn rate of 2.4. This is because we have sold 24 in the past 12 months and we have 10 on hand. So, if we take 24 divided by the number that we have on hand in inventory, which is 10, we get our 2.4 which helps us assess if this frame line is selling enough to warrant how many we currently have on hand.