Microsoft Office Tutorials and References
In Depth Information
Tutorial C: Building a Decision Support System in Excel
Copying cell C18 into cell C24 and then editing the IF function arguments to change the sales growth
As before, you can now copy cell C24 to cell D24. You have completed the Total Sales Dollars
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
No Business Expansion
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:
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.