Question

Your company is a global seller or home furnishings called Worldwide Home Stuff Unlimited (WHSU). (Yes, they need some more creative people in their company.) Complete a seven-year planning model for WHSU for the period 2016 through 2022. Use the structure shown at the end of this assignment. Proceed as follows:

1. Take the 2016, 2017, and 2018 values from the data at the end of this assignment. Enter the ACTUAL VALUES even for the various lines that can be calculated from other lines (e.g., the Gross Profit or the EBT).

2. Place all growth rates and other input variables at the top left corner of the worksheet. Use formulas and/or functions to perform all necessary calculations.

   Important Note: Most or all of the growth factors and other input values you will be using in this model are calculated in steps 3 through 7 below. So put the formulas for calculating these values in the appropriate cells at the top left corner of the worksheet.

3. Starting with 2019 and beyond, for the following line-items (a thru d below), assume a constantPERCENTAGE growth from one year to the next—e.g., from 2018 to 2019. That percentage change is equal to the Average Annual Percentage Change from 2016 to 2018. Calculate this value by averaging the percentage change from 2016 to 2017 and the percentage change from 2017 to 2018.

   1. Net Sales/Sales Revenue

   2. Selling, General, and Administrative (SG&A)

   3. Depreciation and Amortization

   4. Other Expenses

4. Starting with 2019 and beyond, assume that Advertising will change by the same dollar amount (not the same percentage) from one year to the next—e.g., from 2018 to 2019. That amount is equal to the Average Annual Change (in dollars) between 2016 and 2018.

5. Starting with 2018 and beyond, assume that Rent Expense will be unchanged (that is, constant) from one year to the next, so the values in 2019 through 2022 will be the same as the 2018 value.

6. Assume that the Cost of Goods Sold (CGS) as a percentage of Net Sales/Sales Revenue (that is, the ratio of CGS to Net Sales) will be constant in years 2018 through 2022 and equal to the percentage in 2018. You will need to calculate that percentage (ratio).

7. Assume that the tax rate will be constant in years 2018 through 2022 and equal to the tax rate in 2018. You will need to calculate that value (that is, the tax expense as a percentage of the EBT).

2

8. Note that your formulas should allow for the possibility that your company may lose money in any given year (whether or not it is not the case with the current data).

9. Be sure to note somewhere on the spreadsheet that all figures are in millions.

10. Format financial data with commas (but no decimal places), using dollar signs only for the Net Sales/Sales Revenue, Gross Profit, Total Expenses, Earnings Before Taxes, and Net Income lines. Format growth rates as percentages. Properly format all columns and numbers.

11. When creating the spreadsheet, be sure to copy cell formulas rather than entering similar formulas many times (for example, you can use the autofill handle to copy cell formulas from year to year).

12. Use Excel to place a footer on your spreadsheet with your last name and section (e.g., Jones—INSY 2299 RZQ—where you substitute your last name for Jones and your section for RZQ).

Be sure to follow these instructions carefully!

	2016	2017	2018	2019	2020	2021	2022
REVENUE
Net Sales/Sales Revenue	$29,241	$32,567	$34,444
Cost of Goods Sold (CGS)	11,634	16,600	21,200
Gross Profit	$17,607	$15,967	$13,244
EXPENSE
Selling, General, and Administrative (SG&A)	1,250	1,450	2,210
Advertising	1,250	1,100	1,675
Depreciation and Amortization	3,266	3,482	3,300
Rent Expense	1,880	1,880	1,880
Other Expenses	3,130	3,200	3,350
Total Expenses	$10,776	$11,112	$12,415
Earnings before Taxes (EBT)	$6,831	$4,855	$829
Tax Expense	$2,134	$1,265	$220
Net Income	$4,697	$3,590	$609

Answer 1

		1	2	3=(1+2)/2	4=3*(1+av.)	5=4*(1+av.)	6=5*(1+av.)
		(2017-2016)/2016	(2018-2017)/2017	Average	2019*(1+av.)	2020*(1+av.)	2021*(1+av.)
Net Sales/Sales Revenue		11.37%	5.76%	8.57%
Selling, General, and Administrative (SG&A)		16%	52%	34.21%
Depreciation and Amortization		6.61%	-5.23%	0.69%
Other Expenses		2.24%	4.69%	3.46%
Advertising(change by av. $ value)		-150	575	212.50
Rent expense				same as 2018	S/as 2018	S/as 2018	S/as 2018
COGS			61.55%	61.55%*sales	61.55%	61.55%	61.55%
Tax Rate			26.54%	26.54%	26.54%	26.54%	26.54%
	2016	2017	2018	2019	2020	2021	2022
REVENUE
Net Sales/Sales Revenue	$29,241	$32,567	$34,444	$37,395.85	$40,600.68	$44,080.15	$47,857.82
Cost of Goods Sold (CGS)	11,634	16,600	21,200	23017.15	24989.72	27131.33	29456.49
Gross Profit	$17,607	$15,967	$13,244	$14,378.70	$15,610.96	$16,948.82	$18,401.33
EXPENSE
Selling, General, and Administrative (SG&A)	1,250	1,450	2,210	2966.04	3980.72	5342.53	7170.21
Advertising	1,250	1,100	1,675	1,887.5	2,100.0	2,312.5	2,525.0
Depreciation and Amortization	3,266	3,482	3,300	3322.77	3345.70	3368.78	3392.03
Rent Expense	1,880	1,880	1,880	1,880	1,880	1,880	1,880
Other Expenses	3,130	3,200	3,350	3465.91	3585.83	3709.90	3838.26
Total Expenses	$10,776	$11,112	$12,415	13522.22	14892.25	16613.71	18805.50
Earnings before Taxes (EBT)	$6,831	$4,855	$829	$856.48	$718.71	$335.11	($404.17)
Tax Expense	$2,134	$1,265	$220	$227.31	$190.75	$88.94	0
Net Income	$4,697	$3,590	$609	$629.17	$527.96	$246.17	($404.17)