Fundamentals of Inventory Management Using Microsoft Excel

Explore the basics of business accounting and its role in inventory management, highlighting how money flows, expenditure categories, and the language of business drive manufacturing decisions.

Explore how inventory management ties to business accounting by examining the balance sheet and income statement, and apply the fundamental equation assets equal liabilities plus owner's equity.

Explore a hypothetical balance sheet with assets, liabilities, and stockholders’ equity, dated March 31, 2024, using an attached pdf for hands-on excel-based financial accounting practice.

Analyze the asset side of a balance sheet, focusing on liquidity, inventory, and accounts receivable, and introduce financial ratios to compare assets with industry averages.

Analyze balance sheets by confirming assets equal liabilities plus owners' equity, focusing on accounts payable and net working capital. Explore optimization levers like payment terms, discounts, and inventory funding.

Explore how the income statement transforms revenues into profits or losses, highlighting top line revenue, bottom line profits, and expense categories like cost of goods sold, materials, labor, and depreciation.

Explore the income statement, a profit and loss statement and one of the four main financial statements, tracing net revenue, expenses, and the bottom line of net income.

Calculate cost of goods sold as starting inventory plus purchases minus ending inventory to derive cogs and gross margin from net revenue on the income statement, noting obsolescence.

The inventory turnover ratio measures how often inventory is sold and replaced, calculated as cost of goods sold divided by average inventory (beginning plus ending divided by two).

Explore days of inventory outstanding, a key measure related to inventory turnover. Calculate as average inventory divided by cost of goods sold, times 365; in the example, about 607 days.

Explore inventory valuation using fifo, lifo, and weighted average cost to determine unsold inventory value and its impact on taxes, inflation, and financial decisions.

Explore a simple valuation spreadsheet to experiment with changing values across three valuation methods, using the downloadable resource for hands-on practice.

Apply key accounting concepts in inventory management with an Excel practice exercise. Downloadable workbook includes two tabs, practice exercise and the answer key, with comments on how answers are derived.

Walk through solving the end-of-year balance sheet, calculating assets, liabilities, and owner's equity; compute purchases, cost of goods sold, inventory turnover, and gross margin relative to revenue.

Explore demand forecasting for inventory management using excel, bridging statistics concepts with practical data analysis. Learn to use historical data and models in excel to estimate future purchases.

Translate customer demand into raw material requirements and explore forecasting methods, focusing on time series analysis and causal analysis to optimize inventory planning for forecast accuracy.

Accurate demand forecasting drives cost efficiency by reducing excess inventory and holding costs, while protecting against obsolescence and stockouts. Analyzing data improves customer service and informs long-term strategic decisions.

Analyze a forecast case study using historical monthly demand data to plan raw materials and production for a wiper blade business, then prepare to apply predictive analytics in Microsoft Excel.

Analyze stable demand in Excel using line charts and histograms to explore time-series data, noting no clear trend or seasonality, and compute average, min/max, and dispersion for simple forecasting.

Explore the normal distribution and its use in inventory forecasting, linking histograms, mean, and standard deviation to infer population behavior and predict future demand.

Learn how the normal distribution models data in inventory management, interpret mu and sigma, and use Normdist and Norm.inv in Excel to forecast demand from sample data.

Learn to forecast demand in excel using a simple normal distribution model, computing the sample mean and standard deviation, and estimating the percentage below 48,000 with norm.dist.

Apply simple demand forecasting to inventory management using normal distribution assumptions. Compute mu (mean) and sigma in Excel, model the distribution, and verify assumptions with histograms before forecasting.

Cycle service level measures the probability of no stockouts during an order cycle, guiding both past performance evaluation and future inventory planning using a normal distribution model.

Perform predictive analytics on quarterly sales data from four product lines to forecast next year's demand, noting seasonal patterns and steady growth.

Practice simple demand forecasting in the fundamentals of inventory management using Microsoft Excel, building a histogram and calculating weekly demand’s average and standard deviation, with an answer key.

Explore time series analysis to forecast sales data by analyzing baseline, trend, seasonality, and random variation, and compare static, cumulative, and moving-average approaches for inventory planning.

analyze time series in Excel to establish a baseline using mean and standard deviation, test for seasonality, and forecast quarterly dryer sales with an expected range of 718 to 902.

