|
|
This assignment prepares a Profit and Loss
(also called Income) statement, the
most common and important financial statement in business.
Perform the following tasks in
the order given:
- Enter column A, rows 1,2, and 3 enter "Revenue", "Cost of Goods
Sold", "Gross Margin" as row labels.
- Adjust the column width.
- In B1, enter a value for as Revenue (this is the money that is
collected for all goods and services sold).
- Format column B with a comma (,) and not pennies. Format B1 with a $ and no pennies
- Enter another value in B2 for "Cost of Good Sold".
(This is the money that MUST be spend to create and sell the Note it comes in with
the correct formatting
- Enter the formula for Gross Margin in cell B3. (Type or click on the =, point to cell
B1, type a minus(-), point to cell B2)
- Change revenue value (increase it by 50,000) Change "Cost of Good
Sold" value. Note the change in Gross
Margin.
- Save the sheet to your disk (UCC hard disk space, floppy or Zip) as "P_AND_L" or "Income
Statement" or whatever name makes sense to you and that you will
remember. Upload to your briefcase later.
- Enter labels for 3 Discretionary (non-operating)
Expenses such as
Interest", "Officer's Bonus" and "Xmas Party" in column A, rows
4, 5, and 6.
- Enter 3 values for the 3 Discretionary (non-operating)
Expenses into B4, B5 and B6.
- Enter label "Total Non Operating Expenses" in cell A7
- Total above 3 amounts using a AutoSum icon (sigma). Adjust which cells (B4, B5 and
B6) are to be summed.
- Enter Net Income as a row label in row 8 and the formula to calculate it in Cell B8.
Net Income is the Gross Profit minus the total Non-Operating Expenses.
- Save the sheet again.
- Enter Taxes as a row label for row 9. Enter B9 using the formula as
23% of Net Income of $25,000 is
Taxes. This requires using the
IF
function. The best way to do this is to
 | rename Sheet2 (on the bottom tab, right click)
Assumptions |
| type TaxRate in to Cell A1 of Assumptions |
| type Taxable Minimum into cell A2 |
| type the current tax rate value (23%) in to call B1
and the minimum taxable amount ($25,000) into call B2 |
| select all 4 cells |
| insert menu name create |
| check that you got the two names using the drop down
on the upper left, just to the left of the
 |
| in cell B9
 | click
|
| select IF |
| create the test which is the Net Income amount is
greater than the Taxable Minimum (either point to it or hit F3) |
| the true is Net Income amount minus Taxable
Minimum value times Tax Rate (add parenthesis as need) |
| the false is 0 (zero) |
|
Enter "Net Inc. After Tax" as a row label for row 10
In column B enter a formula that finds the difference between Net income and Taxes.
Format "Net Inc. After Tax "with a $ (no Pennies)
Save the sheet.
Enter "Number of Shares Outstanding" as a row label and 5,000 in cell B12
Enter "Earnings Per Share" as the next row label and enter a formula for
Earning Per share. (IF there is $x of income, what is each
shareholder's share?)
Format "Earning per Share" with a $ and pennies. Bold label and value in
the row.
Position the Select on row 1 and insert a new row.
Enter Monday in B1 and drag to C1 to E1. See Tuesday, Wednesday.. etc appear.
UNDO
Enter January in B1 and drag to B1, C1, D1, E1 etc. and see February, March etc appear, UNDO
Now for real, enter 1 Qtr 07 as column labels in B1 and drag C1 to
D1 and see 2Qtr 07
appear. Underline the years.
Enter bigger amounts for Revenue and for Cost of Goods Sold in the
C2 and C3.
Copy the Cost formula from B4 to column C4.
In column C, Enter 3 new amounts for the 3 Non Operating Expenses some higher some lower
than the prior period.
Select all formulas in the remaining rows of column B and copy them from column B to
column C.
Enter "Difference" as column D label.
Enter formula for the difference between 1Qtr 07 and 2 Qtr 07.
Enter %t Diff as column E label
Enter formula the divides the dollar difference by the first period amount
Restyle as percentage with no decimals
Fill Down the entire column.
Setup a Header and footer with a title your name, the date, etc.
Save and Print sheet
Select Cost of Goods Sold value.
Hold down the ctrl key and select cells with the values of the other
components or revenue: the 3 Discretionary expenses, taxes and Net
Income.
Keeping the Cntl key depressed, select the labels)
Use the Chart Wizard to create a pie chart in the sheet
Spruce up the chart is desired by putting patterns in the slices, exploding a slice,
putting data labels and percentages around the chart. Make sure the chart doesn't
cover the numbers.
Save and Print sheet with the chart
Print the formulae (Tools menu, Options, View tab) with gridlines and row and
column heading (File menu Print Setup)
|
