Transcripts
1. Introduction: If you are a small
retail business and have questions on how to manage
your inventory effectively. You are in the right place. This class will teach you
step-by-step how to take control of your inventory and make your
business convertible. You shouldn't have
some advantage, business knowledge
or technical skills. The content of the class
is suitable for beginners, as well as for people who have some experience
in this field, but want to expand their
competency. In this class. I will be talking
about the open two by system and its importance
when in a retail business. Then I will begin
with easy to follow explanations of the foundations
of open two by system. I will reveal to you the main
simple formulas that you can use to track and manage
your inventory properly. Once I cover the theory part, I will go deeper and give
you some examples of how to apply this inventory management
system in real life. The next step will be to
integrate the open two by system into Google Sheets
and create a calculator, which will help you to plan
your inventory purchases. Of course, this class
will offer you a project that will help you to put into action everything you've
learned in the class. When you complete the class, you will know how to calculate the most appropriate
quantity inventory for your small retail
business and how to integrate this knowledge
into Google Sheets. This way, you will
be able to create, assemble and very effective
inventory management system for your small retail business that works accurately
and it's free. It will inform you how much inventory you need to purchase to satisfy client's needs and generate the profit
you're planning. By having this information, you will make the right
business decisions and your small retail business
will go to the next level.
2. Open-To-Buy System - Explained Simply: One of the main reasons
why retailers go out of businesses because they don't manage their inventory properly. The biggest
contributing factor to retail mismanagement is the lack of an open two by system. If you want your
online retail business to do well and keep existing, you should be aware
of what open to bias and how to apply it
in real business. I personally define it as
a management tool that allows retailers to manage
their inventory investments, plan purchases, and prepare
budgets effectively. We can say that open to buy as essentially the inventory
quantity that your business needs to purchase
after comparing how much inventory is needed
and how much is available. It's the amount of inventory that one retail
stores should buy to meet customer demand while maintaining a positive cashflow. I want to mention
three main features of open to buy planning. First, open to buy
is a budget tool. It is future-oriented
and helps retailers make decisions on how
much inventory to buy. Second, open to buy as
a financial instrument, it is calculated
in terms of cash. Third, open to buy can
work on any level. Depending on the size
of the retail business. You can track an individual
items inventory at the company department
classification or sub-classification level. To help you understand
the main idea behind this inventory management system and how it can make your
retail business profitable. I will explain to
you the concept of open to buy
simply and briefly. To generate satisfying profit, you as a retailer needs
to have the right type and the right quantity
of inventory available. Let's suppose your online
store sells jewelry pieces. If your clients prefer
to buy silver earrings, but you have only gold
earrings and stock, you will miss sales, reduce cashflow, and
generate less profit. Another possible scenario is to have silver
earrings in stock, but the quantity is not enough to meet the
client's needs. In this case, you won't
generate the profit that you could generate if you have the right quantity
of silver earrings, you will also miss sales
and slow down the cashflow. Of course, this will have a negative impact on
your business profit. Too many silver earrings and
stock is also a bad option. You will satisfy
your client's needs, but you won't meet your business needs because you will have too many silver earrings
that you can't realize. What's more. The revenue from all silver
earnings sold may not cover the total cost of all silver earrings
that you purchased. All this will have
a negative impact on your cash flow and profit. As you can see, the wrong
inventory management could cost a lot of your
retail online business. But if you understand
the open two by system and apply it to
your retail business, it will generate the
profit you expect. The open to buy plan
can be prepared and maintained in a
spreadsheet or by purchasing one of the several
retail software packages available that contain
open to BI programs. For this course, I will
use the spreadsheets because they are a
perfect solution for small retail businesses. Spreadsheets don't require
any investments in term of cash and advantage
technical skills as well. After explaining the key role of open to buy for our
retail business, we can move forward
towards next video.
3. Open-To-Buy System - Benefits: Open to buy planning helps any retail business to be
profitable and convertible. In this video, I will mention some main things
that I personally see as the biggest advantages of applying this inventory
management system. By plugging the open to buy tool into your retail business, you can reach optimal
inventory levels. You will have a picture of
your inventory in stock. And based on this information, you will be able to make the
right decisions regarding inventory purchases.
Open to buy. Inventory management
system prevents you from having too much or
too little inventory. It shows you how much
you need to purchase to reach your goals and realize
your business plans. Using this tool, you can track your inventory at anytime
and have it under control. By having the optimal
inventory quantity. You avoid wasting money
on unnecessary inventory. It also prevents you from unsatisfied customers
as a result of not having enough inventory. Open to buy ensures that
your retail business has the right amount of
inventory to satisfy demand. And ensure your business
financial goals. Having too much inventory or the wrong products will
reduce your business margins. On the other hand, having too little inventory can
result in lost sales. Open to buy will ensure
that your retail business has the right inventory in
stock at the right time, resulting in high
revenues and margins. You can do, you're open to buy planning on a weekly
or monthly basis. You have the freedom and
flexibility to change your plant inventory on
any given week or month. Imagine that you sell
winter accessories. On the sixth of March, you are watching the
weather forecast and it says that April will be
an unexpected cold month. It will snow on the
temperature will be under 0. Here you can apply the
open to buy planning. And based on the
previous cold months, you can calculate how many hats, gloves, and scarves you need to purchase to realize
your planned sales. As you can see, even
though you didn't expect that you will sell
winter accessories in April. Open to buy planning
allows you to manage your inventory properly
and effectively. After revealing the benefits
of open two by planning, I believe that I have
convinced you of the importance of this
inventory management system.
4. Main Terms And Formula Relating To Open-To-Buy System: To calculate the open
to buy inventory, you should use estimated
or plain numbers. You can either use
historical numbers based on past transactions or numbers that you expect to
see in the future. But before I show you how to calculate the open
to buy inventory, I would like to clarify
some terms briefly. The first term is planned
beginning of month inventory. It shows how much
inventory in terms of cash you expect to have at
the beginning of the month. When we are talking about plan beginning of month inventory, we should also mentioned
planned end of month inventory. This term is used to describe balance inventory and cash
term at the end of the month. End of month inventory
for the current month becomes the beginning of month inventory for
the next month. The next term is planned sales. This term relates
to the amount you forecast turn through
sales during the month. Planned markdowns are also an essential term that
deserves our attention. We accept that any reduction
in selling prices, some type of markdown, logically, planned markdowns are all expected reductions
in the selling price. These price reductions can be in the form of discounts
or coupons. We can distinguish three
types of markdowns, temporary, permanent,
and competitive. I describe temporary
markdowns as price reductions that create a sense of urgency
and customers. The best examples of
temporary markdowns are limited time sales and buy
one, get one promotions. The most common
permanent markdowns or end of season clearances, or discounts for damaged goods. Competitive markdowns are price reductions
through price matching. Customers must have
evidence that a competitor sells the same item and
they get a discount. Last but not least, we need to include the term
plant open to buy inventory. It's the inventory and cash terms you need to purchase
at the end of the month. After this brief
term introduction, it's time to see how you can calculate the open
to buy inventory. All you need to do is sum
up the planned sales, planned markdowns, and planned
end of month inventory. From the total amount. You need to subtract the plan beginning of the
month inventory. You see how simple
this formulas. At the same time,
it helps you to manage your inventory
properly and effectively. In the next videos, we'll go through a
few simple examples to see how exactly
this formula works.
5. How To Calculate Open-To-Buy Inventory At Cost: In the previous two videos, I showed you how to
calculate open to buy inventory at retail price. But it would make more sense if you could convert
your open to buy inventory at retail price into open to buy inventory at a cost. And this video is exactly
about how to do that. Knowing the cost of the
open to buy inventory, you'll be able to plan your expenses and know how
much you need to spend to realize the sales
you are planning and generate the
revenue are expecting. You will also be able to look
for the best supplier who offers the items you
are looking for at the price you got in
your open to buy plant. Let's take a look at
the following example. They open to buy inventory at retail price for
March as $9 thousand. This amount includes the price
at which I will purchase the silver bracelets and some percentage
over these costs, which I will use
to cover some of the overheads and
generate some profit. This percentage is known
as the initial markup. We can define it as the
difference between the price at which a retailer
purchases their inventory, the retail sale at which a retailer sells it
to their customers. The initial markup
that I uses 70%. The formula that I will use to calculate my open to buy at a cost for March is the one
that most retailers use. The retail price equals the initial costs divided by the initial markups
subtracted from one. Using simple math, I could transform this
formula and use it to calculate the cost
of the inventory that I should buy according to
the open to buy plant. The open to buy at a cost
equals the open to buy retail price multiplied by the initial markups
subtracted from one. I have all numbers I need
to calculate my open to buy inventory at a cost
for March 1st. I convert the
markup percent into a decimal and
subtract it from one. Then I multiply $9 thousand
open to buy inventory by 0.25 and get a
result of $2250. This result shows
that I should spend $2250 to purchase the quantity
of 193 silver bracelets. And if the vendor I am
working with couldn't deliver me this quantity of silver bracelet at this price, I should find a new supplier who could meet my requirements. As you can see, knowing the cost of the open to
buy inventory is more than essential to make
the right decision and achieve the goals that
you've set for your business. If I choose not to calculate the open to buy inventory at a cost, I could purchase the
silver bracelets and a too high price. The markup will go
down and I will generate less profit
than expected.
6. Example 1: There are different approaches
you can take to apply an open to buy plan for
your retail business. I will show you how this
inventory management system could be applied to a
small retail business. For this purpose, we will take a look of
how I am plugging my numbers into the formula I explained in the previous video. The aim of this
video is to show you how the open to
biplane works when the available inventory
at the beginning of the month is less
than the expected sales. In this case, you need to
take into account that the calculated open
to biplane should be realized during
the current period. This means you need to purchase
the calculated open to buy inventory of calculated
during the current period. Only this way you can realize the sales
you've already planned. Let's see how this looks like. I have a jewelry online store and I have calculated that I have $3 thousand
silver bracelets and stock at the
beginning of March. Based on the last March, I have predicted $7 thousand
in sales for the month. I have also forecasted
$120 markdowns. I expect that I will
have $9 thousand in end of month inventory in
preparation for April. Again, based on the information I have from the last year. The first step that I take is to calculate the open
to buy purchase. I need to see how much
inventory I need to buy to make all plant cells and have the right quantity inventory in stock at the
end of the month. As you can remember, I sum up the plan
sales markdowns and available inventory
at the end of March. And then I subtract the
available inventory at the beginning of March, I get a result of $13,120. This is the open
to buy inventory. Explained another way. This is the total retail
price of the inventory I need to purchase to meet my selling expectations in March. The second step is to calculate the units
I need to purchase. In other words, I
need to calculate how many silver
bracelets I need to by knowing the price per unit, I divide the total open to buy amount by the retail
price per unit. And I get how many bracelets I need to purchase to realize all planned sales and have the plant inventory and
stock by the end of March. The retail price of one
silver bracelet is $68. I divide $13,120 by $68, and I get a result of 193. This number shows I need
to purchase 193 bracelets. Purchasing this quantity
of inventory in March, I will be able to make
$7 thousand cells, $120 markdowns, and have available inventory with
$9 total retail prices. The third step is to
decide when and how I will purchase the
open to buy inventory. Based on the information above, I've decided to make
two separate purchases, one at the beginning of March and one at the end of March. The purchase I will make
at the beginning of the month will be
related to the plan, $7 thousand sales
and $120 markdowns. The second purchase will
be related to the plan, $9 thousand end of
month inventory. Let's take a deeper look. I have available inventory
with a total retail price of $3 thousand to be
able to realize $7 thousand sales
and $120 markdowns, I need inventory with a total
retail price of $4,120. I divide $4,120 by $68, and I get a result
of 61 bracelets. This is the number
of silver bracelets I need to buy to
realize the sales, I'm planning in March. Once I realized
the planned sales, I will have enough money to
purchase the inventory with a total retail price
of $9 thousand that I am planning to have
available at the end of March. And of course at the
beginning of April, I could purchase the
whole open to buy inventory at once at
the beginning of March. But this would be a good
decision Only in case I have $13,120 available
in my bank account. In my case, it would be more reasonable to make two
separate inventory purchases. It is up to you how you would act depending on your
situation and needs.
7. Example 2: In this video, I'll show you how they open to
biplane works when the available inventory
at the beginning of the month is more
than the expected sales. Here you need to take into account that the calculated open to biplane can be realized in the end of the
current period. This means you can
purchase the whole calculated open to buy inventory in the end
of the current period. This variant is possible
because the beginning of month inventory covers the sales you're planning for
the current period. Let's see how this looks like. Let's continue with the
example of the previous video. Now I am going to calculate the open to buy inventory and April, the end of month inventory and March becomes beginning
of month inventory. I predict $7,600 in sales
and $100 markdowns. And April. I also expect I will have $4 thousand end of
month inventory and preparation from a based on the information I
have from the last year. The first calculation
that I will make is the open to buy purchase
at a retail price. Then I can see how
much inventory I need to buy to have
the right quantity, inventory and stock at
the end of the month. I sum up the plant
sales markdowns and available inventory
at the end of the March. And then I subtract the available inventory at
the beginning of March, I get a result of $2700. It's the total retail
price of the inventory I need to purchase to meet my business
expectations in April. Now I should calculate how
many silver bracelets I need to by knowing the
price per unit, I should divide the
open to buy him out by the retail
price per unit. I get how many bracelets
I need to purchase in April to meet my
business plans for April. The retail price of one
silver bracelet is $68. I divide $2700 by $68 and
I get a result of 40. The result shows I need
to purchase 40 bracelets. Purchasing this quantity
of inventory in April, I will have available
inventory with $2700 total retail price
at the end of April. Now I should decide
when and how I will purchase the open
to buy inventory. When I look at the numbers, I see that the inventory in stock is more than the
sales I am planning. This means that I am free
to purchase the open to buy inventory
anytime during April. Since the available
inventory can meet all expected
sales during April, I can purchase the whole open to buy inventory at the
end of the month. Exactly. This is the
decision I will make. I believe it is the most
reasonable in this case.
8. Open-To-Buy Calculator Using Google Sheets - Quick View: Once you are aware
of open to buy planning and its importance, you are ready to integrate this inventory management
system into Google Sheets. I will walk you through the easy process of creating an open to buy inventory management
system using Google Sheets. But before we create an open to buy calculator using
Google Sheets, I would like to
mention its benefits for your small retail business. I began with the fact
that the open to buy calculator created with Google
Sheets is simple and free. You don't need to have
some advantage business or software knowledge, and you don't need
to pay any fees. Although it's simple,
this tool is effective, powerful, and allows you to have a clear picture of the
inventory you need to purchase. Only this way. You
could have control of your inventory and provide an adequate level
of stock on hand for the volume of
sales being generated. You should be aware
that this open to buy calculator doesn't tell
you which product to sell and doesn't provide
you information about competitors or any
other information that can impact your purchases. This calculator is to show you how much inventory from a
specific product you need to purchase to realize
the sales you've planned and generate
the profit you want. To be more clear, I'll give you an example. I expect to generate $7,600
sales of silver bracelets. And April, the Google
sheet that you see presents information
related only to the silver bracelets
I am selling. It doesn't show any information about the rest
pieces of jewelry. Logically, the column
for April includes information only about the silver bracelets
from my inventory. The calculator
includes 12 months. For each month that
calculates the open to buy inventory at retail price. Open to buy inventory at a cost, and shows the quantity
that should be purchased. If I want to have the
optimal quantity of all pieces of jewelry
that I offer, I need to create such a sheet
for each jewelry types. Pay attention that this
calculator is suitable for small retailers
with up to 30 products. Retailers with a wide variety of products should be
open to buy software. But in this tutorial, I focus on small
retail businesses. Therefore, I show how to make an open to biplane
using Google sheet. The first four rows
from the calculator include planned sales markdowns, beginning inventory,
and ending inventory. In these routes, you enter your planned
or expected amounts. Once you enter the plan ending inventory for some
of the months, the table adds automatically. This amount has a
beginning inventory for the following month. The next row shows the
percentage you apply over the cost of your items
to get the retail price. Under the markup, you should enter the retail price
of your product. These two rows are necessary to calculate the open to buy at a cost and the actual quantity
that you need to purchase. This simple and effective open
to buy system is based on the calculations that we were talking about in the
previous videos. When I double-click
on some of the open to buy at a
retail price sell. I see that the amount in the cell equals the
plant sales plus planned markdowns
plus plan ending inventory minus
beginning inventory. When I double-click
on some of the open to buy at a cost cell, I see that the amount
equals the open to buy at retail price multiplied by the initial markup
subtracted from one. Now, I will double-click on some of the open to
buy in unit cell. The amount in this
cell equals the open to buy at a
retail price for the selected month divided by
the retail price per unit. Using this symbol and effective
open to buy calculator, you will be able to manage your inventory properly
and this will help your small retail business goes further and become
more profitable.
9. Open-To-Buy Calculator Using Google Sheets - How To Create It: You shouldn't be a
Google Sheets expert to create this open
to buy calculator, you just need to follow a few simple tips
that I will give you. At the end of the video, you will be able to
create a calculator for inventory management
that will help your small retail business be more organized and successful. First, you should merge some
cells from the first row. Select the cells you
want and go to Format. Then move the mouse to merge cells and select
Merge horizontally. Once done, just write the name of the table open
to buy calculator. Second, you should
create 13 columns. The first one is
called indicators, and the rest are
for all 12 months. In the indicators column. You need to add all
indicators from the formulas I was talking
about in the previous videos. The first four indicators relate to what you are planning. The next two
indicators relate to the percentage you've
decided to apply over the cost of the
items you sell and the retail price at which your
customers buy your items. The next three indicators are the results who
are looking for. They will be calculated based on the formulas I was
talking about before. The colorful rows you see
are not so important. But if you want to add them, you just need to merge the cells and then choose a
background color. More important is how to import the formulas you need
into this table. Click on the Open to buy a retailer price sell
in some of the columns. And type equals, which means
that you create a function. Here. You should integrate
the formula for open to buy inventory at retail price. This means you should sum up the planned sales for
the selected month, planned markdowns
for the same period and the plant ending inventory. Then from the result, you should subtract the
beginning inventory. To apply this function
for all 12 months. Select the cell
plague on right under the corner and drag until
you reach the last month. Once done, click on
the Open to buy at a cost cell in the same column
and type the sine equals. The formula includes
the amount in the open to buy at
a retail price, sell for the selected period divided by the initial
market subtracted from one. To apply the function
for all 12 months, click on the right under
corner and drag the mouse. The last step is to
integrate the function that calculates the
open to buy in units. You need to divide
the amount in open to buy at a retail
price self with a selected month by the retail price per
unit for the same month. Then apply the function
to all 12 months. Click the right under corner of the cell and drag until
you reach the last month. You are ready to enter
your business information and manage your inventory.
10. Class Project: Your class project
is designed to help you apply the skills you've
learned in this class. Once you complete the project, you will have the
skills to bring your small retail business
to the next level. Jim own small online
t-shirts business. He opens his online store
from April to September. This year, he has 87
t-shirts left from the last year at a total
retail price of $3,393. Based on his data
from April last year, he expects to make $9,750
sales in April this year. He planned to have $5,850 inventory available
at the end of April. The initial markup that
Jim has always used as seventy-five percent helped him to calculate with the
help of Google Sheets, how many t-shirts he should
purchase and the cost of the quantity he should buy to meet his expectations in April. To complete the project, make the following steps. First, write down the open to buy at a
retail price formula. Second, write down the
open to buy asked formula. Third, write down the open
to buy in units Formula. Fourth, open Google Sheets and create a file called
open to buy calculator. Fifth, create a column
with all indicators that are included
in the formulas you've already written down. Six, create a column
called numbers. Seven. Enter all the information you
have about Jin's business. Integrate the open to buy at a retail price formula
in the cell where the column numbers and the route open to buy at a retail
price cross each other. Integrate the open to buy at a cost formula in the cell where the column numbers and the route open to buy at a cost
cross each other. Ten, integrate the open to buy in units formula in the cell where the column numbers and the route open to buy in
units cross each other. Make a screenshot
of the sheet to see if you've calculated
the right quantity of inventory that Jim
should purchase in order to meet his
expectations in April.