QUESTION 4 - ACCOUNTS (20 marks)

1. Open the worksheet tab Accounts.
2. Adjust the Row height for table rows using the Format Cell and Auto Fit Row Height option.
3. Restrict data entry to Budget within the range D2:D13 by creating a validation rule, input message, and error alert. Allow the Budget to be a whole number between 0 and 775,000.

Input Message Tab: Title: Budget

Input Message: The budget may vary from 0 to 775,000.

Error Message Tab: Title: Data is outside accepted range.

Error Message: Please enter budget from 0 to 775,000.

4. Convert the range A1:G13 to a Table using Blue, Table Style Medium 2.
5. Add a calculated column to the table in column G and create a structured reference formula in the first record that subtracts the Expenses from the Budget. Copy the formula down the column.
6. Adjust all column widths.
7. Use conditional formatting to format the cells of the table containing negative balances with a light orange fill.
8. Add a Total row to the table. Select an option available in the Total Row drop-down list to add all values in columns Budget and Expenses.
9. In cell K1 use a function in a formula to count the number of cells in a range that contains Town of Northfield. In J1 add a label of Festivals:
10. In K2 create a formula using VLOOKUP to search the table for the project Town of Northfield in order to locate the contact in charge of the project. In J2 add a label of Contact:
11. Sort the table using the Balance field, sort on Cell Color with order of Cell Color on Bottom.
12. Add a cell comment to a cell with a negative balance using words Report negative balances.
13. Add a row above the table. Merge and center A1 to G1. Enter a title Apt. Building Services.
14. Change the font to Century Gothic and the font colour to Blue, Accent 1 increasing font size to 22.5.
15. Add label Web to H1 and then hyperlink it to the http://AptBuildingServices.ca/ website.
16. Create a custom header with Confidential at the left and input your first name at the right.
17. Set the page layout to have gridlines print so data is easier to read and scale of 80%.
18. SAVE the file.

    Project	Deadline	Code	Budget	Expenses	Contact	Balance
    South Apartments	#######	N23	########	#######	Cathy Brown
    Warren Condos	#######	C21	########	#######	Cathy Brown
    Rangely Industrial Park	#######	V52	########	#######	Jill Jones
    West Mall	#######	B12	########	#######	Jim Kyte
    North Mall	#######	C13	########	#######	Cathy Brown
    Green Ridge Condominium	#######	AB2	########	#######	Jill Jones
    Town of Northfield	#######	AA5	########	$30,000	Jill Jones
    Route 100	#######	AA1	########	#######	Jim Kyte
    Langley Parking Lot	#######	C43	########	#######	Jim Kyte
    1st Street Bridge	#######	V53	########	#######	Cathy Brown
    Northridge School	#######	A3A	########	#######	Cathy Brown
    Town of Northfield	#######	AA6	########	$30,000	Jill Jones