SchoolNet South Africa



Step-by-Step Microsoft Excel XP:

Product Pricing

Step-by-Step Microsoft Excel XP: Product Pricing provides guided instructions for completing Exercise 2.4: Determining and Comparing the Unit Price of Similar Items – Getting the Best Buy. In this exercise you will access the online grocery shopping site and choose three different brands of the same product. You will then record on the ProductPricing Excel worksheet the cost information provided from the online site.

Below is an illustration of the ProductPricing Excel worksheet you will be working on with a sample of one product (three different brands of raisin bran cereal) completed for you:

1. Open the Excel template ProductPricing (the file can be found in the Step-by-Step folder for Project 4 – Consumers\Student Lessons\).

2. Save the file as “YourNameProductPricing”

3. Six columns (A-F) will be used for this exercise. Each column is labeled in the Row 1 cell for that column:

Column A = Product Brand

Column B = Total Price

Column C = Volume of Product

Column D = Unit of Measurement

Column E = Cost per Unit

Column F = Best Buy (mark with X)

Formatting Cells for Text Data Entry

1. Columns A, D, and F the data entry will be text. The column must be formatted for text data entry.

2. On the ProductPricing worksheet, select Column A by clicking on the Column A Heading.

3. On the Format menu, select Cells. The Format Cells dialog box will appear.

4. Click the Number tab, then under Category select Text.

5. Click on the Alignment tab and under Text control select the Wrap text check box. This will allow the text to wrap within a cell and increase the height of the row.

6. Click OK to close the Format Cells dialog box.

7. Repeat steps 2 through 6 above for columns D and F.

Formatting Cells for Currency Data Entry

1. Columns B and E will be formatted for currency data entry.

2. Both Column B and E can be selected by first selecting Column B, and then holding down CTRL and selecting Column E.

3. On the Format menu, select Cells. The Format Cells dialog box will appear.

4. Click the Number tab, then under Category select Currency.

5. Next to Decimal places type or select 2.

6. Under Symbol select a monetary value symbol ($).

7. Click OK to close the Format Cells dialog box.

8. Save the file.

Formatting Cells for Currency Data Entry

1. Column C will be formatted for number data entry.

2. Select Column Heading C.

3. On the Format menu, select Cells.

4. From the Format Cells dialog box, click the Number tab, under Category select Number.

5. Next to Decimal places type or select 2.

6. Click OK to close the Format Cells dialog box.

7. Save the file.

Entering Research Pricing Data

1. Access the online grocery shopping site:

NetGrocer: Grocery Shopping Online



Click on the “Grocery” tab to see product items to choose from.

2. From the Select an Aisle left column, under Canned Food select Fruits (a new page will appear).

3. On the web page that appears select Mixed Fruit.

4. For practice you will use the information provided below first, then you will have an opportunity to choose other products and enter the new product’s data into your worksheet.

Chart 1: Practice Product Pricing

5. Enter the following data into your Excel worksheet, mynameProductPricing:

• Select cell A5 and type Brand A Fruit Cocktail

• Select cell B5 and type 1.09 (your monetary symbol will appear when you select the next cell)

• Select cell C5 and type 8

• Select cell D5 and type ounces

6. Enter your next two Product Brands in cells A6 and A7.

7. Enter the Total Price data for the other two products from above Chart 1 (“Our Price”) into cells B6 and B7.

8. Enter the Volume of Product data for the other two products from above Chart 1 in cells C6 and C7.

9. Enter the Unit of Measurement data for the other two products from above Chart 1 in cells D6 and D7.

10. Your worksheet should look like the sample below:

[pic]

Calculating Cost per Unit

1. Divide Total Price by the Volume of Product to calculate Cost per Unit. To calculate cost per unit:

a. Select cell E5

b. Type =

c. Select cell B5

d. Type /

e. Select cell C5

f. Click Enter.

g. The value in cell E5 should be $0.14.

2. To create a relative reference in cells E6 and E7, select cell E5, grab the fill handle and drag down to cell E7.

[pic]

3. Type an X in the cell in column F that represents the “Best Buy” (lowest cost per unit).

4. Now go back to the online shopping site and choose another completely different product, for another product price comparison between three brands.

-----------------------

Brand C

Fruit Cocktail

Our Price:

$0.95

8.00 oz $1.90/lb

Brand B

Fruit Cocktail

Our Price:

$1.05

8.00 oz $2.10/lb

Brand A

Fruit Cocktail

Our Price:

$1.09

8.00 oz $2.18/lb

Row Heading

Column Heading

Row Heading

Column Heading

Fill handle

(pointer becomes the fill handle when placed over the lower right corner of the cell)

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download