Description

Due in Canvas Drop Box by due date Weight: 5% of the final grade

Instructions:  Build the initial worksheet then using that as a basis do each scenario on a separate tab of 1 Excel worksheet.  

Bridger’s Department store is a medium-sized department store in the Pensacola area. James Bridger is the owner/manager of a local department store.  As an intern at the store James would like you to develop a series of spreadsheets in Excel to help him in his long-term management of the store.

He has provided you with the following table of the markup percentages that are used in each department. The markups shows are based on the wholesale price.

Department Markup Wholesale

Clothing 115% 359,000

Shoes 100% 200,000

Furniture 250% 375,000

Grocery -staples 8% 475,000

Grocery -indulgence 25% 175,000

Housewares 100% 358,000

Cosmetics 75% 190,000

Electronics 300% 410,000

Books 250% 325,000

The other costs that he has identified are:

1) Hourly salary for 20 workers, 40 hours/week, $10.75/hour, 50 weeks/year

2) Salaried workers, 3 @ $35,000 year

3) Benefits are assumed to be 50% of the total of hourly and salaried workers  

4) Marketing is assumed to be 10% of total sales

5) Administrative/overhead expenses are assumed to be 16% of sales

6) Income taxes are 26%

James would like a Pro Forma Income Statement for last year (2020), the current year 2020, and two years in the future. He would like the spreadsheet divided into two areas. The first is the Pro Forma Income statement itself. There should be no numbers in this area.  Everything should be in the form of a formula or a reference to a cell.  The second area is the input or data entry area. This area is where all the constants are entered, all the assumptions, and any other items that might change will also be entered. This design allows James to alter the inputs to the Pro Forma Income Statement and to see what happens to the different categories.

For this initial spreadsheet James has estimated the following growth rate:

Estimated Growth rate for 2021 5.00%

Estimated Growth rate for 2022 6.00%

Estimated Growth rate for 2023 7.00%

Estimated Growth rate for Wholesale 3.00%

James has provided a rough guideline of how he wants the spreadsheet to be organized. Along with the example worksheet he also provided the following guidance:

1) The title should be centered and in a larger font than the rest of the spreadsheet.

2) The title and subtitle should be in a single cell that spans the relevant section.

3) Column headers should be centered, numbers should be right justified, and titles should be left justified. Subheadings should be indented where appropriate. 

4) Section headers should be in a bold font.

5) Numbers should be formatted with a comma but no dollar sign and no decimal places.

6) Negative values should be in red with parenthesis around them.

7) Percentages should be displayed with 2 decimal places and a percent sign.

8) A formula should be used for all totals.

9) A function should be used to prevent negative numbers in Income Tax.

10) The Net Income and Clothing (under Retail Sales) rows should have a $ before each value.

11) No constants should be used in the Pro Forma portion of the spreadsheet.  

After the initial worksheet is complete with all the values input into it, James 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 2% per year?

Scenario 2: Due to a flu pandemic it is estimated that the growth rate for 2021 will be a negative 20% with the estimated growth rate for 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 2021 will be a negative 20% with the estimated growth rate for 2021 to 2022 expected to be 38%.

Scenario 4: James 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 rate increasing from 26% 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?

Instruction Files