Apply Case Problem 1

Data File needed for this Case Problem: Salon.xlsx

Open the Salon workbook located in the Excel2 Case1 folder included with your Data Files, and then save the workbook as Salon Income Statement in the location specified by your instructor.

In the Documentation sheet, enter your name in cell B3 and the date in cell B4.

Apply the following formatting to the Documentation sheet:

1. Format cell A1 using the Title cell style.

2. Format the range A3:A5 using the Accent6 cell style.

3. In cell B4, format the date value using the long date format, and left-align the cell contents.

4. In cell B5, format the text string Special Highlights Hair Salon in italic.

In the Income Statement worksheet, format cell A1 using the Title cell style.

Calculate the following items in the Income Statement worksheet:

a) In cell C7, calculate the Gross Profit, which is equal to the Gross Sales minus the Cost of Sales.

b) In cell C21, calculate the Total Operating Expenses, which is equal to the sum of the operating expenses.

c) In cell C22, calculate the Total Operating Profit/Loss, which is equal to the Gross Profit minus the Total Operating Expenses.

d) In cell C23, calculate the projected Income Taxes, which is equal to 35 percent of the Total Operating Profit/Loss.

e) In cell C24, calculate the Net Profit/Loss, which is equal to the Total Operating Profit/Loss minus the projected Income Taxes.

Set the following formats to the Income Statement worksheet:

a) Format cells A3 and A26 using the Heading 2 cell style.

b) Format cells A4 and A9 and the range A27:A38 in bold.

c) Format cells B5, C7, B10, C21, and C24 using the Accounting format with no decimal places.

d) Format cells B6, B11:B19, C22, and C23 using the Comma style with no decimal places.

e) Indent the text in the ranges A5:A6 and A10:A19 two spaces. Indent the text in cell A7 and the range A21:A24 four spaces.

f) Add a bottom border to cells B6, C7, C21, C22, and C23. Add a double bottom border to cell C24.

Merge cells A26:E26 and then left-align the merged cell's contents.

Merge the contents of the range B27:E27. Left-align the merged cell's contents and wrap the text within the cell. Increase the height of row 27 to display the entire contents of the cell.

Top-align and left-align the range A27:B38.

Copy the format from the range A27:B27 to the range A28:B38. Merge columns B through E in each row, left-align the text, and resize the row heights to display the complete contents of the cells.

Italicize the text string National Salon News in cells B27 and B28.

Set the following printing formats to the Income Statement worksheet:

a) Insert a manual page break directly above row 26 so that the Income Statement prints on two pages.

b) Set rows 1 and 2 as a print title to print on both pages.

c) Change the page margins to 1 inch on every side.

d) On the first page of the printout, print Prepared by your name in the left section of the header, where your name is your name. Print the current date in the right section of the header. Do not display header text on any other page.

e) For every page, add a footer that prints the workbook filename in the left section, Page page in the center section, and the worksheet name in the right section.

If you are instructed to print, print the entire contents of the workbook in portrait orientation.

Save and close the workbook.

Salaries and Wages	98280
Taxes and Benefits	7371
Insurance	3840
Utilities/Telephone	10320
Advertising	1800
Supplies/Misc.	4000
Lease	9020
Interest	2707
Depreciation	3570
Sales Tax	14700
Total Operating Expenses
Total Operating Profit/Loss
Income Taxes
Net Profit/Loss
Assumptions for the Projected Income Statement
Gross Sales	Projected 20 people per day at an average expenditure of $42 (source: National Salon News, May 5, 2015) over a 350-day year.
Cost of Sales	33% of Gross Sales (source: National Salon News, May 5, 2015)
Payroll	Labor costs are assuming: 4 stylist hired @ $13.50 for 35/hrs per week, 52 weeks per year.
Taxes and Benefits	FICA taxes are assumed to be 7.5% of gross wages
Insurance	Includes owner's liability ($200) and workman's compensation ($120)
Utilities/Telephone	Gas, electricity, water and trash collection average $720 per month. The phone averages $140 per month.
Advertising	Assume $1,000 for the year plus $800 for initial advertising
Supplies/Misc.	Office supplies, computer equipment and support, cleaning supplies.
Lease	The first month's rent is included in the deposit, assume 11 months rent @ $820 per month.
Interest	The $65,000 loan was secured with an annual interest payment of $2,707.
Depreciation	Salon equipment valued at $35,700 with a 10% straight line depreciation over 10 years at $3,570 per year.
Sales Tax	Assume a 5% sales tax on gross sales.