You will learn the following spreadsheet skills:

Absolute cell reference $($Absolute addressing$)$

Relative cell reference

SUM function

IF function

Simple formulas e$.$g$.+,-,{?}^{**}\frac{,}{?}$

Questions$/$Problems

For the problems below, write Excel formula in cells C$7$ to F$34$ 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 C$7$ to F$34$ 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\mathrm{.}00($in cells A $40$

and B$40),$ and the pro forma income statements need to forecast the net income for years

$2004$ to $2006$ based on year $2003\text{'}$s figures. The following assumptions are made $($see

cells A$42$ to F$55)$:

Tax rate for each year is $35\%($cell F$46).$

Sales for 'Deli' are $5\%$ of total sales for each year $($cell B$43).$

Sales for 'Dairy' are $20\%$ of total sales for each year $($cell B$44).$

Sales for 'Canned Goods' are $15\mathrm{.}5\%$ of total sales for each year $($cell B$45).$

Sales for 'Frozen Foods' are $18\%$ of total sales for each year $($cell B$46).$

Sales for 'Meats' are $21\%$ of total sales for each year $($cell B$47).$

Sales for 'Produce' are $12\mathrm{.}5\%$ of total sales for each year $($cell B$48).$

Sales for 'Dry Goods' are $8\%$ of total sales for each year $($cell B$49).$

Cost of Goods sold $($CGS$)$ for 'Deli' is $50\%$ of deli sales each year $($cell C$43).$

Cost of Goods sold $($CGS$)$ for 'Dairy' is $50\%$ of dairy sales each year $($cell C$44).$

Cost of Goods sold $($CGS$)$ for 'Canned Goods' is $75\%$ of canned goods sales each

year $($cell C$45).$

Cost of Goods sold $($CGS$)$ for 'Frozen foods' is $65\%$ of frozen food sales each year

$($cell C$46).$

Cost of Goods sold $($CGS$)$ for 'Meats' is $50\%$ of meat sales each year $($cell C$47).$

Cost of Goods sold $($CGS$)$ for 'Produce' is $65\%$ of produce sales each year $($cell

C$48).$

Cost of Goods sold $($CGS$)$ for 'Dry goods' is $66\%$ of dry good sales each year $($cell

C$49).$

Operating expense for 'sales & marketing' is $5\mathrm{.}5\%$ of total sales each year $($cell

B$52).$

Operating expense for 'general & administrative' is $8\mathrm{.}75\%$ of total sales each year

$($cell B$53).$

Depreciation is $$20,000\mathrm{.}00$ per year $($cell B$54).$

Salaries information

Mr$.$ Leory employs a store manager, an assistant manger and $15$ full$-$time employees $($cell

F$55).$ The manager and assistant manager are paid yearly salaries $($cells F$52,$ F$53),$ and

the $15$ employees are paid an hourly wage, $$10$ per hour $($cell B$55).$ 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 F$51)$ 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\mathrm{.}25\%($cell F$43).$

Growth rate for $2005$ is $7\mathrm{.}75\%($cell F$44).$

Growth rate for $2006$ is $8\mathrm{.}25\%($cell F$45).$