STEP BY STEP GUIDE FOR INVENTORY SHEET #2 & #3 OPEN OFFICE

1. Heading: “FINANCE PLAN FOR “ <your name>“

o First open your inventory workbook.

o Then, click on the tab sheet2 to get to the second sheet.

o Then format / page and click on the header tab and then click on the edit button.

o The header from the first sheet will default, but you can change it.

o Type Purchase Plan for “<your name>“ like below:

o Click OK

1. Footer: Date, filename and page number.

• Click the footer tab and click edit.
• Click in the left section and then click the date button
• Click on the center section and click the title button
• Click the right section and then click the number sign button.
• Click OK

2. Sheet label: Finance Plan

• right click the sheet tab, choose rename sheet, and type Finance Plan

3. On the first line, show “ Amount I need to finance” and in the cell next to it, show any amount formatted as currency. (Do not use 750.)

• Click in cell A1 and type “ Amount I need to finance “.
• Click in cell B1 and enter the amount of money you want to have to borrow.
• Right click on B1 and choose format cells / number tab / currency selection.

4. Wrap the “Amount I needed to Finance” cell so it appears on 2 lines.

• Highlight A1 and choose format / cells.
• Choose the alignment tab.
• Click the wrap box until it is checked and then choose ok.
• See that the words show on two lines now and don’t cross the cell border.

5. On the second line, show “Date Needed” and in the cell next to it, show the date January 2, of this year. Format the date so it shows as “the month abbreviated-the year” (ex: Jan-07)

• Click in cell A2 and enter “Date Needed”.
• Click in the cell next to it (B2) and enter 1/2 and then press <Enter>.
• Choose format / cells / number and then choose the date format month name, day and then year (Jan 2, 08).
• Hit <Enter>.
• It should now look like:

6. Merge C3 to F3 and write “Rates” and bold and center it.

• Highlight C3 to F3
• Choose format / merge cells
• Type “Rates” in the merged cell.
• Click the bold and center buttons

7. Merge A5 to A8 and write “Years” and bold and center that. Turn “Years” so it prints vertically.

• Highlight A5 to A8
• Choose format / merge cells
• Then, to make it print vertically, choose format / cells / alignment tab / type 90 in the degrees box, and check the “vertically stacked” box (which means the letters themselves wont be turned).
• Under vertical alignment, choose middle so that the “Years” will not be at the bottom.
• Hit <Enter> to accept it.
• Type “Years”.
• Click the bold and center buttons
• Highlight rows 5 to 8 (by clicking on the number 5 and dragging to the number 8) and make them a little wider. (by grabbing one row divider and pulling it down).

8. In C4-F4, enter the rates 1%, 5%, 10% and 15% and in B5-B8, enter the years 1, 3, 4 and 5.

• Just enter 1% in C4, 5% in D4, 10% in E4 and 15% in F4.
• Also enter 1 in B5, 3 in B6, 4 in B7 and 5 in B8.
• It should now look like this:

9. Calculate the payment amount for the loan needed for each interest rate/ year combination. (Loans are compounded annually.) This should be done be creating the formula once, and copying it to all the other cells in the grid. The payment should be formatted as currency. Do not enter the formula more than once.

• In cell C5, choose insert / function.
• Select the category financial.
• Choose the function PMT
• Press <Next>
• Hit the spreadsheet box next to rate and click on the rate in C4 and then hit <Enter>
• Hit the spreadsheet box next to Nper (number of periods) and hit the year 1 and then hit <Enter>
• Hit the spreadsheet box next to Pv (present value – the amount you want to borrow) and hit the amount you want to borrow in B1 and hit <Enter>
• Press <Enter> again to accept the formula.
• Format as currency with format / cells / number / currency. (If you want it to be a positive number, you would need to multiply the amount borrowed by -1.)
• See the payment amount. It should be just a little more than the amount you are borrowing because the interest is low and you are only making 1 payment.

10. Adjust the formula so it can be copied to the other cells in the table.

• Highlight cell C5 to see the PMT formula in the formula bar.

