Lab 2

Information Management - Microsoft Excel Lab. 2

You will learn the following spreadsheet skills:
• Absolute cell reference (Absolute addressing)
• Relative cell reference
• SUM function
• IF function
• Simple formulas e.g. +, -, *, /

Questions/Problems
For the problems below, write Excel formula in cells C7 to F34 of worksheet ‘PFIS’ (104
formula in total to fill in) using as much absolute addressing and copy & paste as possible
WITHOUT editing/correcting the formula. This means: Don’t edit/correct the formula
after the formula are copied & pasted. The reasons are:

There are many different ways to write the Excel formula in cells C7 to F34 and obtain the
same calculated values, but certain formula with appropriate absolute addressing and copy &
paste will enable less typing/editing, and thus saves typing time! The purpose of using Excel is
to be more efficient – saves editing time!

In ‘PFIS’ worksheet, how many formula you typed/edited (at most)? How many formula
you copied and pasted from other formula?

----------------------------------------------------------------------------------------------------------------

Background of company

Mr. Maxi, the current owner of Maxi’s Grocery Mart, wants to renovate the grocery mart’s
existing building. Financing the renovation requires a loan from a local bank. Before
proceeding with the loan application, Mr. Maxi needs several pro forma financial statements
prepared. The pro forma income statements are to be prepared in Microsoft Excel. Please refer
to worksheet ‘PFIS’. This case encourages students to use their general business/accounting
background, and spreadsheet knowledge to prepare a pro forma income statement for Mr. Maxi.

Details of the worksheet ‘PFIS’

It is now end of year 2003 and the total sales for year 2003 is $3500000.00 (in cells A40 and
B40), and the pro forma income statements need to forecast the net income for years 2004 to
2006 based on year 2003’s figures. The following assumptions are made (see cells A42 to F55):

• Tax rate for each year is 35% (cell F46).

• Sales for ‘Deli’ are 5% of total sales for each year (cell B43).
• Sales for ‘Dairy’ are 20% of total sales for each year (cell B44).
• Sales for ‘Canned Goods’ are 15.5% of total sales for each year (cell B45).
• Sales for ‘Frozen Foods’ are 18% of total sales for each year (cell B46).
• Sales for ‘Meats’ are 21% of total sales for each year (cell B47).
• Sales for ‘Produce’ are 12.5% of total sales for each year (cell B48).
• Sales for ‘Dry Goods’ are 8% of total sales for each year (cell B49).

Page 1 of 3
 Lab 2

• Cost of Goods sold (CGS) for ‘Deli’ is 50% of deli sales each year (cell C43).
• Cost of Goods sold (CGS) for ‘Dairy’ is 50% of dairy sales each year (cell C44).
• Cost of Goods sold (CGS) for ‘Canned Goods’ is 75% of canned goods sales each year
(cell C45).
• Cost of Goods sold (CGS) for ‘Frozen foods’ is 65% of frozen food sales each year (cell
C46).
• Cost of Goods sold (CGS) for ‘Meats’ is 50% of meat sales each year (cell C47).
• Cost of Goods sold (CGS) for ‘Produce’ is 65% of produce sales each year (cell C48).
• Cost of Goods sold (CGS) for ‘Dry goods’ is 66% of dry good sales each year (cell C49).

• Operating expense for ‘sales & marketing’ is 5.5% of total sales each year (cell B52).
• Operating expense for ‘general & administrative’ is 8.75% of total sales each year (cell
B53).
• Depreciation is $20,000.00 per year (cell B54).

Salaries information

Mr. Leory employs a store manager, an assistant manger and 15 full-time employees (cell F55).
The manager and assistant manager are paid yearly salaries (cells F52, F53), and the 15
employees are paid an hourly wage, $10 per hour (cell B55). Each employee works 40 hours
per week, 50 weeks a year. Row 29 contains the formula for the yearly total salaries for all the
managers and employees.

Mr. Leory Maxi also works in the mart, and he takes 15% of gross profit (cell F51) as salary if
the gross profit is positive.
Note: Mr. Leory Maxi’s salary is classified as “common costs” in row 30.

Maxi’s Grocery Mart needs to pay income taxes (Row 33) only if income (before taxes) is
positive.

Growth information (Note: Growth rate applies to previous year’s figures, and applies
to everything except wages, common costs, tax and depreciation.)

• Growth rate for 2004 is 7.25% (cell F43).

• Growth rate for 2005 is 7.75% (cell F44).
• Growth rate for 2006 is 8.25% (cell F45).

Page 2 of 3
 Lab 2

Worksheet ‘PFIS’
A B C D E F
1 Maxi's Grocery Mart
2 Pro Forma Income Statement
3
4 2003 2004 2005 2006
5
6 Sales
7 Deli ?? ?? ?? ??
8 Dairy ?? ?? ?? ??
9 Canned Goods ?? ?? ?? ??
10 Frozen Foods ?? ?? ?? ??
11 Meats ?? ?? ?? ??
12 Produce ?? ?? ?? ??
13 Dry Goods ?? ?? ?? ??
14 Total Sales ?? ?? ?? ??
15 Cost of Goods Sold
16 Deli ?? ?? ?? ??
17 Dairy ?? ?? ?? ??
18 Canned Goods ?? ?? ?? ??
19 Frozen Foods ?? ?? ?? ??
20 Meats ?? ?? ?? ??
21 Produce ?? ?? ?? ??
22 Dry Goods ?? ?? ?? ??
23 Total Cost of Goods Sold ?? ?? ?? ??
24 Gross Profit ?? ?? ?? ??
25 Operating Expenses
26 Sales and Marketing ?? ?? ?? ??
27 General and Administrative ?? ?? ?? ??
28 Depreciation ?? ?? ?? ??
29 Wages ?? ?? ?? ??
30 Common Costs ?? ?? ?? ??
31 Total Operating Expenses ?? ?? ?? ??
32 Income Before Taxes ?? ?? ?? ??
33 Income Taxes ?? ?? ?? ??
34 Net Income ?? ?? ?? ??
35
36
37 Assumptions
38
39
40 Total Sales for 2003 $3,500,000.00
41
42 Store Items % of Sales CGS Rates
43 Deli 5.00% 50.00% 2004 Growth 7.25%
44 Dairy 20.00% 50.00% 2005 Growth 7.75%
45 Canned Goods 15.50% 75.00% 2006 Growth 8.25%
46 Frozen Foods 18.00% 65.00% Tax 35.00%
47 Meats 21.00% 50.00%
48 Produce 12.50% 65.00%
49 Dry Goods 8.00% 66.00%
50 100.00% Salaries
51 Operating Expenses Leory 15.00%
52 Sales and Marketing 5.50% Manager $50,000.00
53 General and Administrative 8.75% Asst. Manager $36,000.00
54 Depreciation Expense $20,000.00
55 Wage $10.00 No. of Employees 15

Page 3 of 3