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!
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.
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‑_
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.
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
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
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
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.
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.
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.