For the past two years, you have been working for MTSU Painting, doing everything from running errands to cutting the we

[answerhappygod]

For the past two years, you have been working for MTSU Painting,doing everything from running errands to cutting the weeklypaychecks and filing the appropriate quarterly employmentwithholding forms with the IRS. Given your knowledge ofspreadsheets, your boss has asked you to create an Excel worksheetfor estimating the price of individual painting jobs. Your bosswants the worksheet to contain some basic input information andautomatically calculate an estimated price so that a customer canquickly know the cost of the proposed work. The variables to beconsidered are as follows: • The dimensions of each room—length,width, and height • The condition of the wall surfaces, where 1represents excellent, 2 represents reasonable but has some peelingand/or old paint, and 3 represents poor condition with major holes,peeling, and/or very old paint • Whether or not the requested newcolor is lighter than the existing wall color (TRUE or FALSE) •Grade of paint being requested—premium, superior, or economyComplete the following: 1. Open the workbook named Paint.xlsx, andthen save the file as PaintCalculator(your last name).xlsx. 2. Thecalculation table worksheet already has some of the informationready for you. Ultimately, this worksheet will be used as atemplate and filled out on site by the painter. To complete Steps3–11, you need to calculate the individual component costs by room,writing all formulas so that they can be copied down the column.List all other inputs (read: references) that are needed for yourcalculations on a separate worksheet in the workbook, named“Inputs”. Assume all wall surfaces, including the ceiling area, areto be included when calculating repair and painting costs.Remember, your formulas will need to work when new quantities aresubstituted into the data-entry area. Give all new columns made anappropriate title. 3. In the column already listed, calculate thetotal square footage (sf) of walls and ceiling. If a room is 10' by12' with an 8' ceiling height, it would have two walls that are 10'× 8' (total of 160 sf) and two walls that are 12' × 8' (total of192 sf), and a ceiling of 10' × 12' (120 sf) for a total of 472 sf.Do not subtract any area for windows, doors, and so on. 4. To theright of the Paint Quality column, calculate the cost of wallrepairs and primer. Only walls with a wall condition of poor (3)will require wall repair and primer. This cost is estimated as$0.60 per sf of wall/ceiling. If no primer is required, a value of0 should be entered. Remember to list any additional inputs (suchas this) on a separate worksheet as described above. 5. In anadjacent column, calculate the cost of the first coat of paint. Ifthe condition of the wall is 1, the cost of paint is $0.75 per sf;if the condition of the wall is 2, the cost of paint is $0.80 persf; otherwise, the cost is $1 per sf. (Hint: use nesting) 6. In anadjacent column, calculate the cost of the second coat of paintbased on the following criteria: a. If the condition of the wall is3, a second coat of paint will be required at $0.55 per sf. b. Ifthe condition of the walls is not poor (3), but new wall color islighter than the existing color, a second coat of paint will berequired at $0.65 per sf. c. Otherwise, no second coat will berequired, and a value of $0 should be entered. 7. In an adjacentcolumn, calculate the cost adjustment for paint quality based onthe following criteria: a. If premium paint is used, add $0.50 persf. b. If economy paint is used, deduct $0.25 per sf. 8. In anadjacent column, calculate the total cost to paint this room(primer, first coat, second coat, and adjustments for paintquality). 9. In an adjacent column, determine if (TRUE or FALSE)this is a low-priced room. A low-priced room is one that isestimated to cost less than $400. 10. Create a row below the datathat totals the costs of each item (primer, first coat, and so on)and then a grand total of all items for all rooms. 11. Since largerjobs have certain economies of scale in setup and cleanup, adiscount is given based on these estimated values to jobs based ontheir total size. Just below the grand total, determine the totaldiscounted price of the job based on the following: a. If the totalcost of the painting job is less than $800, then there is nodiscount. b. If the total cost of the painting job is at least $800but less than $2,000, then a 5% discount will be given (discount iscalculated based on the grand total cost for all items and allrooms). c. If the total cost of the painting job is at least $2,000but less than $5,000, then a 10% discount will be given. d. If thetotal cost of the painting job is $5,000 or more, then a 15%discount will be given. 12. Format your worksheets so that they areeasy to read and information is clearly identifiable. Highlight theresult of Step 11 in yellow.