Microsoft Excel

CMIS 342

Excel Case #2: Maxi’s Grocery Mart1
Pro Forma Income Statement Case Description and Instructions
SKILLS CHECK

You should review the following areas:

SPREADSHEET SKILLS

· Absolute and Relative Cell References
· Protecting Cells
· Cell and Worksheet Formatting
· Range Names
· Consolidating Worksheets
· SUM, IF, @NOW, and other Functions
· Complex Formulas, including Present Value

Case Overview
Since its opening almost 50 years ago, Maxi’s Grocery Mart has continued to grow and evolve with the times. The family-owned business has survived many ups and downs and is currently experiencing a modest growth in business. Leroy Feronti, the current owner, wants to expand his family’s business by renovating the grocery mart building. Mr. Feronti needs your help in evaluating the economic viability of the project and preparing documents required for procuring a bank loan. To do the first part, you created an Economic Feasibility workbook (Excel Case
#2). The purpose of that workbook was to summarize and analyze the benefits and costs associated with the proposed renovation project. The preparation of an Economic Feasibility workbook required you to design several worksheets, use a variety of formulas and functions, use basic cell and worksheet formatting, consolidate data from multiple worksheets into a summary worksheet, and produce a breakeven analysis that includes a line chart.

Now that that work is completed, Mr. Feronti will need to arrange financing for the project. While Mr. Feronti has some personal funds available, he will need to procure a loan from the local bank. Before approaching the local bank, he would like to prepare and review several pro forma financial statements (Excel Case #2). If Mr. Feronti decides to go forward with the renovation, he will use the pro forma financial statement as part of his loan application; and he asks you to prepare it for him. Preparation of the pro forma income statement requires you to design a worksheet with input and information sections, properly format the worksheet, construct formulas, perform what-if analysis, and generate a chart.

1 This case is based on Lisa Miller (2009), MIS Cases Decision Making with Application Software, Fourth Edition. Pearson Prentice Hall; Upper Saddle River, New Jersey.

CMIS 342

Revised: 05/04/2021
Excel Case #1B – Maxi’s Part 2 Pro Forma Page 11 of 11

Scenario Details for Maxi’s Part 2 – Pro Forma Income Statement

Maxi’s Grocery Mart is a family-owned business that has been in operation since the 1960s. Although Leroy Feronti is very active with his business, he does employ a store manager, assistant manager, and 17 full-time employees. The store manager and assistant manager are paid a salary, and the employees are paid an hourly wage. Each employee works 40 hours a week, 50 weeks a year.

Having recently assumed ownership of the business from his parents, Mr. Feronti feels that one of the keys to the business’s continued success is the renovation of the grocery’s building.
Renovating the existing building will cost approximately $400,000. Mr. Feronti must borrow
$300,000 from the local bank and will use income generated from the grocery to repay the loan. Mr. Feronti asks you to prepare a set of pro forma financial statements for him. He will use these statements to analyze his business. If he decides to pursue the renovation project, he will use the pro forma statements as part of his loan application.

Figure 1: Maxi’s Food Mart Income Statement Outline

2020

2021

2022

2023

2024

2025

Sales

Deli

Assume 5 percent of total sales each year.

Dairy

Assume 19 percent of total sales each year.

Canned Goods

Assume 10 percent of total sales each year.

Frozen Foods

Assume 22 percent of total sales each year.

Meats

Assume 21 percent of total sales each year.

Produce

Assume 12.5 percent of total sales each year.

Dry Goods

Assume 9 percent of total sales each year.

DVD Sales

Assume 1.5 percent of total sales each year.

Total Sales

Assume $4,000,000 in total sales for 2016.

Cost of Goods Sold

Deli

Assume 50 percent of deli sales each year.

Dairy

Assume 50 percent of dairy sales each year.

Canned Goods

Assume 75 percent of canned goods sales each year.

Frozen Foods

Assume 65 percent of frozen food sales each year.

Meats

Assume 50 percent of meat sales each year.

Produce

Assume 65 percent of produce sales each year.

Dry Goods

Assume 66 percent of dry good sales each year.

DVD Sales

Assume 30 percent of DVD sales each year.

Total Cost of Goods Sold

Gross Profit

Operating Expenses

Sales and Marketing

Assume 5.5 percent of total sales each year.

General and Administrative

Assume 8.75 percent of total sales each year.

Depreciation

Assume $20,000 per year.

Wages

Includes the employees’ wages, store manager’s salary, and assistant manager’s salary.

Common Costs

Mr. Feronti’s salary.

Total Operating Expenses

Income Before Taxes

Income Taxes

Net Income

Mr. Feronti asks you to use the income statement outline shown in Figure 1 and use the grocery mart’s 2020 sales as the base period. You will use the 2020 sales to estimate Mr. Feronti’s sales, cost of goods sold, expenses, taxes, and net income for the next three years. When preparing the pro forma income statement, several assumptions and additional information are necessary.
Figure 2 provides these assumptions and additional information.

Figure 2: Assumptions and Additional Information

Growth & Tax Rates

Salaries & Wages

2021 Growth: 2.5 percent

Mr. Feronti: 12 percent of gross profit

2022 Growth: 5.0 percent

Store Manager: $57,000

2023 Growth: 5.0 percent

Assistant Manager: $42,000

2024 Growth: 4.0 percent

Employee Hourly Wage: $13.00

2025 Growth: 3.0 percent

Tax Rate: 35 percent

Design Considerations
As Mr. Feronti will use the pro forma income statement as part of his loan application, he requests that it have a consistent, professional, and well-organized appearance. Mr. Feronti specifically requests that you include an appropriate header and apply proper formatting to the cells and worksheet.

Using Figures 1 and 2 as guides, you decide that the worksheet requires both input and information sections. Figure 1 provides an outline and guidelines for constructing the information section, and Figure 2 provides the necessary data for the input section. By creating separate sections, it is easy for Mr. Feronti to not only view the input data to his income statement, but also, if necessary, change the parameters, thus facilitating his decision-making activities.

The information section contains the pro forma income statement, and this section provides Mr. Feronti with information about his projected sales, cost of goods sold, operating expenses, and net income for years 2021 – 2025. The information section uses the grocery mart’s 2020 sales as the basis for these projections. You make sure that, where appropriate, the information section formulas reference the cell values contained in the input section.

As you study Figure 1, you realize that Mr. Feronti wants his store item sales, cost of goods sold, and operating expenses expressed as a percentage of total sales. To facilitate Mr. Feronti’s analysis, you place the total sales value in the input section, along with the other assumptions. By doing this, your formulas in the information section can reference the actual total sales amount.
As you study Figure 2, you notice that Mr. Feronti’s salary is 12 percent of gross profit. Since Mr. Feronti only draws his salary if the grocery mart makes a profit, you must build this logic into the income statement. You do so by using the IF function. To keep the information section’s formulas from accidentally being updated, you protect the cells in the information section.

Mr. Feronti wants the input and information sections printed on separate pages. For each section’s printout, he wants the results printed on a single page. The printouts should utilize a portrait orientation and be centered horizontally and vertically.

Information Specifications
Mr. Feronti asks you to use the information provided in Figures 1 and 2 (above) to create a Pro Forma Income Statement. He will use this in support of his financing application to fund the renovation project.

Implementation Concerns
The preparation of this case requires you to apply basic spreadsheet construction concepts. Since Mr. Feronti will change the input values during his decision-making activities, you should have a separate input section for the input values. Keep in mind that the formulas in the information section will reference the input cells. You should use absolute and relative cell references, as opposed to constant values.

Case Questions and Deliverables
Instructions begin on the next page for completing this Excel Case #2. In addition to an Excel Workbook, you may be asked to respond to specific questions that could require interpreting outputs of your workbook, performing additional analyses by modifying key inputs, creating new charts or graphs, or adding additional worksheets.

CMIS 342

Excel Case #2: Maxi’s Grocery Mart Part 2 – Pro Forma Income Statement

Instructions

Be sure to begin this assignment using the “starter file” from Blackboard!
Download and save a copy of that file to begin your work.

Case Background and Scenario: Read the background information about Excel Case #2: Maxi’s Grocery Mart (above) to grasp the business situation you are to address. This third Excel assignment requires you to use and expand your Excel skills to address Mr. Feronti’s request for a PRO FORMA INCOME STATEMENT.

Design Specifications:

First, general formatting of the worksheet (see Figure 3 following):

1. Add formatting to the sheet provided in the starter file:
a. Indent the text in the first column of the detail lines of both the Pro Forma Income Statement section and the Assumptions section. See Figure 3 (below) if you are not sure what we mean by detail lines. Increase the Indent one click to get a horizontal left indent in the first columns of the detail lines of both sections in the sheet.
b. In the (blank) third line of the heading area of the Pro Forma Income Statement section, insert a heading that reads “Prepared on: current date”. HINT: Use 2
cells: one right-justified saying “Prepared on:” and the other left-justified showing the current date.
c. Insert a page break below the Pro Forma Income Statement so the Assumptions section will print on a separate page, as requested by Mr. Feronti. HINT: Use Help within Excel (the ? icon) for help on how to insert a page break in an Excel spreadsheet.

Pro Forma Income Statement

 

 

 

 

Year #

0

1

2

3

4

5

 

2020

2021

2022

2023

2024

2025

Sales

 

 

 

 

 

 

Deli

 

 

 

 

 

 

Dairy

 

 

 

 

 

 

Canned Goods

 

 

 

 

 

 

Frozen Foods

 

 

 

 

 

 

Meats

 

 

 

 

 

 

Produce

 

 

 

 

 

 

Dry Goods

 

 

 

 

 

 

DVD Rentals

 

 

 

 

 

 

Total Sales

 

 

 

 

 

 

Cost of Goods Sold

 

 

 

 

 

 

Deli

 

 

 

 

 

 

Dairy

 

 

 

 

 

 

Canned Goods

 

 

 

 

 

 

Frozen Foods

 

 

 

 

 

 

Meats

 

 

 

 

 

 

Produce

 

 

 

 

 

 

Dry Goods

 

 

 

 

 

 

DVD Rentals

 

 

 

 

 

 

Total Cost of Goods Sold

 

 

 

 

 

 

Gross Profit

 

 

 

 

 

 

Operating Expenses

 

 

 

 

 

 

Sales and Marketing

 

 

 

 

 

 

General and Administrative

 

 

 

 

 

 

Depreciation

 

 

 

 

 

 

Wages

 

 

 

 

 

 

Common Costs

 

 

 

 

 

 

Total Operating Expenses

 

 

 

 

 

 

Income Before Taxes

 

 

 

 

 

 

Income Taxes

 

 

 

 

 

 

Net Income

 

 

 

 

 

 

 

 

 

 

 

 

 

Assumptions

 

 

 

 

 

 

 

 

2020

2021

2022

2023

2024

2025

Base Period Sales

 

 

 

 

 

Annual Sales Growth Rates

n/a

 

 

 

 

 

 

 

 

 

 

 

Store Items

% of Sales

CGS

 

Rates

 

Deli

 

 

Tax

 

 

Dairy

 

 

 

 

 

 

Canned Goods

 

 

 

 

 

 

Frozen Foods

 

 

 

 

 

 

Meats

 

 

 

 

 

 

Produce

 

 

 

 

 

 

Dry Goods

 

 

 

 

 

 

DVD Rentals

 

 

 

 

 

 

 

100.00%

 

Salaries

 

 

 

Operating Expenses

 

 

Leroy

 

 

 

Sales and Marketing

 

 

Manager

 

 

 

General and Administrative

 

 

Asst. Manager

 

 

 

Depreciation Expense

 

 

 

 

 

 

Wages

 

 

No. of Employees

 

 

 

Next, let’s create the Assumptions section of the worksheet (see Figure 4 following):

2. Use the data in Figure 1 to enter the percentages for “% of Sales” and Cost of Goods Sold (“CGS”) for the Store Items.
3. Next, enter the “Operating Expenses” section values in the Assumptions section of the sheet. The first three values come from Figure 1. When you get to “Wages” you will need to reference the Scenario Details and Figure 2 to know the number of
employees, the wage paid to employees, and salary information for Leroy and the managers.
4. Back toward the top of the Assumptions section, enter the Tax Rate and enter the Annual Sales Growth Rates for years 2021 to 2025. These are provided in Figure 2.
5. The last piece to fill in the Assumptions are the Base Period Sales values. The Base Period Sales for 2020 were stated by Mr. Feronti in Figure 1; enter that value for 2020. Then you will develop a formula to calculate the Base Period Sales for years 2021 to 2025. Note that this is the only formula needed in the Assumptions section! And it is not a difficult one. For years 2021-2025, Base Period Sales for each year is just the Base Period Sales of the prior year + (the year’s projected growth rate * the Base Period Sales of the prior year). If you construct this formula correctly for 2021, then you can simply copy (drag) that formula to the other years.

Figure 4: Completed Assumption Section

 

 

 

 

 

 

 

Assumptions

 

 

 

 

 

 

 

 

2020

2021

2022

2023

2024

2025

Base Period Sales

$4,000,000.00

$ 4,100,000.00

$ 4,305,000.00

$ 4,520,250.00

$ 4,701,060.00

$ 4,842,091.80

Annual Sales Growth Rates

n/a

2.50%

5.00%

5.00%

4.00%

3.00%

 

 

 

 

 

 

Store Items

% of Sales

CGS

 

Rates

 

Deli

5.00%

50.00%

Tax

35.00%

 

Dairy

19.00%

50.00%

 

 

 

 

Canned Goods

10.00%

75.00%

 

 

 

 

Frozen Foods

22.00%

65.00%

 

 

 

 

Meats

21.00%

50.00%

 

 

 

 

Produce

12.50%

65.00%

 

 

 

 

Dry Goods

9.00%

66.00%

 

 

 

 

DVD Rentals

1.50%

30.00%

 

 

 

 

 

100.00%

 

Salaries

 

 

 

Operating Expenses

 

 

Leroy

12.00%

 

 

Sales and Marketing

5.50%

 

Manager

$57,000.00

 

 

General and Administrative

8.75%

 

Asst. Manager

$42,000.00

 

 

Depreciation Expense

$20,000.00

 

 

 

 

 

Wages

$13.00

 

No. of Employees

17

 

 

Now you are ready to create the formulas in the Pro Forma information section of the worksheet (see Figure 5 following). These will reference the data you have in the Assumptions section:

6. Create the formulas in the Pro Forma Income Statement section of the sheet, referencing values in the Assumptions section as appropriate. In case you have forgotten your accounting classes, basic calculations are provided on the last page of these instructions.
a. Be sure to use relative, absolute and mixed cell addresses as appropriate. If you create your formulas correctly for 2020, you will be able to copy (drag) them across for 2021 through 2025. AND you will be able to copy (drag) these formulas DOWN; for example, drag the Deli 2020 formula down through DVD Sales. So think hard as you create each formula, then drag to copy and check if your results match what you see in my results.
b. Don’t forget to AutoSum to fill in the Total lines of the Pro Forma statement.
c. In computing Wages, you will need to refer to the Scenario Details (page 2 above) to determine the annual hours worked by wage-hour employees.

CMIS 342
d. In calculating Common Costs, you will need to use the IF function. Use Excel Help (the ? icon) if you need help constructing the IF function. Make sure the resulting value in this cell is formatted as currency.

Figure 5: Completed Pro Forma Income Statement

Maxi’s Grocery Mart

Pro Forma Income Statement

 

 

 

 

Year #

0

1

2

3

4

5

 

2020

2021

2022

2023

2024

2025

Sales

 

 

 

 

 

 

Deli

$200,000.00

$205,000.00

$215,250.00

$226,012.50

$235,053.00

$242,104.59

Dairy

$760,000.00

$779,000.00

$817,950.00

$858,847.50

$893,201.40

$919,997.44

Canned Goods

$400,000.00

$410,000.00

$430,500.00

$452,025.00

$470,106.00

$484,209.18

Frozen Foods

$880,000.00

$902,000.00

$947,100.00

$994,455.00

$1,034,233.20

$1,065,260.20

Meats

$840,000.00

$861,000.00

$904,050.00

$949,252.50

$987,222.60

$1,016,839.28

Produce

$500,000.00

$512,500.00

$538,125.00

$565,031.25

$587,632.50

$605,261.48

Dry Goods

$360,000.00

$369,000.00

$387,450.00

$406,822.50

$423,095.40

$435,788.26

DVD Rentals

$60,000.00

$61,500.00

$64,575.00

$67,803.75

$70,515.90

$72,631.38

Total Sales

$4,000,000.00

$4,100,000.00

$4,305,000.00

$4,520,250.00

$4,701,060.00

$4,842,091.80

Cost of Goods Sold

 

 

 

 

 

 

Deli

$100,000.00

$102,500.00

$107,625.00

$113,006.25

$117,526.50

$121,052.30

Dairy

$380,000.00

$389,500.00

$408,975.00

$429,423.75

$446,600.70

$459,998.72

Canned Goods

$300,000.00

$307,500.00

$322,875.00

$339,018.75

$352,579.50

$363,156.89

Frozen Foods

$572,000.00

$586,300.00

$615,615.00

$646,395.75

$672,251.58

$692,419.13

Meats

$420,000.00

$430,500.00

$452,025.00

$474,626.25

$493,611.30

$508,419.64

Produce

$325,000.00

$333,125.00

$349,781.25

$367,270.31

$381,961.13

$393,419.96

Dry Goods

$237,600.00

$243,540.00

$255,717.00

$268,502.86

$279,242.96

$287,620.25

DVD Rentals

$18,000.00

$18,450.00

$19,372.50

$20,341.13

$21,154.77

$21,789.41

Total Cost of Goods Sold

$2,352,600.00

$2,411,415.00

$2,531,985.75

$2,658,585.04

$2,764,928.44

$2,847,876.29

Gross Profit

$1,647,400.00

$1,688,585.00

$1,773,014.25

$1,861,664.96

$1,936,131.56

$1,994,215.51

Operating Expenses

 

 

 

 

 

 

Sales and Marketing

$220,000.00

$225,500.00

$236,775.00

$248,613.75

$258,558.30

$266,315.05

General and Administrative

$350,000.00

$358,750.00

$376,687.50

$395,521.88

$411,342.75

$423,683.03

Depreciation

$20,000.00

$20,000.00

$20,000.00

$20,000.00

$20,000.00

$20,000.00

Wages

$541,000.00

$541,000.00

$541,000.00

$541,000.00

$541,000.00

$541,000.00

Common Costs

$197,688.00

$202,630.20

$212,761.71

$223,399.80

$232,335.79

$239,305.86

Total Operating Expenses

$1,328,688.00

$1,347,880.20

$1,387,224.21

$1,428,535.42

$1,463,236.84

$1,490,303.94

Income Before Taxes

$318,712.00

$340,704.80

$385,790.04

$433,129.54

$472,894.72

$503,911.57

Income Taxes

$111,549.20

$119,246.68

$135,026.51

$151,595.34

$165,513.15

$176,369.05

Net Income

$207,162.80

$221,458.12

$250,763.53

$281,534.20

$307,381.57

$327,542.52

Your Pro Forma Income Statement is complete! When you show it to Mr. Feronti, he wonders:

7. “What if I’m way off on my estimate of 2020 Base Period Sales?” He asks you to show him how the projections would change if 2020 Base Period Sales turned out to be $2,500,000 instead of $4,000,000 (sad face), but also what the projections would be if the 2020 Base Period Sales reached $6,000,000 (happy face!). To do this, you utilize a cool feature of Excel: What-If-Analysis, found within the Data tab.
a. Select the cell in the Assumptions area of your worksheet that shows the assumed

2020 Base Period Sales of $4,000,000.
b. Now in the Forecast area of the Data tab, click the dropdown arrow on What-If- Analysis and select Scenario Manager.
c. You want to create 2 what-if scenarios. So click Add, then name the first scenario “2020 Base Period Sales are $2,500,000.” At the Scenario Value pop-up, enter the value of 2,500,000. Then create the second scenario, naming it “2020 Base Period Sales are $6,000,000” with the corresponding Scenario Value. Feel free to play

around with the Show button in Scenario Manager at this point; but be sure to close Scenario Manager and (manually) change the 2020 Base Period Sales value in the worksheet back to $4,000,000 before proceeding!
d. Now access Scenario Manager again to produce a summary report of those scenarios you created. Select the Summary button (instead of Add) and choose the “Scenario summary” report type with results cells of B37:G37 (i.e., the Net Income for each of years 2020, 2021, 2022, 2023, 2024, and 2025).
e. Admire the Scenario Summary sheet that you just produced! Then:
· Use column A to label the “Results Cells” row as “Net Income” and label the rows that follow by year (2020, 2021, 2022, 2023, 2024, and 2025).
· Feel free to do a little formatting to make this “pretty”:

8. At this point, your Excel file should consist of 2 worksheets named and ordered as follows:
Scenario Summary Pro Forma

Case Deliverables:

Submit your Excel workbook through Blackboard.

Basic calculations for the Pro Forma Income Statement:

Sales and Costs
· Item Sales = Base Period Sales * appropriate percentage
· Cost of Goods Sold =
Item
Dollar Sales * appropriate percentage
· Gross Profit = Total Sales – Total Cost of Goods Sold

Operating Expenses
· Sales and Marketing = Base Period Sales * appropriate percentage
· General and Administrative = Base Period Sales * appropriate percentage
· Depreciation = constant amount from Assumptions
· Wages = (number employees * wage * annual hours worked) + manager salary + assistant manager salary
· Common Costs: You must use the IF function to check if there is a profit (is Gross Profit that year greater than zero?). If there is a profit, Common Costs = Gross Profit * Leroy’s salary percentage. If no profit, Leroy doesn’t draw a salary.

Income
· Income Before Taxes = Gross Profit – Total Operating Expenses
· Income Taxes = Income Before Taxes * constant tax rate from Assumptions
· Net Income = Income Before Taxes – Income Taxes