# ms_excel_2007_winxp by mxplatform01

VIEWS: 2 PAGES: 2

QA test 1.6.2::182

• 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

Test random file
Composition of Ending Inventory Balances

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

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

325,000

\$600,000
\$450,000

\$400,000
\$375,000

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

```
To top