EXCEL UNIT PROJECT
E-BOOK
Open the E-BOOK file. Complete the following directions. Save as YOURLASTNAME EBOOK.
1. Familiarize yourself with the categories in Column A.
2. Bold the entire worksheet.
3. Enter the SYSTEM date in Cell F2; format it to the 3/14/2001 style.
4. Center and Italicize Cell B3. Rotate its contents 45º. Copy this format to the rest of the years.
5. Indent A8:A12 one space.
6. Change the font size in cells A7, A13, A15, and A17 to 12 pt. Change the font size in cell A19 to 14 pt. and underline the characters in the cell. Add a thick bottom border to the range A3:F3.
7. Change the following column widths: A = 23.43; B through F = 15.00.
8. Change the height of rows 7, 13, 15, 16, and 17 to 24.00. (Try to change all at once)
9. Assign the Comma style format with no decimal places to the range B4:F17.
10. Format Rows 4 and 17 for Accounting style, dollar sign, 0 decimal places.
11. Complete the following formulas (Remember: some of them may require an ABSOLUTE entry!):
a. 2011 Cost of Goods Sold = 2011 Revenue - (2011 Revenue * Margin)
b. Copy to rest of row
c. 2011 Gross Margin = 2011 Revenue - 2011 Cost of Goods Sold
d. Copy to rest of row
e. 2011 Advertising = 1000 + 15% * 2011 Revenue
f. Copy to rest of row
g. 2011 Rent (Cell B9)= 1,800,000
h. 2012 Rent (Cell C9) = 2011 Rent + 10% * 2011 Rent (The rent is increasing 10% each month)
i. Copy cell C9 to rest of row
j. 2011 Salaries = 18% * 2011 Revenue
k. Copy to rest of row
l. 2011 Supplies = 1.5% * 2011 Revenue
m. Copy to rest of row
n. 2011 Total Expenses= Sum of all expense categories
o. Copy to rest of row
p. 2011 Income Before Taxes = 2011 Gross Margin - 2011 Total Expenses
q. Copy to rest of row
r. 2011 Income Taxes: (This requires an If Statement.) If 2011 Income Before Taxes is less than zero, then 2011 Income Taxes = 0; otherwise 2011 Income Taxes = 40% * 2011 Income Before Taxes.(If you CANNOT figure this out, type in the correct numbers manually; I will know by your formulas worksheet.) The manual numbers are: 1,326,036; 0; 550,874; 0; 927,156;
s. Copy to rest of row. If you get dashes instead of 0s, that's fine.
t. 2011 Net Income = 2011 Income Before Taxes - 2011 Income Taxes
u. Copy to rest of row
1. Change the font in cell A1 to 26-point Book Antigua.
2. Change the font in cell A2 to 16-point Book Antigua.
3. Change the font in cell F2 to 10-point Century Gothic.
4. Change the background fill color of A1:F2 to Orange, Accent 6.
5. Do the same background color for Cell A7, Row 13, Row 15, Row 17, and Cells A19 through B24.
6. Rename the sheet tab E-BOOK. Choose a tab color.
7. Spell check.
8. Create a 3-D pie chart of the Years and the Net Income. Movethe chart location to a separate sheet. Name the sheet Net Income and choose a different tab color. Move the sheet so that it is AFTER the E-Book worksheet tab.
9. Format the data labels options in the chart tocategories, and percentages; place them outside end. Omit the legend.
10. Create a title that reads Net Income 5 Years.
11. Explode the largest slice a little. Bold the slice labels. Save one last time as YOURLASTNAME E-BOOK.
Attachment:- e-book_2011_project.xlsx