Filetype Excel XLSX | Posted on 08 Aug 2022 | 4 years ago
Authentication
digital resources
364x Filetype XLSX File size 0.02 MB Source: www.businessguide.ebrd.com
Sheet 1: Inventory by product
| Instructions | |||||
| Note that highlighted numbers are used as an example and should be replaced by your business's financial data. | |||||
| *TBD currency localisation | |||||
| Inventory reconciliation by product group | |||||
| Finished goods inventory reconciliation by product group | |||||
| *€m | Product A | Product B | Product C | Product D | Total |
| Balance as at 31/12/2020 | 2,000 | 9,000 | 600 | 650 | 12,250 |
| 2021 purchases | 4,000 | 1,000 | 5,000 | 1,500 | 11,500 |
| 2021 COGS | (5,000) | (9,000) | (4,000) | (1,000) | (19,000) |
| Balance as at 31/12/2021 | 1,000 | 1,000 | 1,600 | 1,150 | 4,750 |
| Percentage of total (%) | 21.1% | 21.1% | 33.7% | 24.2% | 100.0% |
| Inventory turnover | 3.3 | 1.8 | 3.6 | 1.1 | 2.2 |
| Days inventory outstanding | 109.5 | 202.8 | 100.4 | 328.5 | 163.3 |
| Inventory turnover can be calculated in three ways: | |||||
| 1) Cost of goods sold (COGS)÷inventory | |||||
| Most accurate because inventory is carried at cost and not market value. | |||||
| 2) Sales revenue÷inventory | |||||
| Not as accurate as COGS but may be more appropriate for comparability as many businesses use sales revenue and not COGS in their financial reports. | |||||
| 3) Use average inventory for businesses that have seasonable sales (add yearly beginning and ending stock figures and divide by 2). | |||||
| Days inventory outstanding is calculated as 365÷inventory turnover ratio. | |||||
| Instructions | ||||||
| Note that highlighted numbers are used as an example and should be replaced by your business's financial data. | ||||||
| *TBD currency localisation | ||||||
| Inventory analysis | ||||||
| Inventory analysis | ||||||
| *€'000 | 2017 | 2018 | 2019 | 2020 | 2021 | 6 months ended 30 Jun 2021 |
| Actual inventory | 3,200 | 4,800 | 3,400 | 3,672 | 3,601 | 3,602 |
| Average inventory | 4,000 | 4,100 | 3,536 | 3,637 | 3,602 | |
| Cost of goods sold | 14,607 | 14,085 | 14,100 | 15,034 | 18,621 | 10,016 |
| Inventory days outstanding | 104 | 106 | 86 | 71 | 66 | |
| Remember that if you do not have a full year period (i.e., 365 days) you need to alter the number of days in the sales calculation. | ||||||
| 2017 | 2018 | 2019 | 2020 | 2021 | 6 months ended 30 Jun 2021 | |
| Average inventory | 4000 | 4100 | 3536 | 3636.5 | 3601.5 | |
| Inventory days outstanding | 103.7 | 106.1 | 85.8 | 71.3 | 65.6 | |
| *Inventory days could be graphed against average inventory or COGS | ||||||
| Inventory turnover can be calculated in three ways: | ||||||
| 1) Cost of goods sold (COGS)÷inventory | ||||||
| Most accurate because inventory is carried at cost and not market value. | ||||||
| 2) Sales revenue inventory | ||||||
| Not as accurate as COGS but may be more appropriate for comparability as many financial reporting business use sales revenue and not COGS. | ||||||
| 3) Use average inventory for businesses that have seasonable sales (add yearly beginning and ending stock figures and divide by 2). | ||||||
| Days inventory outstanding is calculated as 365÷inventory turnover ratio. | ||||||
no reviews yet
Please Login to review.
