Excel/Access Assignments
wajsai98
example.docx
( CMPTR Excel 2013 Chapter 13: SAM Project 1a Lakeside Art Center CREATING AND FORMATTING A WORKBOOK )
CMPTR Excel 2013
Chapter 13: SAM Project 1a
Lakeside Art Center
CREATING AND FORMATTING A WORKBOOK
PROJECT DESCRIPTION
The Lakeside Art Center overlooking Lake Tahoe in northern California contains a theater, an exhibition space, and workshop rooms. Throughout the year, concerts, musicals, lectures, plays, exhibitions, and courses provide locals and visitors with a wide range of cultural activities. The Center employs twenty people including production and maintenance staff, the artistic director and his assistants, marketing staff, and administrative staff. As an office assistant, you will complete an expense statement from Kim Palmer by formatting cell contents, modifying rows and columns, adding formulas, and preparing the worksheet for printing. You will then copy the formatted worksheet to use as a template for all future expense reports.
GETTING STARTED
· Download the following file from the SAM web site:
· CMPTR_Excel2013_C13_P1a_FirstLastName_1.xlsx
· Open the file you just downloaded and save it with the name:
· CMPTR_Excel2013_C13_P1a_FirstLastName_2.xlsx
· Hint: If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.
PROJECT STEPS
1. In the Palmer worksheet, select cell A1 and apply the formatting options described below:
a. Change the font to Arial.
b. Change the font size to 22 pt.
c. Change the font color to Dark Blue (1st row, 9th column in the Standard Colors section of the Color Palette.)
d. Bold the content of the cell.
2. Apply the Long Date Number format (Monday, June 20, 2016) to the date in cell A3.
3. Select the non-adjacent ranges B7:B9 and D7:D9 and then apply the bold formatting to the range.
4. Edit the contents of cell E8 to Production.
5. Use Autofit to change the width of column C to make all the contents visible.
6. Change the width of column H to 13.00 characters.
7. Select the range A11:K11 and apply bold formatting to the range.
8. Change the height of row 11 to 49.50 characters.
9. Wrap text in cell K11.
10. Apply the Short Date Number format (5/3/2016) to cell A12, then use the Format Painter to copy the formatting only to the range A13:A15.
11. Edit the contents of cell E12 to 120.
12. Enter the values in bold shown in Table 1 below into the range E15:I15.
Table 1: Values for Range E15:I15
13.
|
Cell |
Value ( © 2014 Cengage Learning. ) © 2014 Cengage Learning. |
|
E15 |
600 |
|
F15 |
400 |
|
G15 |
50 |
|
H15 |
300 |
|
I15 |
100 |
13. In the cell J12, enter a formula using the SUM function that calculates the total of the range E12:I12. Copy the formula you created in cell J12 to the range J13:J15.
14. In cell K12, enter a formula that subtracts the value in cell F12 from the value in cell J12 to determine the Total expenses without transport costs. Copy the formula you created in cell K12 to the range K13:K15.
15. Copy the formula in cell E16 to the range F16:K16.
16. Copy the formula in cell E17 to the range F17:K17.
17. Format the values in the range E12:K15 with the Comma Style Number format.
18. Format the values in the range E16:K16 with the Accounting Number format and then format the range E16:K16 as bold.
19. Format the cells in the range E17:K17 with the Percentage Number format and then increase the number of decimal places displayed to 2 (if necessary).
20. Delete the range A19:A21, shifting the remaining cells up.
21. Change the page orientation of the Palmer worksheet to Landscape.
22. Switch the view of the Palmer worksheet to Page Break Preview.
23. Make a copy of the Palmer worksheet and rename it Expense Template. Move the Expense Template worksheet so that it located between the Documentation and the Palmer worksheet.
24. In the Expense Template, clear the content from the cell A3, and the ranges C7:C9, E7:E9, and A12:I15. (Hint: Ignore the error messages that appears in the range E17:K17 after clearing the content from the range A12:I15.)
