Lisa Noriega developed the spreadsheet shown in Figure 4-27 so that she can better manage her inventory of disposable catering supplies. Download the spreadsheet named Ch 04 Ex 01 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% 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 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?