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

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 file / page setup and click on the header/footer tab and then click on the custom header button

o The header should be empty. If it has a heading already, you are probably not in sheet 2, so go back and click the sheet 2 tab first.

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

o Click OK

1. Footer: Date, filename and page number.

• Click the custom footer button
• Click in the left section and then click the calendar button
• Click on the center section and then click the x over a paper button
• Click the right section and then click the number sign button.
• Click OK

2. Sheet label: Finance Plan

• Double click the sheet tab 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.
• Click on the $ button in the toolbar at the top of the screen or format / cells / number / currency.

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 that matches Jan-07, with 07 being the year, not the day.
• 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 / cells and choose the alignment tab.
• Keep clicking the merge cells box until it is checked.
• Hit <Enter> to accept it.
• Type “Rates”.
• 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 / cells and choose the alignment tab.
• Keep clicking the merge cells box until it is checked.
• Then, to make it print vertically, type -90 in the orientation box, and then highlight the box that says “text” and see that it is still at 0 degrees (which means the letters themselves wont be turned).
• Under vertical alignment, choose center 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 <Enter>
• 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.
• Press the $ button to format this number as a 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 by pressing the $ button.
• 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 an 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
• Choose the double line border
• Click only on the bottom of the cell.
• 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.

· File / Page Setup 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 file / page setup and click on the header/footer tab and then click on the custom header button

o The header should be empty. If it has a heading already, you are probably not in sheet 3 so go back and click the sheet 3 tab first.

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

o Click OK

17. Footer: Date, filename and page number.

• Click the custom footer button
• Click in the left section and then click the calendar button
• Click on the center section and then click the x over a paper button
• Click the right section and then click the number sign button.

• Click OK

18. Sheet label: Charts

• Double click the sheet tab and type Charts

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

• Highlight from A4 to F8.
• Chose insert / chart and then just keep pressing next until you reach the chart location. Then, change “As object in” to the charts page.

• 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 range, hit the spreadsheet box and again highlight from E2 through the last total value. Hit <Enter>.
• Click the series tab.
• See that series1 is already highlighted and enter “Unit Value” in the name field
• Click on series2 in the Series box.
• Click in the name field and enter “Total Value” in the name field. (Note that you could also have selected the cell that reads total value instead.)
• Click on the spreadsheet box next to category x axis. Highlight the first item number though the last item number and choose enter.
• Hit Next
• Under Titles, Type “Comparison of total units and values” in Chart title, “Item Numbers” in category x axis, and “value” in category y axis
• Hit Next
• Click the “as object in” button and choose the value sheet
• Hit finish.
• You will need to highlight the whole chart (not a piece) and move it lower on the sheet so it doesn’t cover up the other chart.