Can someone please explain what the heck I am to do with this project? I'm totally lost! I will be forever grateful for any and all help. Thanks!
~Debra
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:
Advertising 3,000
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
