Microsoft Office Tutorials and References

In Depth Information

**Tutorial C: Building a Decision Support System in Excel**

FIGURE C-17

Copying cell C18 into cell C24 and then editing the IF function arguments to change the sales growth

percentages

As before, you can now copy cell C24 to cell D24. You have completed the Total Sales Dollars

calculations.

The Cost of Goods Sold (cells C19, D19, C25, and D25) is the Total Sales Dollars multiplied by the Cost

of Goods Sold as a percent of Sales. In cell C19, type =C18*C20 and press Enter. Copy cell C19 and paste the

contents into cells D19, C25, and D25. Your answers will be $0 until you enter the formulas for the Cost of

Goods Sold as a percent of Sales.

Cost of Goods Sold as a percent of Sales (cells C20, D20, C26, and D26) was 70% in 2011. In variety

merchandising for resold items, it is easier to use an aggregate measure such as Cost of Goods Sold as a percent of

Sales rather than trying to capture an individual Cost of Goods Sold for each item. From the 2011 data, you

determined that for every dollar of sales you collected in 2011, you spent 70 cents purchasing the item for

resale. You will use that percentage as a basis for forecasting Cost of Goods Sold as a percent of Sales,

applying an appropriate inflation factor for the cost of acquiring the stock for sale. The following table lists the

predicted inflation percentages for Cost of Goods Sold.

Cost of Goods Sold Forecast—Collegetown Thrift Shop

Business Expansion

No Business Expansion

High Inflation

6%

6%

Low Inflation

2%

2%

As with Total Sales Dollars previously, you will again use the IF function to calculate the Cost of Goods

Sold as a percent of Sales. Now that you are familiar with the IF function, you can probably enter the function

without using the dialog boxes. In cell C20, type the following:

=IF($C$9=

“

H

”

,B20*1.06,B20*1.02)

This expression means that if the text string in cell C9 is the letter H, you multiply the value in cell B20

by 1.06 (6% inflation). If the value in cell C9 is not an H, multiply the value in cell B20 by 1.02 (2% inflation).

The value in cell B20 was the baseline Cost of Goods Sold as a percent of Sales in 2011, which was 70%. You

can now copy cell C20 and paste the contents into cell D20.

Because the inflation percentages were exactly the same for both the Expansion and No Expansion

calculations, you can also copy cell C20 and paste the contents into cells C26 and D26. Your Calculations sections

should now look like Figure C-18.