# ms_excel_2007_winxp by mxplatform01

VIEWS: 1 PAGES: 2

• pg 1
```									[Company Name]
Inventory/Cost of Goods Sold Analysis
[Date]
Dark gray cells will be calculated for you. You do not need to enter anything into them.

[Product A]           [Product B]         [Product C]     [Product D]     Total
Inventory unit analysis:
Number of units in inventory—beginning of period                   1,200                  1,000           1,200           1,300           4,700
Production                                                           700                    800             600             600           2,700
Units available for sale                                           1,900                  1,800           1,800           1,900           7,400
Units sold                                                           800                    600             500             750           2,650
Number of units in inventory—end of period                         1,100                  1,200           1,300           1,150           4,750

Cost of goods sold analysis:
Beginning inventory                                            \$800,000             \$750,000           \$900,000       \$1,200,000   \$3,650,000
Add: purchases                                                   400,000              400,000            450,000         600,000    1,850,000
Cost of goods available for sale                               1,200,000            1,150,000          1,350,000       1,800,000    5,500,000
Less: ending inventory                                           770,000              900,000            925,000       1,000,000    3,595,000
Total cost of goods sold                                       \$430,000             \$250,000           \$425,000         \$800,000   \$1,905,000

Inventory costing:
Cost per unit—beginning of period                               \$666.67                 \$750.00         \$750.00         \$923.08      \$776.60
Cost per unit—end of period                                     \$700.00                 \$750.00         \$711.54         \$869.57      \$756.84
Variance                                                        (\$33.33)                  \$0.00          \$38.46          \$53.51       \$19.75

Weighted average cost (Cost of goods available
for sale/Units available for sale)                               \$631.58                 \$638.89         \$750.00         \$947.37      \$743.24

Ending inventory breakdown:
Value of ending inventory (from above)                          \$770,000             \$900,000           \$925,000      \$1,000,000   \$3,595,000
Finished goods inventory                                        \$400,000             \$375,000           \$450,000        \$600,000   \$1,825,000
Work in progress                                                 300,000              325,000            350,000         300,000    1,275,000
Raw materials                                                     70,000              200,000            125,000         100,000      495,000
Total ending inventory                                          \$770,000             \$900,000           \$925,000      \$1,000,000   \$3,595,000
Composition of Ending Inventory Balances

Raw materials

70,000                          125,000         100,000
200,000                                    Work in progress

Finished goods inventory
300,000
300,000
350,000

325,000

\$600,000
\$450,000

\$400,000
\$375,000

[Product A]     [Product B]    [Product C]     [Product D]

```
To top