Excel Test $2$ Review Exercise

$$ Begin a new workbook and input the information below

$$ Begin by typing the company name title in cell A$1$ Marketing Results

$$ Save the workbook as Excel Test $2$ Review Exercise by Your Name

$$ Replace Your Name with Your real full name

$$ Rename the sheet tab to Marketing and remove$/$delete other sheet tabs

$$ Create a Documentation sheet with relevant information and the date as a value

$$ Setup the workbook below $($Read what$$s highlighted In yellow before entering the data$)-$ centre the column headings with bold and wrap text as indicated $$ create the thick bottom border from A$4$ to H$4($select the range, select Border icon, More Borders..., click on a thicker border, click on Bottom Border$)$

$$ Merge and centre the title over the appropriate cells

$$ Create a stop validation for $$Number of Sales$,$ before entering the data for that column. Create an appropriate input message

$$Territory area$$ should be entered from a list $($Sount$,$ North, East, West$)$

$$ Create a formula for Sales Projection based on increasing the October sales by $18\%$ and format to accounting number format $$ use absolute appropriate referencing and include October Sales amount in the formula

$$ Create a Goal Seek for $$Column G$,$ Row $5($Your Name$)$ for $18\%$ increase of sales to be $$55000$ by changing it$$s October Sales.

$$ Create an If function for the Sales Increase and format to accounting number format $$ create the note as displayed in H$4($show$/$resize$/$move$)$

$$ Search for Domestic and replace with Canadian

$$ Create a new column labelled Bonus and create a warning validation including an input message that will only allow a value greater than $$5,000.$ Format the heading Bonus the same as the other column headings.

$$ Create a pivot table on a separate sheet placing Campaign Type in the Rows, Territory Area in the Columns and display the average and count of October sales. Insert a Slicer for the Marketing Reps. Move the Slicer to the right of the Pivot Table and resize appropriately. Only show Demonstration and eBook types only. Rename the sheet to Pivot Table

$$ Create a conditional format on the proper Budget range that if the October Sales greater than $$50,000,$ create a green fill with dark green text

$$ Return to the Marketing sheet tab and at every change in Location, count the October sales

$$ Create a $3-$D bar chart to show Daniel, Dolphin and Mill$$s October Sales and Sales Projection with $18\%$

$$ Daniel Takes a company loan of $87000$ at $4\mathrm{.}5\%$ interest rate on the promise to pay back in $5$ years. What is his bi$-$weekly payment.

$$ Add the following to a new sheet called $$Bonus Table$$

October Sales Below$($$$)$ Bonus Rate

$4000013\%$

$4500018\%$

$5000022\%$

Above $50$K $25\%$

Using XLOOKUP and conditional statements Update Bonus Column Values by

$$ Multiplying the $$October Sales by the relevant Bonus Rate as per the above table$$