• Put a $ before any row or column you want to stay put when you copy the cell. Leave the $ out when you want excel to move the row or column when you copy. For the rate, you will want to keep the row the same and let the column move. For the year, the column will stay the same and the row will move. Make your best guess at where the $ signs belong. (ex: C$5 to hold the row as 5 or $C$5 to hold the cell as C5 or $C5 to hold the column as C). Press <Enter> when you are done with your first guess.
• Copy C5 to all the cells from C5 to F8.
• Format all these cells as currency with format / cells / number / currency
• Verify it is correct by asking:
• Are all the 1 year loans a little bigger as the rate increases?
• Are 1% loans smaller as the number of years increases?
• If the answers are yes, you are done. If not, go back to C5 and change the $ signs and repeat the copy until it is correct.

• Change the amount you need and see all the numbers in the grid change.

11. Bold all labels.

• Highlight row 4 by clicking on the number 4
• Press the B button
• Highlight column A by pressing on the A letter
• Press the B button
• Highlight B5-B8
• Press the B button

12. Center everything in columns B through F.

• Highlight columns B through F by clicking on the B and then holding until you reach the F.
• Click the center button.

13. Put gridlines around the years and rates and all the payments. Below the year and rate labels, place a double line.

• Highlight B4 to F8
• Format / Cells and click the border tab
• Choose the outside and inside
• Hit <Enter>
• Highlight just the interest rate labels (C4-F4)
• Format / cells /border
• Click only on the bottom of the cell.
• Choose the double line border
• Hit <Enter>
• Highlight just the year labels (B5-B8)
• Format / cells /border
• Choose the double line border
• Click only on the right of the cell
• Hit <Enter>
• It should look something like the following:

14. Set it to print landscape so that it doesn't break into 2 pages.

· Format / page and then click page and then check the landscape box.

15. Start on Sheet 3 by clicking on the Sheet 3 tab, or insert / worksheet

16. Heading: “VALUE CHART FOR “ <your name>“

o First open your inventory workbook.

o Then, click on the tab sheet3 to get to the second sheet.

o Then format / page and click on the header tab and then click on the edit button

o Type Value Chart for “<your name>“ like below:

o Click OK

17. Footer: Date, filename and page number.

• Click the footer tab and hit the edit button
• Click in the left section and then click the calendar button
• Click on the center section and then click the title button
• Click the right section and then click the number sign button.

• Click OK

18. Sheet label: Charts

• Right click the sheet tab, choose rename sheet and type Charts

19. Using the chart wizard on the finance chart, create a chart of the possible payments:

• Highlight from B4 to F8.
• Chose insert / chart and then hit next, and then then check the “first row as labels” and “first column as labels” and then click finish.
• Then click off the chart and once back on the chart to highlight it. Edit / cut and then click on the chart sheet tab and then edit / paste.
• Then hit finish. Your chart should look like (with different numbers):

20. Using the chart wizard on sheet 1, create a column chart with the following:

• Title: Comparison of total units and values
• y axis label - “value”
• x axis label - “items”, with the numbers being taken from column A
• Series 1 - Unit Value, with the label being taken from row 1
• Series 2 - Total Value, with the label being taken from row 1
• The chart should look something like:

Steps to create the chart:

• In Sheet 1, highlight the unit value starting at E2, through the last total value. (So you should have 2 columns highlighted.)
• Choose Insert / Chart from the menu.
• Leave it on the column type and press next.
• Next to data series See that column E is already highlighted and click on the spreadsheet box for “Range for name”. Click on the “unit value” in E1.
• Click on column F in the Series box.
• Click on the spreadsheet box for “Range for name”. Click on the “total value” in F1.
• Click on the spreadsheet box next to categories. Highlight the first item number though the last item number and choose enter.
• Hit Next
• Under Chart Elements, Type “Comparison of total units and values” in Chart title, “Item Numbers” in category x axis, and “value” in category y axis
• Hit Finish
• You will need to highlight the whole chart (not a piece), cut it from simple list and paste it into the chart sheet below your other chart.