BUS 2110 Part 1: Paint Calculator 1. Open the workbook named Paint.xlsx, and save it as Paint Calculator_yourname.xlsx. Fill in the data into the worksheet from the table shown below. Include a meaningful title at the top of the worksheet. Ultimately, this worksheet will be used as a template and filled out on site by the painter. 2. To complete steps 2-10, 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 that are needed for your calculations on the Inputs worksheet in the workbook. Assume all wall surfaces, including the ceiling area, are to be included when calculating repair and painting costs. Remember, your formulas will need to work when new quantities are substituted into the data-entry area. 3. In the column listed, calculate the total square footage (sf) of walls and ceiling. If a room is 10 by 12 with an 8 ceiling height, it would have two walls that are 10 by 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. In column I, 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 of wall/ceiling. If no primer is required, a value of 0 should be entered. Remember to list any additional inputs on a separate worksheet as described above. 5. In column J, calculate the cost of the first coat of paint. If the condition of the wall is 1, the cost of paint is $0.65 per sf; if the condition of the wall is 2, the cost of paint is $0.70 per sf; otherwise, the cost is $0.85 per sf. 6. In column K, calculate the cost of the second cost of paint based on the following criteria: If the condition of the wall is 3, as second coat of paint will be required at $0.45 per sf. If the condition of the walls is not poor (3), but new wall color is lighter than the existing color, a second coat of paint will be required at $0.50 per sf. Otherwise, no second coat will be required, and a value of $0 should be entered. 7. In column L, calculate the cost adjustment for paint quality based on the following criteria: If premium paint is used, add $0.25 per sf. If economy paint is used, deduct $0.15 per sf. 8. In column M, calculate the total cost to paint this room (primer, first coat, second coat, and adjustments for paint quality). 9. In column N, determine if (TRUE OR FALSE) this is a low-priced room. A low-priced room is one that is estimated to cost less than $300. 10. Below the data, total the costs of each item (primer, first coat, and so on) 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: If the total cost of the painting job is less than $800, then there is no discount. If the total cost of the painting job is at least $800 but less than $2,000 then a 5% discount will be given (discount is calculated based on the grand total cost for all items and all rooms). If the total cost of the painting job is at least $2,000 but less than $5,000, then a 10% discount will be given. If the total cost of the painting job is $5,000 or more, then 15% discount will be given. 12. Format your worksheet so that they are easy to read and information is clearly identifiable. Highlight the result of Step 11 in yellow. 13. Save and close the Paint Calculator_yourname.xlsx workbook and submit via Moodle. Worksheet data for the paint calculator Room Length in Feet Width in Feet Height in Feet Square Feet (SF) of Wall/Ceiling Wall Condition New Color Lighter Paint Quality Great Room 30 14 12 3 FALSE Premium Bedroom1 18 11 8 1 FALSE Superior Bedroom2 14 12 8 1 FALSE Economy Bath 8 6 8 2 TRUE Premium Screenshots Calculator Worksheet Inputs Worksheet Part 2: Fishing Vijay has been asked to develop an order form for fishing equipment. He has already created a workbook name Fishing.xlsx, which contains the worksheets described below. Worksheet Description Orders Contains an order form for fishing products. Item List Lists items sold by the Fishing division, including the item number, description, shipping volume in cubic feet (cf), and corresponding price schedule. Sched1 Lists fixed unit prices ($/unit) based on item number. Sched2 Lists variable unit prices ($/unit) for soft bait in 4 oz. packages. Discount Lists discounts based on the total value of an order. For example, orders under $3,000 receive no discount, and orders of at least $3,000 but less than $5,000 receive a 2% discount, as detailed on the worksheet. Ship Contains shipping data. Shipping costs are based on the shipping method (Truck, Rail, or Air), the shipping region, and the volume of the order. The prices given are dollars per cubic foot ($/CF). Regions vary by state. Regular customers are charged at the rates listed in the first shipping table, and rates for preferred customers are in the second shipping table. States Lists all the U.S. states and their corresponding region abbreviations (NE, SE, SW, etc.), and a list of corresponding region numbers. In these steps, you will complete the order form, creating the formulas so that new items can be added easily to the item list. Youll need to design the order form so that it works as follows: Prices for fixed-price items are listed in Sched1 worksheet. Prices for variable-priced soft bait packages are listed in the Sched2 worksheet. A price schedule code (1 for fixed priced, 2 for soft bait variable priced) is included in the Item List worksheet. Shipping costs are based on the freight customer type, destination region, shipping method, and total shipping volume. Users enter the freight customer type text and state abbreviation. You need to use this information to retrieve the freight customer type number and region number. Calculate the shipping volume based on the volumes listed by item number in the Item List worksheet. Discounts are calculated as a percentage of the total order value as listed in the Discounts worksheet. Orders of less than $3,000 receive no discount, orders of at least $3,000 or less than $5,000 receive a 2% discount, orders of at least $5,000 but less than $10,000 receive a 3% discount, and orders of at least $10,000 but less than $25,000 received a 4% discount. Orders of $25,000 or more receive a 6% discount. As you complete the Orders worksheet, select functions that are flexible enough to allow for additional items or up to 23 pricing schedules. Use range names to make the form easy to use and troubleshoot. It the item number field is blank, be certain your form displays a blank cell for the resulting unit price and total. Test that your workbook calculates the correct values. Complete the following: 1. Open the workbook name Fishing.xlsx and then save it file as Fishing Orders_yourname.xlsx. 2. Use the following test data: Order: Item #201 (150 items), Item #209 (315 items), Item #218 (500 items) Shipped by truck to California (CA) to a preferred customer 3. In cell B9 of the Orders worksheet, write a formula that enters the item description. Copy the formula to cells B10:B14. 4. In cell D9 of the Orders worksheet, write a formula that calculates the total volume of the first line item (quantity multiplied by volume per item). Copy the formula to cells D10:D14. 5. In cell E9 of the Orders worksheet, write a formula that calculates the unit price. Copy the formula to cells E10:E14. 6. In cell F9 of the Orders worksheet, write a formula that calculates the total value of this line item. Copy the formula to cells F10:F14. 7. In cell F16 of the Orders worksheet, write a formula that calculates the total for all items, excluding discounts and shipping. 8. In cell D16 of the Orders worksheet, write a formula that calculates the total shipping volume of this order. 9. In cell F17 of the Orders worksheet, write a formula that calculates the discount, if any, on this order. 10. In cell F18 of the Orders worksheet, write a formula that calculates the shipping costs directly from the state and ship method (Truck, Rail, or Air). 11. In cell F20 of the Orders worksheet, write a formula that calculates the grand total of this order. 12. Test the formulas by entering different order values, and then repeat step 2. 13. Save and close the Fishing Orders_yourname.xlsx workbook and submit via Moodle. Screenshots Orders Worksheet Part 3: Players You write an NBA blog that provides information, opinions, and analysis related to teams, players and games in the National Basketball Association (NBA). You are to analyze the data using an Excel table that tracks teams, positions, and salaries for each player in the league to help the readers respond with comments. 1. Open the PLAYERS workbook from Moodle. Save as yourlastname. 2. In the Documentation worksheet, enter your name and the date. 3. In the Players worksheet, create an Excel table named NBAPlayers. Format the Salary column with the Accounting format and no decimal places. Format the NBA Players table with the table style of your choice. 4. Make a copy of the Players worksheet, rename the copied worksheet as Sort Position. Sort the NBAPlayers table in ascending order by position, then in descending order by salary. 5. Use conditional formatting to apply a yellow fill with dark yellow text to highlight all players with a salary greater than $10,000,000. 6. Make a copy of the Players worksheet, rename the copied worksheet Filter Team. Insert a slicer filter by Team. Place the slicer to the right of the NBAPlayers table. Select a slicer style that matches the style you used to format the NBAPlayers table. Resize the slicers height and width to improve its appearance. 7. Use the slicer to filter the NBAPlayers table to display all players on the NY Knicks and Miami Heat teams. 8. Expand the filter to display NY Knicks and Miami Heat players earning more than $5,000,000. Sort the filtered table in ascending order by salary. 9. Make a copy of the Players worksheet, rename the copied worksheet as Filter Top 15%. Filter the NBA Players table to display players whose salaries are in the top 15 percent. Sort the data by Salary in descending order. 10. Use the Total row to include the average salary at the bottom of the table; change the Total row label to Average. Add the Count of the Team column to the Total row. Remove the entry in the division column of the Total row. 11. Make a copy of the Players worksheet, rename the copied worksheet as Subtotals. Use the Subtotal command to display the total salary for each team in the Salary column. 12. Based on the data in the Players worksheet, create a PivotTable in a new worksheet that totals salaries by team and position. Place the Position field in the COLUMNS area. Rename the worksheet as PivotTable Team Position. 13. Create a Division slicer for the PivotTable. Resize the slicer object and buttons as needed, then select a slicer style that matches the PivotTable. Use the slicer to filter the PivotTable to display teams from the Atlantic, Central and Southeast divisions. 14. Based on the data in the Player worksheet, create a PivotTable that calculates the number and average salaries by position in a new worksheet. Format the average salaries, change the label above the average salaries to Avg Salary; and then change the label above the count to Number. Resize columns as needed to display all cell contents. Rename the worksheet as PivotTable Avg Sal. 15. Submit the workbook with the worksheets in the following order: a. Documentation b. PivotTable Team Positions c. PivotTable Avg Sal d. Players e. Sort Position f. Filter Team g. Filter Top 15% h. Subtotals 16. Submit the file to Moodle with yourlastname.