Explore how to use Excel's data analysis toolpak to generate descriptive statistics—mean, standard deviation, min, max, median, skewness, and kurtosis—for baseline inventory analysis.

Explore Excel data analysis add-ons, including the Analysis ToolPak and Solver, and learn to enable them. Apply descriptive statistics, regression, and exponential smoothing within predictive analytics for time series.

Analyze seasonality in ice maker sales by creating a line chart, computing annual averages and seasonal indices, deseasonalizing data, and generating a seasonal forecast from the mean using the seasonal indices.

Learn to create upper and lower forecast estimates by multiplying the mean by the seasonality index, producing quarter-specific ranges that capture uncertainty.

Explore the Cartesian coordinate system and line graphs, define y = a + b x, identify the y-intercept and slope, and relate to time series trend analysis and linear regression.

Use linear trend analysis in Excel to fit a regression line to signature stove sales data and forecast next year's quarterly sales with y = 119.41 x + 505.13.

Learn to use Excel's regression analysis tool to select linear and nonlinear models, interpret R-squared and p-values, and identify independent and dependent variables for forecasting.

Learn to combine growth, seasonality, and trend by deseasonalizing data, computing seasonal indices, and forecasting four quarters with a linear model in Excel.

Review forecasting methods from baseline data to seasonal and trend analyses. Apply deseasonalizing, mean and standard deviation, and regression with seasonal indices for forecasts.

Explore the total cost of inventory by connecting financial concepts, analytical models, trade-offs, and predictive models. See how the economic order quantity informs inventory decisions.

Learn how the total cost of inventory per unit arises from purchase, ordering, holding, and shortage costs to reveal the optimal inventory level between excess and shortage costs.

Identify and quantify carrying costs by analyzing financing costs and operational costs for inventory, using the weighted average cost of capital and return on equity to estimate annual holding costs.

Explore total inventory cost through a mini case study in Excel, comparing order quantities, carrying costs, and unit prices to identify the lowest total cost scenario.

Compare total cost calculations in Excel across two inventory scenarios, including purchase costs, ordering costs, carrying costs, and shortage costs, showing how discounts may be offset by carrying costs.

Explore total cost calculations in Excel by comparing prices and order sizes, using Goal Seek to identify break-even costs and optimize holding costs and discounts.

Explore how EOQ minimizes total inventory costs by balancing fixed ordering costs and linear holding costs, and find the optimal order quantity via its formula and example.

Explore total cost calculations in Excel using the EOQ model to find the optimal order quantity that minimizes annual inventory costs, comparing scenarios and using what-if goal seek.

Introduce further learning in inventory management, including a glossary, A, B, C classification, and continuous and periodic control policies, with connections to supply chain and capacity planning and analysis.

Explore key inventory management terms through a downloadable glossary, including ABC analysis, accounts payable/receivable, bill of materials, dead stock, economic order quantity, and kanban, with guidance for further study.

Explore key inventory concepts such as SKUs, just-in-time and vendor-managed inventory, plus periodic and continuous review systems. Learn through examples of stock planning, reorder points, min-max policies, and consumables handling.

Explore inventory classification with ABC analysis and Pareto principles, distinguishing value-based, volume-based, and criticality-based approaches to focus on the significant few SKUs that impact total inventory cost in Excel.

Perform an ABC analysis in Excel using annual demand to rank parts, identify top 20% A parts, middle 30% B parts, and bottom 50% C parts for prioritization.

Step through a hands-on abc analysis exercise in excel, using total annual value and total demand to classify inventory items.

Explore continuous inventory control policies that trigger automatic ordering at the reorder points with real-time data, and contrast them with periodic policies affected by stochastic demand and stockouts.

The periodic inventory control policy counts inventory at fixed intervals and orders up to a set level, offering easy implementation for sea level items and small businesses with stable demand.

Explore how inventory management fits into the broader supply chain and learn related fields like capacity planning, procurement, analytics, and quality management, including safety stock and cycle service level.

Review practical tools, terminology, and analytical approaches from fundamentals of inventory management for immediate workplace application, with lifetime access to materials and Udemy Q&A support.

Uncover a bonus lecture with a downloadable resource listing Udemy classes, including fundamentals of capacity planning and analysis, introduction to supply chain analytics using Microsoft Excel, and business finance.