$19$

Without using the formula bat, add a tolal row to the Gross$\_$Margin table that sums each column denominated in dollars $($i$),$ the columns that do

not contain percentages$).$

Check

figure for total cost of goods sold $$222,150$

$20.$ For each column in the Gross$\_$Margin table denominated in dollars, apply the Accounting format, no decimal, to the first row and the total row. For the remaining cells denominated in dollars, apply the Comma $[0]$ cell style. Proof the spelling on the sheet and $($if AutoSave is not on$)$ save the workbook.

$21.$ Use cell references to enter the Cost of Goods Sold and Gross Margin on the Operating Budget sheet

$22.$ Create a new Get and Transform Data query to import the csy text file Wage and Commission Assumptions. Use the query editor to remove the top row and use the next row as headers. If necessary, transform the data type for the Hourly Pay field from Text to Currency and transform the data type for the Hours Worked per Week field from Text to Decimal Number. Close and load the query to import the data.

$23.$ Apply the Orange, Table Style Medium $17$ to the imported data. Rename the worksheet Wages if Comissions, tab color Orange, Accent $2($column $6,$ row $1).24.$ Use a text formatting function in cells A$6$:AB to remove extra spaces from the Employee data in Column $1.$ Use another text formatting function in cells B$6$: B$8$ to concatenate, or combine, the text in cells A$6$:A$8$ with the sales clerk identifiers in Column $1("$A$,""$B$,""$C$").($The function appears in the Text category; use Excel's Tell Me or help feature if necessary.$)$ When creating the formula, be sure to insert a space between the two text fieids to make the text readable.

$25.$ Copy the results from cells B$6$:B$8$ and paste the values only to cells A$2$:A$4.$ Do not delete the results in cells B$6$:B$8$; they will be used for grading.

$26.$ Add a worksheet named Wages, tab color Black, Text $1,$ with title

Boutique du chic,

subtitie Wages and a blank row. Apply the Title cell style to the title and the Subtitie cell

style to the subtitle.

$27.$ Below the blank row create a table with the following headings $($from left to right Employee, Hourly Pay, Hours Worked per Week, Weekly Wage, Annual

Wage. Set the width of each of the five columns to $13.$ Apply cell style Heading $3$ to each.

$28.$ Link to relevant cells in the Wage$\_$Commission table using cell references; use the linked

data and formulas to calculate the weekly and annual wage for each employee anc in

total.

$29.$ Sales Clerk A recently negotiated a wage increase of $2\%.$ Enter a formula in ce B$5$ of the

Wages sheet that calculates the new hourly wage after the increase.

Check figure for Sales

Clerk A annual wage after wage increase: $$30,975\mathrm{.}36.$