For the past year, you have been working with a medium-sized painting contractor, RJ Construction, doing everything from running errands to cutting the weekly paychecks and filing the appropriate quarterly employment withholding forms with the IRS. Given your knowledge of spreadsheets, your boss has asked you to create an Excel workbook for estimating painting jobs that are done by a subgroup of the construction firm, as either part of larger jobs or as stand-alone projects. Your boss wants the worksheet to contain some basic input information and automatically calculate an estimated price, so that a customer can quickly know the cost of the work. The variables to be considered are as follows:

• The dimensions of each room length, width, and height
• The conditions of the walls, where 1 represents excellent, 2 represents reasonable but has some peeling and/or old paint, and 3 represents poor condition with major holes, peeling, and/or very old paint.
• Whether or not the requested new color is lighter than the existing wall color (TRUE or FALSE)
• Grade of paint being requested premium, superior, or economy

Complete the following:

1. Create an Excel worksheet with the columns and data shown in the following table. Also, include a meaningful title at the top of your worksheet. Add the current date and time below the title. Ultimately this worksheet will be used as a template and filled out on sight by the painter. List all other inputs that are needed for subsequent calculations on a separate worksheet in the workbook.

Room	Length in Feet	Width in Feet	Height in Feet	Square Feet (SF) of Wall/Ceiling	Wall Condition	New Color Lighter	Paint Quality
Kitchen	20	15	8		2	TRUE	Premium
Bedroom 1	16	12	8		1	FALSE	Economy
Bedroom 2	10	12	8		1	FALSE	Superior
Bath	8	6	8		3	FALSE	Superior

Fill in the costs by room. Write all formulas so that they can be copied down the column. Remember, your formulas will need to work when new quantities are substituted into the data-entry area.

2. Use data validation in the Length, Width, and Height columns to verify the user enters numbers rather than text. Use appropriate input and error messages.
3. In the Square Feet column, create a formula to calculate the total square footage (SF) of walls and ceiling. If a room is 10 feet by 12 feet with an 8-foot ceiling height, it would have two walls that are 10 x 8 (total of 160 SF), and two walls that are 12 x 8 (total of 192 SF), and a ceiling of 10 x 12 (120 SF) for a total of 472 SF. Do not subtract any area for windows, doors, and so on.
4. Use data validation for the Wall Condition, New Color Lighter, and Paint Quality columns to verify valid data entry. Use appropriate input and error messages.
5. To the right of the Paint Quality column, calculate the cost of wall repairs and primer. Only walls with a wall condition of poor (3) will require wall repair and primer. This cost is estimated as $0.50 per SF. If no primer is required, a value of $0 should be entered. (Of course, do not enter $0.50 directly in your formula. Enter the $0.50 in your inputs (variable) worksheet and reference the cell in your formula.)
6. In an adjacent column, calculate the cost of the first coat of paint. If the condition of the wall is 1, the cost of paint is $0.45 per SF; if the condition of the wall is 2, the cost of paint is $0.50 per SF; otherwise, the cost is $0.60 per SF. (Again, do not enter these amounts directly in the formula. Reference them in the inputs worksheet.)
7. In an adjacent column, calculate the cost of the second coat of paint based on the following criteria:
   1. If the condition of the wall is 3, a second coat of paint will be required at $0.35 per SF.
   2. If the new wall color is lighter than the existing color, a second coat of paint will be required at $0.30 per SF.
   3. Otherwise, no second coat will be required, and a value of $0 should be entered.

(Again, do not enter these amounts directly in the formula. Reference them in the inputs worksheet.)

8. In an adjacent column, calculate the cost adjustment for paint quality based on the following criteria:
   1. If premium paint is used, add $0.15 per SF.
   2. If economy paint is used, deduct $0.10 per SF.

(Again, do not enter these amounts directly in the formula. Reference them in the inputs worksheet.)

9. In an adjacent column, calculate the total cost to paint this room (primer, first coat, second coat, adjustment).

Use conditional formatting to highlight in yellow the total cost to paint the room if it is a high-priced room. A high-priced room is one that is estimated to cost more than $450. (Again, do not enter this amount directly in the conditional formatting. Reference it in the inputs worksheet.)

10. Create a row below the data that totals the costs of each item (primer, first coat, second coat, adjustments), and then a grand total of all items for all rooms.
11. Because larger jobs have certain economies of scale in setup and cleanup, a discount is given based on these estimated values to jobs based on their total size. Just below the grand total, determine the total discounted price of the job based on the following:
    1. If the total cost of the painting job is less than $1000, then there is no discount.
    2. If the total cost of the painting job is at least $1000 but less than $2500, then a 10% discount will be given (discount is calculated based on the grand total cost for all items and all rooms).
    3. If the total cost of the painting job is at least $2500 but less than $5000, then a 12% discount will be given.
    4. If the total cost of the painting job is $5000 or more, than a 15% discount will be given.

(Again, do not enter these amounts directly in the formula. Reference them in the inputs worksheet. Use a LOOKUP function rather than an IF statement in your formula.)

12. Format your worksheets so that they are easy to read and information is clearly identifiable.
13. Lock all cells which you do not want the user to overwrite. Protect both the quote worksheet and the inputs worksheet (do not include a password).