Lab Week 2 – Starting Inventory Worksheet

Background

After careful analysis last week, you have determined that you can join Longmeadow Baking Company if you are able to pay yourself $1000.00 per week as a salary. You now need to create a list of tools that you are going to need while working at Longmeadow Baking Company.

Because the current owners of Longmeadow Baking Company already have the large equipment in place, you do not need to purchase these items as you are going to pay them a fee to use the ones that they have currently in place. Some of the inventory that you will need to store materials and produce your baked goods includes cleaning supplies, storage supplies, baking tools, food utensils, and clothing.

Goal

In this assignment, you are going to format a list of items that you need in order to get started at Longmeadow Baking Company, using the Starting Inventory Workbook found in the assignment details in Canvas. The worksheet will show how many unique items, cost of items in each category you defined, and the total cost of all the items that you require to store and bake your goods.

Then you are going to send your list to your new potential partners for review and update some decisions you have made. This will require you to edit the list and make changes.

You will demonstrate the following Excel skills with this exercise:

• Applying formatting (column width, row height, fill, font, alignment, number)
• Format painter
• Insert row
• Delete row
• Moving row
• Basic formula creation
• Copy/paste formula
• Basic functions (SUM, COUNTA)

The completed spreadsheet will organize the inventory items by categories and total costs calculated for each category.

Creating Inventory List

1. Download and open the file “Starting Inventory Workbook.xlsx” from the Canvas module.
2. Save the workbook as “Starting Inventory.xlsx”

Before we get started, DO YOU REMEMBER HOW TO…?

In order to make spreadsheets more readable and professional looking, we often add formatting to improve the look of the spreadsheet. Many of the formatting options are easily accessible as icons on the Home Ribbon.

Drag and drop the correct icon from the Home Ribbon onto the action that is performed:

3. Highlight rows 1 and 2 and Insert two (2) new rows
4. In cell A1 enter the following text: Longmeadow Baking Company
5. In cells A2:E2 enter the following information
   1. A2 = Items
   2. B2 = Unit
   3. C2 = Unit Price
   4. D2 = Inventory to Purchase and apply wrap text formatting
   5. E2 = Total Cost

6. Auto-Adjust the Column Width of column A
7. Change the Column Width of columns B:E to 15
8. Change the Row Height of row 1 and 2 to 40
9. Merge and Center cells A1:E1
10. In cell A1, change the font to Georgia Pro 26, Bold, White
11. Change the Fill Color of cell A1 to #FF3399 (more colors options menu)
12. Change the alignment of the text of cell A1 to Middle-Align, so the title is centered vertically in the cell
13. Make all the text Bold in cells A2:E2
14. Center-Align the text in cells C2:E32
15. Place a Bottom Border on cells A2:E2

16. We now want to separate our categories. We want 2 rows between each new category of items. Looking at the list, the categories are Cleaning Supplies, Storage Supplies, Baking Tools, Food Utensils, and Clothing
    1. Insert two rows after cell A8 (Mop and Bucket)
    2. Insert two rows after cell A19 (30 Gallon Food Storage Drum)
    3. Insert two rows after cell A29 (French Whips)
    4. Insert two rows after cell A35 (9 Inch Tongs)

17. Place a Bottom Border Style to the cell range A8:E8 (row 8, Mop and Bucket)
18. Use the Format Painter to add Bottom Border Style to cell ranges
    1. A19:E19 (row 30, Gallon Food Storage Drum)
    2. A29:E29 (French Whips)
    3. A35:E35 (9 Inch Tongs)
    4. A40:E40 (Chef Jackets)
19. Merge and Center the following cell ranges and enter the text as shown
    1. C9:D9 = Cleaning Supplies Total
    2. C20:D20 = Storage Supplies Total
    3. C30:D30 = Baking Tools Total
    4. C36:D36 = Food Utensils Total
    5. C41:D41 = Clothing Total
    6. Format C9:D9 to Bold, Right-Aligned text and use the Format Painter to apply the same formatting changes to the remaining cell ranges in the list above

**** Save your workbook before continuing. This is a good habit to get into. ****

20. In cell E3, create a Formula to calculate the total of the Item cost by multiplying the Unit Price X Inventory to purchase.

21. Copy the formula from cell E3 to the remaining items on the list. Use your preferred method to complete this task.
22. Make note of any issues you encountered for the weekly Excel question set.

23. In cells E9, E20, E30, E36, and E41 calculate the SUM of the category Total Costs (Column E)

24. In cell C43 enter the text: Total Cost of Startup
25. Merge cells C43:D43 and make the text Bold and Right-Aligned
26. In cell E43 create a Formula that totals each of the category’s totals together (cleaning, storage, baking tools, food utensils, and clothing)

27. In cell A43 enter the text: Number of Different Items and make the text Bold and Right-Aligned
28. In cell B43 use the COUNTA Function to count the different number of items you’ll need using the information in the A column.

29. All category TOTALS should be Bold, Accounting format with 2 decimal places
30. The first item in each category should be Currency format, right aligned
31. Remaining individual items within each category should be Comma format
32. Between the last item in each category and the row for the total category amount should have a bottom border applied
33. Add the Total cell Style to cells A43:E43

** You will continue with this file in the next section.  If you are continuing on to the next section immediately, you’ll be saving your file in step 2 below.  If you are not immediately moving to the next section, SAVE your file now.**

Updating Inventory

1. Continue using your “Starting Inventory.xlsx” file.
2. Save your workbook.

After completing your Inventory List, you contact your two new potential partners at Longmeadow Baking Company, and they inform you that they have some items already, and you would not need to purchase them.  This is excellent news because it will save you money!  You also noticed there are a few items in the wrong categories, and you decided to change your outfit for the bakery. We need to make some changes in your Starting Inventory Workbook.

3. You notice that your Storage Racks and Baking Racks are in the wrong category.
   1. Move the Storage Racks row from the Cleaning Supplies category to the Storage Supplies Category below the Baking Racks row (There are several methods that you can use to complete this task.  Choose your preferred method.)
   2. Move the Baking Racks row from the Storage Supplies category to the Baking Supplies Category below the Thermometers.
   3. Fix the Currency formatting of the Storage Supplies Category and the Baking Tools Category so only the top amount is in Currency format and Right-Aligned
4. Your partners have told you that you do not need to purchase the trash cans, broom/dustpan, and the mop/bucket.
   1. Delete these rows from your worksheet.
   2. You may need to fix the Bottom Border Style of your category

5. Your partners have reviewed your items and provided you the following feedback.  Adjust your inventory as needed to reflect the changes.
   1. Only two (2) storage racks and four (4) wire shelving are needed.
   2. Only four (4) thermometers and four (4) baking racks are needed.
6. Lastly, you have decided that you are going to wear a Chef Jacket each day and not aprons and will also purchase only one (1) chef hat.
   1. Delete the Aprons and make the necessary changes to your inventory for Chef Hats
   2. Fix the Currency formatting of the Clothing Category, so the top amount is in Currency format and aligned right

Congratulations, you’ve completed this exercise.

** Save your workbook. You will submit this file to the appropriate weekly Excel question. **