Using the data entry area, complete the pro forma income statement using excel.

DAndre would like you to run several what if scenarios. Each one should start from the initial worksheet and be placed on its own tab. The tab should be given the name of the scenario that is on it.

Scenario 1: What is the impact if the estimated growth rate of the business for the four-year period is a flat 5% with wholesale costs only increasing 3% per year?

Scenario 2: Due to a flu pandemic it is estimated that the growth rate for 2020 to 2021 will be a negative 30% with the estimated growth rate for 2021 to 2022 remaining at 8%. How will this change impact net profit?

Scenario 3: Due to a flu pandemic it is estimated that the growth rate for 2020 to 2021 will be a negative 30% with the estimated growth rate for 2021 to 2022 expected to be 38%.

Scenario 4: DAndre would like a pie chart of 2022 estimated sales. The title of the chart should be 2022 Sales. The percent of sales should be displayed for each department, and there should be a legend.

Scenario 5: Due to a change in the law, the hourly rate for employees has increased to $15.00 per hour.

Scenario 6: There is a possibility of an increase in business license fees, utility costs, and insurance costs that will increase the overhead percentage to 20%. How would these changes impact the net profit?

Scenario 7: There has been a change in the Federal Income Tax rates reducing the rate from 35% to 29%. How will this change impact net profit?

Scenario 8: If the large discount clothing store opens in the neighborhood, the markup on clothing will have to be reduced to 75%. What impact would this decrease in markup have on net profit?

2022 10% 16% % Departmernt Clothing Shoes Furniture Grocery staples Grocery-indulgence Housewares Cosmetics Electronics Books D'Andre's Department store Data Entry Area Wholesale Cost Markup 359,000 115% Deprecation 200,000 100% Marketing % of Sales 375,000 250% Overhead % of Sales 475,000 8% Hourly Workers 175,000 25% Number of workers 358,000 100% Rate per hour 190,000 75% Hours per week 410,000 300% Weeks per year 525,000 250% Salaried Workers Number of workers Rate per year Benefits rate Income Tax Rate 20 10.75 40 50 3 35,000 50% 35% 1 1 D'Andre's Department store 2 Pro Forma Income Statement 3 3 2019 2020 2021 4 Retail Sales 5 Clothing 6 Shoes 7 Furniture 8 8 Grocery staples 9. Grocery-indulgence 10 Housewares 11 Cosmetics 12 Electronics 13 Books 14 Total Sales 15 16 Wholesale Costs 17 Clothing 18 Shoes 19 Furniture 20 Grocery staples 21 Grocery-indulgence 22 Housewares 23 | Cosmetics 24 Electronics 25 Books 26 Total Wholesale 27 28 29 Gross Profit 30 31 Operating Expenses 32 Hourly Workers 33 Salaried Workers 34 Benefits 35 Marketing 36 Overhead 37 Total Expenses 38 Est Growth rate 19-20 Est Growth rate 20-21 Est Growth rate 21-22 6.00% 7.00% 8.00% Est Growth rate Wholesale 5.00% 39 Income before Taxes 40 Income Taxes 41 Net Income 42 43 2022 10% 16% % Departmernt Clothing Shoes Furniture Grocery staples Grocery-indulgence Housewares Cosmetics Electronics Books D'Andre's Department store Data Entry Area Wholesale Cost Markup 359,000 115% Deprecation 200,000 100% Marketing % of Sales 375,000 250% Overhead % of Sales 475,000 8% Hourly Workers 175,000 25% Number of workers 358,000 100% Rate per hour 190,000 75% Hours per week 410,000 300% Weeks per year 525,000 250% Salaried Workers Number of workers Rate per year Benefits rate Income Tax Rate 20 10.75 40 50 3 35,000 50% 35% 1 1 D'Andre's Department store 2 Pro Forma Income Statement 3 3 2019 2020 2021 4 Retail Sales 5 Clothing 6 Shoes 7 Furniture 8 8 Grocery staples 9. Grocery-indulgence 10 Housewares 11 Cosmetics 12 Electronics 13 Books 14 Total Sales 15 16 Wholesale Costs 17 Clothing 18 Shoes 19 Furniture 20 Grocery staples 21 Grocery-indulgence 22 Housewares 23 | Cosmetics 24 Electronics 25 Books 26 Total Wholesale 27 28 29 Gross Profit 30 31 Operating Expenses 32 Hourly Workers 33 Salaried Workers 34 Benefits 35 Marketing 36 Overhead 37 Total Expenses 38 Est Growth rate 19-20 Est Growth rate 20-21 Est Growth rate 21-22 6.00% 7.00% 8.00% Est Growth rate Wholesale 5.00% 39 Income before Taxes 40 Income Taxes 41 Net Income 42 43