Instructions

First, insert a column next to column B. We are going to use this column for percent change from 2016 to 2017. The mathematical formula for percent change is going to be:

(2017 Sales 2016 Sales)/(2016 Sales)*100

You are going to need to make an Excel formula for this, and it will go into cell B2. You will also need to format column B to the Number format type with two decimal places.

Insert a column between each of the years and make a formula to find the percent change between those years (for example, cell D2 will end up containing the percent change from 2015 to 2016). There does not need to be a column between the 2014 and 2013 data, because there is no data for the 2012, we cannot calculate the percent changes for 2013.

Select the sales with the percent change Sales formulas (should be cells B2, D2, F2, H2), and go to Conditional Formatting. Choose an icon set that has a green icon, a yellow icon, and a red icon. Go back to Conditional Formatting and choose Manage Rules. Edit the rule we have just created to conform to the rule above/below for Sales. Throughout the exercise, we are going to want to use number instead of percent for the Type, so make sure to change that before entering numbers (changing it will erase everything in the entry boxes). Also before you click OK, check the box that says, Show Icon Only.

For Sales: A green light should represent sales growth (where the sales of one year exceed the sales of the year that came before it). A yellow light represents sales that are at least 98% of the previous years sales amount. Red represents anything that is less than 98% of the previous years sales amount.

Two things to consider:

(1) Because we multiplied by 100 in our formula, the numbers we are shown are in percent. So if you see 1.98, that means 1.98% change. If you see -2.35, that means -2.35%.

(2) If you want to at least have made 98% of the previous years sales, how much could your sales have decreased by? That will be the number you use for the yellow icon rule.

The rule for Operating Expenses that will be put in row 7 work off the same type of percent change as Sales. (2017 Operating Expenses 2016 Operating Expenses)/2016 Operating Expenses*100

For Sales Returns, it will be a percent of Sales, so on row 3, you will have a formula for percent of Sales. 2017 Sales Returns/2017 Sales*100

Cost of Goods Sold will be a percent of Sales as well, so on row 5, put a formula for percent of Sales.

2017 Cost of Goods Sold/2017 Sales*100

Then for each of them create Conditional Formatting with icons based on the rules given to you above. Pay attention to signs (for example, a decrease in operating expenses is considered good).You can also reverse the icon order if that makes more sense for the rule (Like it sounds. Red and yellow will get a rule, and green will be what does not fit into those two rules).

You have been given the sales data for Merchandisers, Inc. (shown below as well as provided to you in a spreadsheet). 2017 918,764 S904,913 IS925,07 2013 934,869 15969,864 $48,626 $38,996 $42.979 $37.180 $48,896 870,138 $865,917 $882,092 $897,689 $920,968 2016 2014 les les Returns Sales ost of Goods Sold S673,547 ross Profit 83.434 148,968 ncome Taxes $21,124 $16,441 $20,541 S2 $31,686 $24,661 S30,811 $38,037 S63,706 59 $42.47 Your manager wants you to add "traffic lights" (one of the icon sets that include green, yellow, and red) to create a short of dashboard that managers can look at to see how well the company is doing in regards to the various aspects of sales. To create this dashboard, he gives you some rules as to how the company would want to rate different sales performance measures For Sales: A green light should represent sales growth (where the sales of one year exceed the sales of the year that came before it). A yellow light represents sales that are at least 98% of the previous year's sales amount. Red represents anything that is less than 98% of the previous year's sales a mount. For Sales Returns: A gree less than 4% of sales, a yellow light means that sales returns are between 4 and 5% of sales, and a red light means that sales returns are more than 5% of sales. n light means that sales returns ar For Cost of Goods Sold: A green light means cost of goods sold are less than or equal to 75% of sales, a yellow light means COGS is between 75% and 80% of sales, and a red light means cost of goods sold are greater than or equal to 80% of sales. For Operating Expenses: A green light means expenses were lower than the year before, a yellow light means expenses did not increase by more than 3% from the year before, and a red light means that expenses increased by 3% or more from the vear before