I am sooooo lost! Can someone please clarify what the heck this project is asking me to do? I would be forever thankful for any and all help! Thanks!

~Debra

THE MAYFAIR COSMETICS COMPANY

The Mayfair Cosmetics Co. is a manufacturer of skin and body care products. The company manufactures these products and sells them to department stores and specialty stores throughout the country. To do this, it must maintain a large product inventory. In order to quickly respond to the retail customer's needs for certain product lines the company president, Stacey Thompson, has decided to modernize the accounting and inventory control procedures. One of the key components of a new automated system is the electronic spreadsheet.

When you have completed this activity, you will be able to:

I . Develop a spreadsheet which will show the effects of proposed inflation rates on a company's monthly revenue and expenses.

2. Develop a spreadsheet which will show the effect of projected sales increases on product lines.

3. Develop a spreadsheet to project a company's inventory figures.

EXERCISE 3‑1

You have been asked to develop a spreadsheet that will show the effects of two inflation rates on the company's monthly revenue and expenses. Stacey Thompson has given you an income statement from the previous month. See Figure 3. 1.

Figure 3.1 Income Statement

Mayfair Cosmetics Income Statement

For Month Ended June 30,19‑_

Revenue:

Sales \$50,000

Expenses:

Insurance 2,000

Miscellaneous 2,000

Rent 3,000

Telephone 1,000

Supplies 5,000

Utilities 2,000

Salaries 10,000

Total Expenses \$28,000

Net Income \$22,000

Given the revenue and expense data listed from the income statement, prepare a spreadsheet to determine the effect inflation rates of 3 percent and 5 percent will have on revenue, expenses, and net income. Determine the new dollar amount for each income or expense item based on each inflation rate.

NOTE: Although the worksheet contains the appropriate riate data, formulas, and functions, the text and numbers need to be formatted to improve their appearance and readability.

Save the spreadsheet as Mayfair Cosmetics Company

Print a copy showing the effect each inflation rate has on the Income statement data. When printing fit the printout on one page. Also print a display of the formulas in the spreadsheet.

EXERCISE 3‑2

You have been given a list of the company's products available for sale. See Figure 3.3.

Figure 3.3 Mayfair Cosmetics Product List

ITEM OUNCES PRICE SALES

Hair Care

Shampoo 16.00 8.00 4050.00

Conditioner 16.00 8.00 800.00

Spray 7.90 6.00 1175.00

Mousse 5.00 7.00 2980.00

Skin Care

Cream 2.00 9.00 2430.00

Cleanser 6.00 9.00 2900.00

Moisturizer 4.00 16.00 1200.00

Gel 2.00 6.00 1430.00

Sun Care

Sunblock spf 15 2.50 9.50 5000.00

Sunscreen spf8 4.50 9.50 4000.00

Sunblock spf25 6.00 12.50 4100.00

Sunscreen spf12 4.50 9.50 4500.00

The ounces column represents the jar or tube size of each product. The price column represents the current amount each retailer will pay for the product. The sales column represents the dollar amount of sales by product for the previous month.

Prepare a spreadsheet that will show the effect a 5 percent, 10 percent, and 15 percent increase in sales will have on each of the product lines. Total the sales and percent increase columns and determine the dollar amount of the difference between the original sales amount and each increase.

Save the spreadsheet as Mayfair Cosmetics Product List

Print a copy of the spreadsheet to fit on one page, and print a display of the formulas as well.

EXERCISE 3‑3

The president wants to know which products represent the top four in sales revenues and what percent of the total sales for the company the four products represent. Select the top four products by sales revenue. NOTE: Use the sales revenue figures prior to computing any of the percentage increases. For the four products, determine the percent of total sales of these four products.

Save this spreadsheet with a name of your choosing. Print both a values version of the worksheet as well as a formulas version.

EXERCISE 3‑4

The marketing department has predicted that the next spring and summer season will result in higher sales revenues for the company's product lines. You are asked to project what the company's inventory figures will be for next month based on a 5 percent increase in quantity sold for hair products; 10 percent increase for skin; and 15 percent for sun care products.

Save this spreadsheet with a name of your choosing. Print both a values version and a formulas version.

QTY/INV OTY/SOLD

Shampoo 2000 Shampoo 650

Conditioner 2000 Conditioner 1200

Spray 1800 Spray 1000

Mousse 2500 Mousse 900

Cream 1200 Cream 600

Cleanser 1500 Cleanser 750

Moisturizer 1600 Moisturizer 1000

Gel 1550 Gel 690

Sunblock spf15 5 1600 Sunblock spf15 5 800

Sunscreen spf8 1500 Sunscreen spf8 800

Sunblock spf25 1600 Sunblock spf25 750

Sunscreen spf12 1600 Sunscreen spf12 900