Microsoft Office Tutorials and References

In Depth Information

Cases and Places

EX 157

•
2: Create a Proﬁ t Potential Worksheet

You work part-time for Doze-Now, a retailer of sleep-related products. Your manager wants to know

the proﬁ t potential of their inventory based on the categories of inventory in Table 2–12. Table 2–12

contains the format of the desired report. The required formulas are shown in Table 2–13. Use the

concepts and techniques developed in this project to create and format the worksheet. Submit a

printout of the values version and formulas version of the worksheet. The company just received a

shipment of 175 additional comforters and 273 items of sleepwear. Update the appropriate cells in the

Units on Hand column.

Table 2–12 Doze-Now Proﬁ t Potential Data and Worksheet Layout

Item

Units on

Hand

Average

Unit Cost

Total

Cost

Average

Unit Price

Total

Value

Potential

Proﬁ t

Comforters

216

46.52

Formula A

Formula B

Formula C

Formula D

Night lights

4,283

6.89

Pillows

691

47.64

Sleep sound machines

103

45.06

Sleepwear

489

16.77

Total

—

—

—

—

—

Average

Formula E

Lowest

Formula F

Highest

Formula G

Table 2–13 Doze-Now Proﬁ t Potential Formulas

Formula A = Units on Hand * Average Unit Cost

Formula B = Average Unit Cost * (1 / (1 – .58))

Formula C = Units on Hand * Average Unit Price

Formula D = Total Value – Total Cost

Formula E = AVERAGE function

Formula F = MIN function

Formula G = MAX function

Continued >

C6161_EXC_02.4c.indd 157

C6161_EXC_02.4c.indd 157

3/6/07 3:30:28 PM

3/6/07 3:30:28 PM