In: Finance
Projecting revenues, cost of goods sold, and inventory
use the following data for Walgreens in years 11 and 12 to project revenues, cost of goods sold, and inventory for year +1. Assume that Walgreen's year +1 revenue growth rate, gross profit margin and inventory turnover will be identical to year 12. Project the average inventory balance in year+1 and use it to compute the implied ending inventory balance.
Walgreens (data in millions) Year 11 Year 12
Sales revenues $53,762 $59,034
Cost of goods sold $38,518 $42,391
Ending inventory $ 6,791 $ 7,249
Calculating Sales revenue growth rate and projecting sales revenue for year +1
Sales growth rate = (Sales in year 12 / Sales in year 11) - 1 = (59034 / 53762) - 1 = 1.098061 - 1 = 9.8061% = 9.81%
Sales revenue for year +1 = Sales for year 12 x (1 + growth rate) = 59034 (1 + 9.81%) = 59034 x 1.0981 = 64825.23
Sales revenue for year +1 = 64825.23
Projecting cost of goods sold for year +1
Gross profit for year 12 = Sales revenue - Cost of goods sold = 59034 - 42391 = 16643
Gross profit margin for year 12 = Gross profit margin for year +1 = Gross profit / Sales = (16643 / 59034)
Gross profit for year + 1 = (Gross profit margin for year +1) x (sales for year +1) = (16643/59034) x 64825.23 = 18275.68
Cost of goods sold for year +1 = Sales - Gross profit = 64825.23 - 18275.68 = 46549.55
Cost of goods sold for year +1 = 46549.55
Projecting average inventory for year + 1
Average inventory for year 12 = (ending inventory for year 11 + Ending inventory for year 12) / 2 = (6791 + 7249) / 2 = 7020
We know that
Inventory turnover ratio for year 12 = inventory turnover ratio for year + 1
(Cost of goods sold 2012 / Average inventory of 2012) = (Cost of goods sold for year + 1 / Average inventory for year + 1)
( 42391 / 7020) = (46549.55 / Average inventory for year + 1)
Average inventory for year + 1 = (46549.55 x 7020) / 42391 = 7708.66
Average inventory for year +1 = 7708.66
Computing implied ending inventory for year + 1
Average inventory for year + 1 = (ending inventory for year 12 + Ending inventory for year +1) / 2
7708.66 = (7249 + + Ending inventory for year +1) / 2
7708.66 x 2 = 7249 + Ending inventory for year +1
Ending Inventory for year + 1 = 7708.66 x 2 - 7249 = 8168.32
Ending inventory for year + 1 = 8168.32