50.1K

Verified Solution

Link Copied!

i need the formulas to fill out this workshet

BUAD 161 -- Microsoft Excel Extra Credit, Part 1 Instructions: 1. On the Electricity Use" worksheet, sort the facilities and associated figures by Facility Name using the Excel "Sort" function 2. On the "Electricity Use" worksheet, in Cell B10, enter a formula to calculate the sum of the 2019 Usage across all facilities. 3. On the "Electricity Use" worksheet, copy the formula from step 2 and paste it into the "Total row for "Cost" and "Square Footage. 4. On the "Electricity Use" worksheet, in Cells C3 through C9, calculate the Percent of Total Usage by dividing the kWh Usage of each facility by the Total 2019 Usage, and format the values to display the percent symbol 13 5. On the "Electricity Use" worksheet, in cell D3, enter a formula that calculates the cost of the 2019 Electricity Usage for that location, using the 2019 Cost per kWh amount located in cell H3 NOTE THAT THE COST LISTED IN CELL H3 IS GIVEN IN CENTS AND FRACTION OF A CENT. "NOT DOLLARS 6. On the "Electricity Use" worksheet, do the same for cells D4 through D9 by copying the formula in cell D3 and modifying the formula in the subsequent rows to maintain a link to the Cost per kWh cell. H3 Vlookup Part Sales Data Part 1 Instructions Electricity Use File Home Insert Draw Formulas Data Financial Review Logica Excel - Accounting Extra Credit BUAD161-1 (1) - Saved View A Test Date & Time Lookup & Reference AutoSum Recerey Used Math & Trig 8. Calculate Now fx ELECTRICITY CONSUMPTION FACILITY NAME Cost Per Square 2019 Usage (Wh) Percent of Total Usage Square Footage Cost per kW 13 CENTS per kWh 24330 0.00 182.980 563,795 12.497 133.000 s von O . 7,410 von Building 107630 45.180 + Pan instructions Electricity Use Sales Data Vlookup Part 2 Instructions uto Sum Recently Used Financial 2 Logical A Text Date & 7. On the "Electricity US worksheet, in Column F, enter a formula that calculates the Cost Per Square Foot for each facility by dividing the Cost by the Square Footage for each facility. 8. On the "Electricity Use" worksheet, in cell F10, the orange cell, calculate the average Cost Per Square Foot for all facilities. 9. On the "Sales Datatab, use a SUMIF formula in any cell in column "H" to calulate the total of all units sold online 10. On the "Sales Data" tab, use a COUNTIF formula in any cell in column "H"to calulate the total number of orders shipped to Denmark 25 11. On the "Vlookup" tab, write VLOOKUP formulas to fill in ALL of the blue-shaded cells from data from the table on the "Sales Data" tab. You MUST USE a VLOOKUP formula in each cell, NOT a direct reference to a call on the sales Data table 27 28 12. On the "Sales Data" tab, format the table to make it look EXACTLY LIKE the sample graphic to the right of the table Note that the column heading names are on one row, and are center justified both horizontally and vertically. Place a double-line border around the entire table 13. On the "Sales Datatab, use the "Freeze Panes" function so that the column label row remains visible at all times as user Scrolls down through the table rows 14. on the sales Datatab, use the Freeze Panes" function so that the customer number column remains visible at all times as user Scrolls to the far right of the worksheet Part 1 Instructions Electricity Use Sales Data Vlookup Part 2 Inst File Home Insert Draw Formulas Data Review View AutoSum Recently Used Financial Logical A Text Date & Time Lookup & Reference Cust Number Customer Name Country Product Sales Channel Units Sold 23726 23422 23873 24179 24253 23691 23341 + Part 1 Instructions Electricity Use Sales Data Vlookup Part 2 Instructions Trial Balances

Answer & Explanation Solved by verified expert

See Answer