Managing Catering Supplies
Lisa Noriega developed the spreadsheet shown in Figure 4-24 so that she can better manage her inventory of disposable cater-ing supplies. Download the spreadsheet named Ch04Ex01 so you can help her with the inventory analysis.
Lisa listed her inventory items in "Case" quantities, but she now wants to analyze items according to "Pack" quantities and create a price list to show to her customers. For example, a case of Heavy Duty Deluxe Disposable Plastic Knives has 12 packs of 24 knives each. She wants to calculate a "Sales Price per Pack" based on her cost plus a 25 percent markup.
Lisa asks that you complete the following operations and answer the following questions.
# Create columns that list Case Pack, Packs on Hand, and Cost per Case Pack for each item. Use a formula to calculate the Cost per Case Pack.
# Create a column that lists Sales Price per Pack. Use a formula to calculate a 25 percent markup. Set up an assumption cell to input the percentage markup rather than include the markup value in the formula.
# Format the spreadsheet to make it easy to read and visu¬ally appealing.
1. What is Lisa's total investment in disposable catering supplies?
2. What is the total sales value of her inventory?
3. How much profit will she make if she sells all of her inventory at a 25 percent markup?
4. How much profit will she make if she uses a 35 percent markup instead?
Catering supplies spreadsheet
|
A
|
B
|
C
|
D
|
E
|
F
|
|
Item No.
|
Description
|
Color
|
Unit
|
Unit Cost
|
On Hand
|
|
630K-C
|
Deluxe Heavy Duty Disposable Plastic Knives
|
Clear
|
Case (12 pk/24 each)
|
13.61
|
3
|
|
630F.0
|
Deluxe Heavy Duty Disposable Plastic Forks
|
Clear
|
Case (12 pk/24 each)
|
13.61
|
3
|
|
6305-C
|
Deluxe Heavy Duty Disposable Plastic Spoons
|
Clear
|
Case (12 pk/24 each)
|
13.61
|
3
|
|
630K-B
|
Deluxe Heavy Duty Disposable Plastic Knives
|
Black
|
Case (12 pk/24 each)
|
13.61
|
2
|
|
630E-8
|
Deluxe Heavy Duty Disposable Plastic Forks
|
Black
|
Case (12 pk/24 each)
|
13.61
|
2
|
|
630$-8
|
Deluxe Heavy Duty Disposable Plastic Spoons
|
Black
|
Case (12 pk/24 each)
|
13.61
|
2
|
|
5454W
|
54" x 54" Plastic Table Cover
|
White
|
Case (24)
|
19.15
|
1
|
|
5.4548
|
54" x 54" Plastic Table Cover
|
Beige
|
Case (24)
|
19.15
|
2
|
|
54548L
|
54" x 54" Plastic Table Cover
|
Black
|
Case (24)
|
19.15
|
1
|
|
549W
|
54" x 108' Plastic Table Cover
|
White
|
Case (24)
|
30.24
|
4
|
|
5498
|
54" x 108' Plastic Table Cover
|
Beige
|
Case (24)
|
30 24
|
2
|
|
549BL
|
54" x 108' Plastic Table Cover
|
Black
|
Case (24)
|
30.24
|
1
|
|
72W
|
72" Round Plastic Table Cover
|
White
|
Case (24)
|
52.08
|
3
|
|
728
|
72" Round Plastic Table Cover
|
Beige
|
Case (24)
|
52.08
|
3
|
|
7281
|
72" Round Plastic Table Cover
|
Black
|
Case (24)
|
52.08
|
1
|
|
537
|
13" x 17" Linen-Like Napkins
|
White
|
Case (6 plc/50 each)
|
45.90
|
6
|
|
28500
|
10" x 10" Linen-Like Napkins
|
White
|
Case (8 pk/50 each)
|
42.00
|
3
|
|
1010W
|
10" x 10" 2-ply Beverage Napkins
|
White
|
Case (12 pk/50 each)
|
18.14
|
6
|
|
10108
|
10" x 10" 2-ply Beverage Napkins
|
Black
|
Case (12 pk/50 each)
|
18.14
|
1
|
|
101OR
|
10" x 10" 2-ply Beverage Napkins
|
Red
|
Case (12 pk/50 each)
|
18.14
|
1
|
|
1313W
|
13" x 13" 2-ply Luncheon Napkins
|
White
|
Case (12 pk/50 each)
|
25.20
|
3
|
|
13138
|
13" x 13" 2-ply luncheon Napkins
|
Black
|
Case (12 pk/50 each)
|
25.20
|
2
|
|
1010WED
|
10" x 10" Wedding Bells Print Napkins
|
White
|
Case (12 pk/50 each)
|
26.46
|
3
|
|
1313WED
|
13" x 13" Wedding Bells Print Napkins
|
White
|
Case (12 pk/50 each)
|
37.80
|
4
|
|
CC12
|
12 Oz. Classic Crystal Tall Plastic Glasses
|
Clear
|
Case (12 pk/20 each)
|
78.33
|
1
|
|
CC16
|
16 oz. Classic Crystal Tall Plastic Glasses
|
Clear
|
Case (12 pk/20 each)
|
107.73
|
1
|
|
CCR9
|
9 oz. Classic Crystal Plastic Rocks Glasses
|
Clear
|
Case (12 pk/20 each)
|
53.90
|
3
|