$1.$ Open the file SC$3$ Data and save it to your computer as SC$3$ Birdhouses.

$2.$ Use a current date function cell B$4,$ so Wally knows when the data was last edited.

$3.$ Wally has provided a pricing table below the sales data. Beginning in cell E$7,$ you need to

create a VLOOKUP function that will match the product order by each customer to the

lookup table and extract the correct unit price in Column E$.$ You are looking for an $$exact

match$$ so don$$t forget the $$false$$ argument at the end of the lookup syntax.

$4.$ Use the fill handle to copy this function down the remaining rows and then double check

your values to make sure your lookup function works properly.

$5.$ In the Total Amount column create a formula that produces the total amount purchased for

each customer based on the number of items ordered.

$6.$ Next, Wally would like you to use an IF function that would calculate the $($dollar$)$ discount

amount in Column G based on the condition that if the Total Amount shown in Column F is

greater than $$110,$ he wants to calculate a $5\%$ discount on the Total Amount purchased; if it

is not greater than $$110,$ he wants to show a zero for the discount amount. The discount

rate is recorded in cell C$38$ below the table. Take special measures when you reference this

cell in the function.

$7.$ Remember that all birdhouses are available at the market for purchase other than the

higher end $$Deluxe $24$ Cedar$$ birdhouse. Those are a special order and must be shipped to

the customer. In Column H$,$ Wally needs another IF function that would show the shipping

charge shown in cell C$39$ if a customer orders the $$Deluxe $24$ Cedar$$ birdhouse. If the

customer orders any other type of birdhouse, the results should show zero.

$8.$ In the final column $($Column I$),$ the net amount needs to be calculated based on total

amount minus discount plus shipping.

$9.$ Wally needs one more formula on his sheet$$he wants to provide totals for the month in

Row $28.$ If he has more sales next month, he can always insert rows above the total row.

Type the label TOTALS in cell A$28$ and then in the range D$28$:I$28$ use the SUM function to

total the columns.

$10.$ For the range A$28$:I$28,$ apply Bold and a top border.

$11.$ Now it is time to add formatting to other sections of the worksheet. Apply comma format

with $2$ decimal places to all dollar values in the worksheet. Use zero decimal places in

Quantity column.

$12.$ Check all worksheet columns to make sure the information is fully displayed. If not, change

the width where necessary.

$13.$ Merge & Center the title in cell A$1$ over all the worksheet columns of data $($A:I$).$ Then

change to $20$ pt$.$ font size and Green Accent $6$ font color. Apply Bold.

$14.$ Merge & Center the subtitle in cell A$2$ over all the worksheet columns of data. Change to $16$

pts$.$ font size and use same green font color and bold.

$15.$ Apply Green Accent $6$ Fill color to Row $6$ headings and change the Font color to white.

$16.$ Wally would like to have special formatting displayed when he makes higher volume sales.

In the Total Amount column $($F$),$ apply a conditional format as follows: Use Greater than in

the Highlight Cells and Rules option. He wants to highlight any value greater than $100.$ Then

use a custom format with a green fill and black font.

$17.$ In looking at the results of your conditional formatting, you$$ve decided that the green is too

dark. Use the Manage Rules option for conditional formatting. Edit your rule $$format$$ by

clicking the Format button and change the fill color from green to a lighter blue of your

choice.

$18.$ There are just a few more steps needed before completion. Wally wants the data to all

appear on one page. Change the orientation to landscape. If all the columns still do not fit

across one page, take necessary steps to ensure that they do$.$

$19.$ To ensure that Wally$$s worksheet prints correctly, a special $$Print Area$$ needs to be set so

that the pricing table and discount and shipping information are not included.

$20.$ Finally, he wants a footer on the worksheet that shows the filename at the left and your

name $($the creator!$)$ at the right.

$21.$ Save and submit the completed SC$3$ Birdhouses file through the Chapter $3$ Scored

Assignments link in Canvas by the stated deadline.