|
|
This assignment prepares a Profit and Loss statement,
which is the
most common (important) financial statement in business and a pie chartT.
Perform the following tasks in
the order given:
- Enter column labels in of "Length", "Width", "Cost per sq. yd" and
"Room Cost" in row 1
(to get Cost per Sq yd on 2 lines type cost per then hold
down the "Alt" key as you tap "Enter")
- Click on the "1" of row 1 to highlight the entire row, center each, italics them, change font if
you wish
- Enter four room names such as "Living Room",
Master Bed Room" etc. as row labels in column A
- Adjust the column widths. (double click on the line
between the "A" and "B" on top to adjust to the optimum size)
- In B2 to C2, enter dimension for first room in feet. In D2 enter a cost per square yard
for carpet in 1st room.
- In B3 to C3, enter dimension for second room in feet. In D3 enter a cost per
square yard for carpet in second room.
- In B4 to C4, enter dimension for third room in feet. In D4 enter a cost per square
yard for carpet in third room.
- In B5 to C5, enter dimension for fourth room in feet. In D5 enter a cost per
square yard for carpet in fourth room.
- Position in cell E2. Calculate the cost to carpet room one. Remember you are dealing with room
dimensions in linear feet and cost per square yard.
(think about a square yard that you must carpet
with 1 by 1 carpet tiles, how many will you need?)
- Position in E2, type an = to indicate a formula is
coming,
click B2, the length, type a *, to multiply. click C2 the width
type a / to divide and type a 9 because there are 9 square feet in a
square yard
finally type a *, to multiply and click on the cost per square yard
in D2 and press Enter cause you are done.
- Drag this formula down 3 rows by positioning on black
square on lower right of E2 so the cursor turns into a black cross and
pull down 4 rows.
- Position in cell E6. Use AutoSum icon (the sigma) on toolbar to total (sum) the room costs
- Save the sheet to your M drive as CARPET or whatever makes sense to you.
Put in a Header using File menu Page Setup. Put your name and the date
etc. in the header. Make it looks nice using the
A icon
- Type "Discount" in A7. Type 10% in B7. The "of
amount over" in C7 type "500" in D7.
- In E7 calculate a discount of 10% the the total that is over
$1,000.
Note this will require the use of the
IF function. (Read the
IF hint page ) Click on the
 .
Select Logical. Select IF.
For 1st argument click on E6 then type < for less than . Then click on
cell D7 which has the 500.
In the 2nd argument type a zero since there is no discount if the total
amount is less than $500.
In the 3rd argument click B7 - the 10%, then type a *, the sign for
multiplication.
Click E6, the total, then type - because only the amount OVER 500 gets
discounted. Then click D7 the 500.
Finally looking at the formula note that the subtraction must occur before
the multiplication so put it in parenthesis.
- Type "Net Due" in A8.
- In E8 calculate net. Position in E8. This
is a formula so type =. Then click on the Total, type -, minus,
click on the Discount.
- In A9 enter State Tax. B9 enter 6%.
- In E9 calculate state tax (which is the Net Due times the Tax rate,
B9, in NJ it's 6%).
Position in E9. This is a formula so type =.
Then click on the Total, type *, to multiply, click on the cell with
6%.
- In A10 enter "Total Due". This is the sum (sigma) of the 2
cells above.
- In E10 calculate Total Due which is Net plus tax.
- In A11 enter Monthly Payment.
- In E11 calculate the monthly payment using the
PMT Financial function calculate the
payment for 1 year (hint 12 monthly payments at 11% a year).
(Read the PMT hint page )
Click on the
.
Select Financial since you go to a bank for a loan and a bank is a
Financial institution. Read the descriptions of the functions as you
press down. Select PMT for monthly payment.
For 1st argument Rate is the Monthly interest rate. Since 11% is the
yearly interest rate, Click on B11, then type / for divided by, then type
12 for 12 months in a year.
In the 2nd argument, number of periods type a 12 since you pay monthly and
there are 12 months in a year. .
In the 3rd argument. PV or Present Value, how much the money is worth now,
which is the amount you are borrowing. Click on E10, the Total Due.
Finally press Enter cause you're done.
- Save and Print sheet
- Create the chart by
 | highlighting both the room names in column A and their cost in column
E (1st highlight E1 to E5, then hold down the ctrl key to highlight A1
to A5, non-touching cells). |
| use the chart wizard to create a Pie Chart.
Enter a heading in Step 3 and possibly put Data labels
of value and/or percentage around the chart. Finish after Step
3. |
| explode a slice |
| make sure you have values or percentages next to each slice
(go back to the wizard if you forgot to do so at Step
3) |
| put patterns in one or more slices by selecting one slice at a time
double clicking on it or otherwise getting the format menu. Click on
Fill Effects Box. |
For your portfolio, print the formulae (Tools menu, Options, View tab) in Landscape with gridlines and row
and column heading (File menu Print Setup). Remember to reverse the Formulas,
Landscape, Gridlines, Row and Column Heading clicks because you don't want to print it
when you print values.
|
