The Portable MBA in Finance and Accounting, 3rd Edition

159

EXHIBIT 5.3 Spreadsheet formula infrastructur

e. Pro Forma Income Statement

January

February

March

April

May

June

Year to Date

Sales

100,000

125,000

135,000

127,000

132,000

155,000

=SUM(B3:G3)

Cost of goods sold

=$B21*B3

=$B21*C3

=$B21*D3

=$B21*E3

=$B21*F3

=$B21*G3

=SUM(B4:G4)

Gross profit

=B3-B4

=C3-C4

=D3-D4

=E3-E4

=F3-F4

=G3-G4

=SUM(B5:G5)

Operating ExpensesSalaries

=$B22*B3

=$B22*C3

=$B22*D3

=$B22*E3

=$B22*F3

=$B22*G3

=SUM(B8:G8)

Benefits

=$B23*B3

=$B23*C3

=$B23*D3

=$B23*E3

=$B23*F3

=$B23*G3

=SUM(B9:G9)

Rent

=3,200

=3,200

=3,200

=3,200

=3,200

=3,200

=SUM(B10:G10)

Utilities

4,300

4,750

3,790

4,100

3,100

2,800

=SUM(B11:G11)

Advertising

=$B24*B3

=$B24*C3

=$B24*D3

=$B24*E3

=$B24*F3

=$B24*G3

=SUM(B12:G12)

Supplies

1,300

1,400

1,270

1,500

1,550

1,600

=SUM(B13:G13)

Total operating expenses

=SUM(B8:B13)

=SUM(C8:C13)

=SU

M(D8:D13)

=SUM(E8:E13)

=SUM(F8:F13)

=SUM(G8:G13)

=SUM(B14:G14)

Net profit before taxes

=B3-B14

=C3-C14

=D3-D14

=E3-E14

=F3-F14

=G3-G14

=SUM(B16:G16)

Income taxes

=$B25*B16

=$B25*C16

=$B25*D16

=$B25*E16

=$B25*F16

=$B25*G16

=SUM(B17:G17)

Net profit after taxes

=B16-B17

=C16-C17

=D16-D17

=E16-E17

=F16-F17

=G16-G17

=SUM(B18:G18)

AssumptionsCosts of goods sold %

0.3275

Salaries (% sales)

0.228

Benefits (% sales)

0.112

Advertising (% sales)

0.12

Income taxes %

0.25