APPLICATION EXERCISES 4-29. EXCEL APPLICATION: Managing Catering Supplies Lisa Noriega developed the spreadsheet shown in Figure 4-27 so that she can better manage her inventory of dispos- able catering 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" quanti- ties and create a price list to show to her customers. For exam- ple, 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% 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% markup. Set up an assump- tion 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 visually 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% markup? 4. How much profit will she make if she uses a 35% markup instead? FIGURE 4-27 Catering supplies spreadsheet. B C D E F 1 Item No. Description Color Unit 2 630K-C 3 630F-C 4 630S-C 5 630K-B 6 630F-B Deluxe Heavy Duty Disposable Plastic Knives Deluxe Heavy Duty Disposable Plastic Forks Deluxe Heavy Duty Disposable Plastic Spoons Deluxe Heavy Duty Disposable Plastic Knives Deluxe Heavy Duty Disposable Plastic Forks Clear auan Case (12 pk/24 each) Unit Cost On Hand T A 13.61 Clear Case (12 pk/24 each) 13.61 3 Clear Case (12 pk/24 each) 13.61 3 Black Case (12 pk/24 each) 13.61 2 9q2 Black Case (12 pk/24 each) 13.61 2 7 630S-B 8 5454W Deluxe Heavy Duty Disposable Plastic Spoons 54" x 54" Plastic Table Cover Black Case (12 pk/24 each) 13.61 2 White Case (24) 19.15 1 9 54548 10 5454BL 54" x 54" Plastic Table Cover Beige Case (24) 19.15 2 11 549W 12 549B 54" x 54" Plastic Table Cover 54" x 108" Plastic Table Cover 54"x108" Plastic Table Cover Black Case (24) 19.15 1 White Case (24) 30.24 Beige Case (24) 30.24 2 13 549BL 14 72W 15 728 16 72BL 54" x 108" Plastic Table Cover 72" Round Plastic Table Cover 72" Round Plastic Table Cover 72" Round Plastic Table Cover Black Case (24) 30.24 1 White Case (24) 52.08 3 Beige Case (24) 52.08 3 Black Case (24) 52.08 1 17 537 13" x 17" Linen-Like Napkins White Case (5 pk/50 each) 45.90 6 18 28500 10" x 10" Linen-Like Napkins White Case (8 pk/50 each) 42.00 3 19 1010W 10" x 10" 2-ply Beverage Napkins White Case (12 pk/50 each) 18.14 6 20 1010B 21 1010R 22 1313W 10" x 10" 2-ply Beverage Napkins 10" x 10" 2-ply Beverage Napkins 13" x 13" 2-ply Luncheon Napkins Black Case (12 pk/50 each) 18.14 1 Red Case (12 pk/50 each) 18.14 1 White Case (12 pk/50 each) 25.20 3 26 CC12 23 13138 13" x 13" 2-ply Luncheon Napkins 24 1010WED 10" x 10" Wedding Bells Print Napkins 25 1313WED 13" x 13" Wedding Bells Print Napkins 12 oz. Classic Crystal Tall Plastic Glasses Black Case (12 pk/50 each) 25.20 2 White Case (12 pk/50 each) 26.46 3 White Case (12 pk/50 each) 37.80 4 Clear Case (12 pk/20 each) 78.33 27 CC16 16 oz. Classic Crystal Tall Plastic Glasses Clear Case (12 pk/20 each) 107.73 28 CCR9 9 oz. Classic Crystal Plastic Rocks Glasses Clear Case (12 pk/20 each) 53.90 113 1