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

1. Take 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 constant percentage 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!

2017	2018	2019	2020	2021	2022	2023
REVENUE
Net Sales/Sales Revenue	13611	14196	15952
Cost of Goods Sold	775	998	1298
Gross Profit	12836	13198	14654
EXPENSE
Selling, General, and Administrative	1418	1518	1618
Advertising	845	801	1235
Depreciation and Amortization	33	86	67
Rent Expense	89	89	89
Other Expenses	130	200	350
Total Expenses	2515	2694	3359
Earnings before Taxes (EBT)	10321	10504	11295
Tax Expense	3612	3676	3953
Net Income	6709	6828	7342

Answer rating: 100% (QA)

Particulars 2017 2018 2019 2020 2021 2022 Formulas REVENUE Net SalesSales Revenue 13611 14196 15952 ... View the full answer