ISM 3011 - Assignment 1 Weight: 3% of the final grade

Subject:MS ExcelPrice:23.99 Bought3

Share With

ISM 3011 - Assignment 1

Weight: 3% 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.

D’Andre’s Department store is a medium-sized department store in the Pensacola area. D’Andre Tarrant is the owner/manager of a local department store. As an intern at the store D’Andre 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

 

1.            The other costs that he has identified are: 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%.

7.            Straight-line depreciation is $100,000 per year.

 

D’Andre would like a Pro Forma Income Statement for 2022, 2023, and 2024. The last year’s complete year, 2021 should be the base year for the Pro Forma Statement. 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 D’Andre to alter the inputs to the Pro Forma Income Statement and to see what happens to the different categories.

 

 

For this initial spreadsheet D’Andre has estimated the following growth rate:

 

Estimated Sales Growth Rate for 2022    5.00%

Estimated Sales Growth Rate for 2023    6.00%

Estimated Sales Growth Rate for 2024    7.00%

Estimated Growth Rate for Wholesale Costs       3.00%

 

D’Andre 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, D’Andre 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 wholesale costs increased to 8% per year?

•             Scenario 2: Due to a flu pandemic it is estimated that the growth rate for 2022 will be a negative 10% with the estimated growth rate for 2023 and 2024 remaining at 6% and 7% respectively. How will this change impact net profit?

•             Scenario 3: Due to a flu pandemic it is estimated that the growth rate for 2022 will be a negative 10% with the estimated growth rate for 2022 to 2023 expected to be 5%.

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

•             Scenario 5: 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 6: There has been a change in the Federal Income Tax rate increasing from 26% to 25%. How will this change impact net profit?

•             Scenario 7: D’Andre would like a pie chart of 2024 estimated sales. The title of the chart should be 2024 Sales. The percent of sales should be displayed for each department, and there should be a legend.

•             Scenario 8: If a 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?