Excel for business procedures math.docx

Excel for Practical Business Math Procedures Overview of Excel The Excel spreadsheet is made up of columns and rows. Each column is headed by a letter of the alphabet and each row is headed by a number. Data is entered where the column and row intersect. This is referred to as a cell. When using the spreadsheet, the cell is assigned an “address” which is the column letter followed by the row number. As you move around the spreadsheet, you will see the cell location displayed just above the column letters on the left side of the screen. A black border surrounds the active cell. You can move to any cell address by clicking the mouse pointer in a cell or by using arrow keys. Remember: you must always enter the column number before the row number. B2, C5l, T132 are correct cell addresses; 6lG, 5R, A4A are not correct cell addresses. In the picture below the cell pointer is in A1. Looking at the Excel screen you will see the following layout: 1) The first row is a menu bar with pull down menus: file, edit, view, insert, format, tools, data, window, and help. 2) The next two rows are toolbars with various symbols. If you place the mouse arrow on any of the symbols a “label balloon” appears to indicate what that symbol represents. We will use the CURRENCY Symbol, PERCENT symbol, the INCREASE DECIMAL and DECREASE DECIMAL the most. 39376352889250027946352889250017659352889250073723528892500 CURRENCY PERCENT INCREASE DECREASE 3) The fourth row is the formula bar, which includes the cell address, and a blank space where data and formula are displayed as you enter them. 4) At the bottom of the screen there are two rows. The top row is the area for the sheet names, referred to as “sheet” tabs, and horizontal scroll bar which allows you to move the spreadsheet to the left or right. The last row at the bottom is the status bar. 5) On the right side of the screen is the vertical scroll bar that allows you to move the spreadsheet up and down. The active cell, the cell where data is entered, is outlined with a black box. In the lower right side of that box there is an equal sign or a small square dot (this equal sign allows you to copy information from the active cell to other cells by using the mouse). You will notice that the column letter and row number are in bold. The mouse pointer is a large plus sign. As you move the mouse pointer to the menu bar or tool bars the large plus sign will change to a white arrow. When entering data you first choose the cell you wish. You can use either the arrow keys on your keyboard to move in any direction or place the mouse pointer in the cell and click the left mouse button one time. The active cell will have the black box. You may then type either words, called labels, or numbers, called values. The words must fit into the cell or else they will spill over into the adjoining cell (if the adjoining cell is not empty then only a part of the label will be visible). When entering a value there is no spill over available and you will get an error message (a series of ##### symbols will appear). The method to widen the cells is explained later. In general you will be able to work with the columns and rows that are visible on screen. This will usually be 19 rows and 9 columns. Your spreadsheet, depending on how it was installed, may display an additional of fewer columns and rows. If you need more space use your arrow keys to reach the cells that are not visible. Once you have chosen the cell you wish to use, you type the information. The data will appear in two places on the screen: in the cell you chose and in the formula bar directly above the column letters. That formula bar gives you the opportunity to make corrections by using the backspace key and retyping data. After you type the data you will note three additional symbols will appear to the left of the data in the formula bar. The first is a red letter X which allows you to cancel what you have done; the second is a green check mark which allows you to enter information onto the spreadsheet; and the third consists of two black letters, an F and an X, which allows you to use a function wizard or built in formula. Data can be entered either in all upper case, all lower case, or a combination. If the data is correct you proceed by either clicking on the green check mark in the formula bar or by pressing the ENTER key on your keyboard. You then move to the next cell and repeat the operation. If you find you made a mistake, or wish to change your data, just return to the cell and press the DELETE key on your keyboard. That will empty the cell. It is recommended that you place your labels in row one and column A and place your values in a column format. With Excel, you can add, subtract, multiply, or divide in any direction however. To create a formula use the equal (=) sign followed by the specific math operations you wish. On a spreadsheet you may use numbers in your formulas but it is recommended that you enter the address of the cells that contain the numbers. This allows you to change numbers without having to change the formulas. The symbols you use for mathematical operations are: the plus sign (+) for addition; the minus sign (-) for subtraction; the asterisk symbol (*) for multiplication; and the slash or slant sign (/) for division. In addition you can add a column or row using a built in formula called SUM. The format you will use is as follows: 1) To add a value in cell A2 to a value in A3 you would make cell A4 the active cell. You would then type the equal sign, followed by the cell address of A2, followed by the plus sign, followed by the cell address of A3, and press enter or click the green check mark symbol in the formula bar. =A2+A3 (note there are NO SPACES between any parts of the formula). 2) To multiply a value in cell C5 times a value in T6 you would use the following: =C5*T6 (note that you use the equal sign no matter what operation you are doing) 3) To add D4 to G6 and subtract R7 you would use the following: =D4+G6-R7 You must be careful when building formulas to remember this rule: a spreadsheet will multiply and/or divide BEFORE it will add and/or subtract. In order to reverse that process you must use parenthesis around the part of the formula you wish Excel to do first. Here are examples of the difference when using parenthesis: A4 has a value of 8, B5 has a value of 2, and C6 has a value of 3. =A4+B5*C6 will give you a total of 14. (2 times 3 are 6; plus 8 are 14). =(A4+B5)*C6 will give you a total of 30 (8 plus 2 is 10; times 3 is 30). When entering labels or values you may need to make the cell wider (the default or basic setting of a cell is 9 spaces wide). You cannot make just one cell wider - you must make the entire column wider. One way to do this is to place the cell pointer on the LETTER of the column and move the pointer to the RIGHT. When you reach the black vertical line that separates the columns the cell pointer will change from a white arrow to a vertical line with a thin black arrow pointing to the left and pointing to the right. Hold the left mouse button down and drag the mouse to the RIGHT. You will see the line that creates the cell edge move to the right. When the entire label or value is inside the cell just let go of the mouse button. The column is now wide enough. You may also widen a column using the FORMAT menu. Place the mouse arrow on the word FORMAT and click the left mouse button. Next drag the left mouse button on the word COLUMN. You change the column by using the AUTOFIT choice using the left mouse button. 17659351181100062293511811000374904013716000FORMAT COLUMN AUTOFIT SELECTION Try this exercise: 1) Start by loading Excel by double clicking on the Excel icon from the Excel work group or the Microsoft Office work group. 2) Place the cell pointer (white plus sign) into cell B5 and click the left mouse button. The black box should appear to surround cell B5. 3) Type the label “TESTING” into that cell. You can either click on the green check mark or press the ENTER key. 4) Move to cell B6 (if you clicked on the green check mark you now can either place the cell pointer in B6 and click once or press the down arrow key once. If you used the ENTER key you should already be in B6). 5) Type the value 4 and enter it. 6) Move to Cell B7. 7) Type the value 5 and enter it. 8) Move to cell B8. To add those values type the formula: =B6+B7 and enter it. You should get the total of 9. 9) Move to cell C1 and enter the value 6. 10) Move to cell C2 and enter the value 7. 11) Move to cell C3 and type the formula to multiply those values. You should get the total of 42. (That formula should be =C1*C2.) 12) Move to cell D6 and here we will add the values in B6 and B7 and then multiple times the total in cell C3. Remember to use parenthesis to surround the part of the formula you wish Excel to do first: the adding of B6 and B7. The formula should look like this =(B6+B7)*C3 and the total should be 378. To use the automatic SUM formula, you would start with the equal sign (=), followed by the word SUM and then a left parenthesis. Inside the parenthesis you would put the cell address of the first cell, followed by a colon, and then the cell address of the last cell. Now type a right parenthesis. In cell D1 type =SUM(B6:B7) and you will get a total of 9. You are now ready to begin. On additional note, although the latest version of Excel – Excel 2007 – has some cosmetic updates, the fundamental functions and features remain basically the same. Besides, in the light of the fact that the existing versions of Excel (2003, 2000 and 97) are still in wide use, we will continue to use the existing versions for our examples. The following figures are introduced just for your reference and illustrate the cosmetic changes in Excel 2007. To fully utilize the features that we will use throughout this book also in Excel 2007, you will need to activate some options as follows. Locate the “Add-Ins” tab from the “Excel Options” box and click on “Go” button at the bottom with “Excel Add-Ins” selected in the “Manage” window. Check “Analysis ToolPack” and “Solver Add-in” options for later uses in this book. Chapter 4 Problems Reconciliation statements are one type of item a spreadsheet handles well. You can enter outstanding check amounts, deposits not credited, bank fees and charges and have Excel complete the reconciliation. The file can be a template that can be used over and over again. We will be using problem 4-7 and create a reconciliation statement for Judy Smejek’s account. We will need two areas: 1) Checkbook Balance and 2) Bank Balance. We will use abbreviations and change the number format to currency. Formulas will be entered where needed. NOTE: ALL FORMULAS APPEAR IN APPENDIX A IN CASE YOU HAVE ANY DIFFICULTY. To be sure we have enough room in each cell we will first widen the cells and change the cells to a currency format. In the upper left hand corner, to the left of the letter A and directly above the number 1, there is a blank box. When you click on that box it will change the entire spreadsheet. Click on it now and the spreadsheet will highlight in grayish blue. Now click on the “$” symbol in the format task bar. Next click on the FORMAT menu; select COLUMNS; select WITH; and then type in the number 12. All the cells are set for currency and all the cells are set for a width of 12 characters. Now click on cell A1. In cell A1 type the word “Checkbook”. Next in cell A3 type the word “Balance:”. In cell A5 type the word “Refund:”. In cell A7 type the words “Check Fees:”. In cell A9 type the word “ATM”. In cell A11 type the words “Service Fee:”. And in cell A14 type the word: “Total:”. In cell D1 type the words “Bank Statement”. In cell D3 type “Balance:”. In cell D5 type “D.I.T.” for Deposits in Transit. In cell D7 type the words “Outstanding Checks:” for checks not yet deducted. In cell D14 type the word “Total”. Now we are ready to put the figures and formulas into place. 1) In B3 we enter the balance of $1,085.81. 2) In B5 we enter the IRS Refund of $1,506.50. 3) In B7 we enter the fee for additional checks of $11.25. 4) In B9 we enter the ATM total of $540.00. 5) In B11 we enter the service fee of $2.50. 6) Now in B14 we enter the formula that will add the balance to the refund and subtract the check fee, ATM amount and the service fee. (See Appendix A if you have trouble with the formula.) 7) In E3 we enter the bank statement balance of $1,768.01. 8) In E5 we enter the deposits in transit amount of $650.25. 9) In E9 we enter the amount of the first outstanding check of $312.50. 10) In E10 we enter the amount of the next outstanding check of $50.40. 11) In E11 we enter the amount of the next outstanding check of 16.80. 12) Now in E14 we enter the formula which will take the balance and add the deposit in transit and subtract the three outstanding checks. (See Appendix A if you have trouble with the formula.) The final result will look like this: Before proceeding, you should save this file to your disk. This is a template you can use over and over. Click on FILE and click on SAVE AS. Enter the filename “C4” for Chapter 4. Be sure the file is set for your disk. Ask the lab assistant for help. You can now erase the figures and enter new figures. Be careful NOT to erase the formulas. Alternatively, you can perform the same task using minus signs as follows. PROBLEM: Using this reconciliation template, take your own checkbook or a checkbook from a business or company and see if you can make the balances match. Chapter 6 Problems Spreadsheets do not have to be elaborate to do the job. The WORD PROBLEMS 6-52 and 6-53 ask for two different numbers but they refer to the same base (the base will be the denominator in the percent problem). You can set up a spreadsheet to do both problems at once. We must change column C to percentages. First click on the letter C above the C column. The entire column will turn dark except for cell C1. Then click on the FORMAT menu; click on the CELLS command; click on the word PERCENTAGE on the LEFT side of the information box and then click on the OK button. Now the column is set for percentages. Use A1 for our heading and type “Wendy’s Survey about Diet Pepsi” (again the overflow is automatic). Go to cell A3 and type “Base”. Go to cell A5 and type “Diet”. Go to cell A7 and type “No Diet”. Go to B7. The formula will calculate the number of non-diet drinkers by subtracting the number of diet drinkers in cell B5 from the base in cell B3. (See Appendix A). Go to C5. The formula will take the number of diet drinkers from B5 and divide them by the base number of B3 (disregard the results for now). (See Appendix A). Go to C7. The formula must take the number of non-diet drinkers from B7 and divide them by the base number of B3 (disregard the results for now). (See Appendix A). Save the template to your disk if you wish. The template will look like this: In cell B3 enter the base number of 12,000. In cell B5 enter the diet drinkers number 3,000. The formulas will automatically calculate. PROBLEM: Build a template to find the answer to this question: The Gallup Poll interviews 850 people about their choice of candidate. 375 people prefer Senator Halley while the rest prefer Senator Johnson. What percentages does each Senator have? Chapter 7 Problems Spreadsheets can help compare different values. In this case we will be using the SUMMARY PRACTICE TEST problem number 7 to help Pat Mann decide whether manufacturer A or manufacturer B is offering her the best value. First we change the format of the values. Take the cell pointer and place it on the rectangle just ABOVE the row numbers and to the LEFT of the column letters and click. The entire spreadsheet has turned dark except for cell A1. Now click on the TASKBAR button that looks like a comma (,) to set for numbers. In cell A1 type “Man. A”. In cell D1 type “Man. B”. In cell A2 type “First”. Do the same in cell D2. In cell B2 type “Second”. Do the same in cell E2. In cell A7 type “Discount”. Do the same in cell D7. In cell A5 enter the formula that will take 1.00 and subtract the value in A3 (disregard the answer for now). (See Appendix A). In cell B5 enter the formula that will take 1.00 and subtract the value of B3 (disregard the answer for now). (See Appendix A). In cell B7 enter the formula that will take the answer from A5, multiply it times the answer in B5, and subtract that from 1.000 (disregard the answer for now). (See Appendix A). In cell D5 enter the formula that will take 1.00 and subtract the value of D3 (disregard the answer for now). (See Appendix A). In cell E5 enter the formula that will take 1.00 and subtract the value of E3 (disregard the answer for now). (See Appendix A). In cell E7 enter the formula that will take the answer from D5, multiply it times the answer in E5, and subtract that from 1.000 (disregard the answer for now). (See Appendix A). Save if you wish. Your spreadsheet should look like this: We are now ready to enter the information. Go to cell A3 and enter “.14”. Go to cell B3 and enter “.08”. Go to cell D3 and enter “.15”. Go to cell E3 and enter “.07”. All the formulas will calculate and you can now compare the discount for Manufacturer A in cell B7 with discount for Manufacturer B in cell E7. You will find that Manufacturer A gives the best discount. PROBLEM: You are ordering new computers from COMPWORLD. They offer you a choice of discount plans. Plan A offers you 14/10 and Plan B offers you 25/4. Which of the plans is best for you? Chapter 8 Problems In this chapter we will be calculating a final price that has gone through a number of markdowns and markups. We will show the price through each change as well as the final selling price and the markdown percent. We will use the information in PROBLEM 8-15. First we change the way the values will appear. Go to the row number 3 and click on it. The entire row turns black except for cell A3. Next click on the CURRENCY button ($) in the TASKBAR. Click cell F3 and click on the PERCENT button (%) in the TASKBAR and INCREASE the number of decimals by 2. In cell A1 type “Original”. In cell A2 type “Selling Price”. In cell B1 type “First”. In cell B2 type “Markdown”. In cell C1 type “Second”. In cell C2 type “Markdown”. In cell E1 type “Final”. In cell E2 type “Markdown”. In cell F1 type “Markdown”. In cell F2 type “Percentage”. In cell D2 type “Markup”. You should now adjust your columns by using the FORMAT menu and COLUMN choice as you did before. Your spreadsheet should look like this: In cell A3 enter the figure $5,000. Enter the formulas keeping in mind you must subtract or add the percentages given from 100% depending on if it is a markdown or a markup. In cell B3 enter the formula that will take the value of B3 (it is a markdown), and multiply by A3. In cell C3 enter the formula that will take the value of C3, (it is a markdown), and multiply by B3. In cell D3 enter the formula that will take the value of D3, (it is a markup), and multiply by C3. In cell E3 enter the formula that will take the value of E3, (it is a markdown), and multiply by D3. As the formulas should calculate and you will find the totals will be: $4,000 for first markdown; $3,600 for second markdown; $4,032 for the markup; $3830.40 for the final markdown; and 23.39% for final percentage. PROBLEM: Your boss put you in charge of changing the price each time a new markdown is taken. You need to keep track of each price change and what the final percentage will be. The original selling price is $300.00. Markdowns: First is 5%, Second is 12%, Markup is 3%, and the Final is 18%. Breakeven Analysis We will use summary practice test #11 for example. There are largely two ways to perform breakeven analysis using Excel. The first one is by building the format and entering relevant data and formula to solve for the breakeven output level (QBE), and the second one is by using Excel’s built-in “Goal Seek” function. We will look into the formula method first. We build the table as in the following figure. Ignore columns D and E for now. We build just one table across columns A and B only. In cell A1, type “Parameters”, which are the given data. In cell A2, type “P” for selling price, and enter 25.99 in cell B2. Highlight column B and select $ sign from the tool bar, so that the entire column will be formatted as currency. In cell A3, type “TFC” for Total fixed cost, and enter 80960 in cell B3 In cell A4, type “AVC” for Average variable cost, or the unit cost, and enter 18.95 in B4. In cell A6, type “Variables”, which are the unknowns to solve for. In our problem, this is the output level (quantity) at breakeven. Enter the formula for break even B6 as shown in the figure below. In cell A9, type “Results”. In cell A10, type “TR” for Total revenue, and enter the formula in cell B10 as in the figure. In cell A11, type “TFC”, and simply enter B3 to point to the cell that already contains the data. In cell A12, type “TVC” for Total variable cost, and enter the formula in cell B12 as in the figure. In cell A13, type “TC” for Total cost which is TFC+TVC, and enter the formula in cell B13 as in the figure. In cell A14, type profit, which is TR-TC, and enter the formula in cell B14 as in the figure. Since profit=$0 by definition at breakeven point, once QBE in cell B7 is solved for, cell B14 will automatically show $ - for 0. Now, we will use “Goal Seek” to perform breakeven analysis. We can copy the existing format for this purpose. Simply highlight the entire format and copy it into cell D1. Although you just select one single cell, Excel will automatically paste the whole table over into the correct range of cells. The only difference between the previous method and this one is that the solution formula is not assumed – i.e. we leave the cell D7 blank, and let Excel worry about it. In order for Excel to find the solution, click on “Tools” menu in the tool bar on top. Once the drop-down menu opens up, select “Goal Seek”, and the Goal Seek dialogue box will open up. Click on the spreadsheet icon in the right corner to enter the target cell into the “Set cell:” box. This is the goal (profit) we want to set to $0. Why? Because by definition, profit=$0 at breakeven. So, we enter “0” in the next box to set it to value of “0”. Then, click on the spreadsheet icon in the right corner of “By changing cell:” box to enter cell E7, the unknown we want to solve for. Intuitively, this is the cell that is supposed to contain the data we must vary to arrive at the $0 profit we have set for breakeven point. Then, we click O.K., and the result will be instantly calculated as follows. Chapter 9 Problems Computing payrolls is made easier with spreadsheets using formulas which automatically change when you change the salary. Here we will be doing the PROBLEMS 9-12 and 13, setting up a payroll spreadsheet for Ring and Porter so they can compute their total earnings. They can use the same spreadsheet each week to compute their earnings by entering the new numbers. In cell A2 type “Gross Sales:”. In cell A4 type “Return:”. In cell A6 type “Net Sales:”. In cell A8 type “Given Quota:”. In cell A10 type “Commission Sales:”. In cell A12 type “Commission Rates:”. In cell A14 type “Total Commission:”. In cell A16 type “Regular Wage:”. In cell A18 type “Total Wage:” Your spreadsheet should look like this: Now we can enter the dollar amounts, commission rate and formulas to calculate the salary. In cells B1 and C1 enter the employees’ names, Ring and Porter respectively. In cell B2 and C2 enter the gross sales. Then click on the currency symbol “$” in the task bar to make the figure into currency. In cell B4 and C4 enter the amount of returns. In cell B6 and C6 enter the formula that will calculate the net sales. Then click on the currency symbol “$” in the task bar to make the figure into currency. In cell B8 and C8 enter the given quota. Then click on the currency symbol “$” in the task bar to make the figure into currency. In cell B10 and C10 enter the commission sales, and click on the currency symbol “$”. In cell B12 and C12 enter the commission rate in decimal format. You may also click on % icon in the tool bar to make these figures into percentage. In cell B14 and C14 enter the formula to calculate the total sales commission. Then click on the currency symbol “$” in the task bar to make the figure into currency. In cell B16 and C16 enter the regular wage, and click on the currency symbol “$”. Now you are ready for the final step. In cell B18 and C18 enter the formula that will add the regular wage to the total commission. Then click on the currency symbol “$” in the task bar to make the figure into currency. PROBLEM: John Franks works for $8.75 per hour and worked a 40 hour week. He also gets commission on sales. His sales were $2,650 and his commission rate is 4%. Calculate his total pay. Chapter 10 Problems We will set up a table to calculate maturity value using ordinary interest, the method used by most banks. This spreadsheet will be used for Problems 10-4, 10-5, and 10-6 in your text. The formula to use is: Exact Number of Days T = ---------------------------------------- 360 We will set three columns to currency and also widen them. By holding down the CONTROL key (marked CTRL) you can select all three columns and all three will stay active. Hold down the CONTROL key and click on the column letters A, F, G. Click CURRENCY symbol ($). Immediately click on the FORMAT menu; click on the COLUMN command; click on the WIDTH choice and a box will open for the desired width. Type “15”. Now click on the OK button and all three columns are now set for currency and are now 15 picas wide. In cell A1 type “Principal”. In cell B1 type “Interest Rate”. In cell C1 type “Date Borrowed”. In cell D1 type “Date Repaid”. In cell E1 type “Time”. In cell F1 type “Interest”. In cell G1 type “Future (Maturity) Value”. (Remember: If the labels are too long, click on “Format” in the tool bar, select “Cells”, click on “Alignment” tab, and check the box for “Wrap text”.) In cell E2 enter the formula to calculate the time which is the repaid date in D2 minus the borrowed date in C2. (Dates can be formatted as mm/dd/yyyy.) In cell F2 enter the formula to calculate the interest by using the principal from A2 times the rate in B2 (Enter decimal and click on % sign on the toolbar.) times the calculation for T (T = the exact number of days in E2 divided by 360). Lastly in cell G2 enter the formula to calculate the Maturity Value by adding the principal in A2 to the interest in F2. Your spreadsheet should look like this: Enter the data from the problems beginning with problem 10-4. In cell A2 enter the principal of $1,000. In cell B2 enter the decimal of 8%. In cell C2 enter the number for the date corresponding to March 8 which you will find in your Business Math Handbook. (You did this in Chapter 7.) In cell D2 enter the number for the date corresponding to June 9. Again you will find it in your Business Math Handbook. You should arrive at the answer of 93 days for time; $20.67 for interest; and $1,020.67 for maturity value. You can now enter the other values in rows A3 and A4 along with the proper formulas. PROBLEM: Change the template to calculate the EXACT interest method used by the Federal Reserve (365 days). You will have to change the formula in cell F2 (see Appendix A). Use the following data: $2500 principal, 7% interest rate, borrowed on April 11, and repaid on August 18. Chapter 11 Problems This chapter’s problem involves finding the amount of interest charged for each note, the amount the borrower would receive, the amount the payee would receive at maturity and the effective rate. We will use the figures from Summary Practice Test 2 in the text. The spreadsheet will use only two columns: 1) column A will be for titles, and 2) column B will be for formulas and data. In cell A1 type “Face Value:”. In cell A2 type “Discount Percent:”. In cell A3 type “Number of Days:”. In cell A4 type “Interest Charged:”. In cell A5 type “Borrower Received”. In cell A6 type “Amount at Maturity”. In cell A7 type “Effective Rate”. Adjust the cell width for both the titles and the formulas. Click on the rectangle above the row numbers and to the left of the column letters; click on FORMAT menu; click on the COLUMN command; click on STANDARD WIDTH and type in the number 20; click on the OK button. You will only see four columns on the screen now. Your spreadsheet will look like this: Finally, adjust the value format of cells B1, B4, B5, and B6 to CURRENCY format. Holding the CONTROL key (CTRL) click on cells B1, B4, B5, and B6. Click on the CURRENCY button ($). Adjust cells B2 and B7 to PERCENTAGES by clicking on cell B2 and hold the CONTROL key and click on cell B7. Click on the PERCENTAGE button (%). You will need to increase the decimals by one using the INCREASE DECIMAL button on the keyboard. Click once. Now we enter the figures and formulas. Remember, if you have any difficulty with formulas you should go to Appendix A. In cell B1 you enter the face value figure. In cell B2 you enter the discount percent figure. In cell B3 you enter the number of days. In cell B4 you enter the formula to calculate interest charged. In cell B5 you enter the formula to calculate amount borrower received. In cell B6 you enter the formula to calculate amount at maturity. In cell B7 you enter the formula to calculate the effective rate rounding off to the nearest tenth percent. The formulas will automatically calculate as you enter this data. You will find the interest rate charged is $302.22; the amount the borrower would receive is $16,697.78; and the effective rate will be 4.1%. PROBLEM: You go to a bank and get a simple discount note for $6,500. The interest rate is 6% for 100 days. Calculate the interest charged, the amount you receive, the amount the payee would receive at maturity, and the effective rate. Chapter 12 Problems We will be calculating present value for problems 12-7, 12-8, 12-9, and 12-10 Three columns need to have the value format and the width changed. In addition you will need to use the PV Table 12-3 from the textbook. Hold the CONTROL key down and click on the column letters A, D, and H. Click on the CURRENCY button ($). Immediately click on the FORMAT menu, click on COLUMN command, click on WIDTH choice, type in the number 12, and click on the OK button. Now click on cell F2 and decrease the number of decimals by clicking once on the DECREASE DECIMAL button. In cell A1 type “Amount”. In cell B1 type “Years”. In cell C1 type “Set Rate”. In cell D1 type “Compounded”. In cell E1 type “Time”. In cell F1 type “Rate”. In cell G1 type “Factor”. In cell H1 type “PV Amount”. We can now enter our formulas. If you have any problems with a formula see appendix A. In cell E2 type the formula for the period used. Take the years in cell B2 and multiply by 2 (semiannual). In cell F2 type the formula for the rate used. Take the rate in cell C2 and divide by 2 (semiannual). In cell H2 type the formula to give you the PV of amount desired at the end of the period. Take the amount in A2 and multiply it by the PV factor you entered from the table in cell G2. The spreadsheet should look like this: Now enter the data from Problem 12-7. In cell A2 enter the amount desired at the end of period. In cell B2 enter the length of time. In cell C2 enter the rate. In cell D2 type “semiannual”. The PV period used and rate used have automatically appeared. Using those figures, go to PV Table 12-3 in your book and type in the PV factor. (That factor should be .7885.) The final column for PV amount desired at end of period will automatically calculate and should be $2,050.10. You can now do problems 12-8, 12-9, and 12-10. Remember that you must adjust the formulas in cells E2 and F2 to reflect the new compounded time frame. See Appendix A if you have difficulty changing the formulas. An alternative approach would be as follows. This approach will allow you to calculate it solely with Excel without relying on the PVIF table. This technique has many advantages over the use of the table, because there are no restrictions in the length or chunk of timeframe or the number of decimal places in the rate as in the table. We build the table in a similar manner as in the previous method. However, this time we will directly enter the formula for PV in our solution cell. , where FV = Future Value or Amount at Maturity r = stated rate or set rate f = compound frequency per year n = number of years to maturity Now, the spreadsheet would look like this. In cell B2 enter the amount desired at the end of period. In cell C2 enter the length of time in years. If it is not in discrete number of years, such as 2 years and 3 months, we need to convert it into fraction of years such as 2.25 years. (3 months = ¼ year = 0.25 year) In cell D2 enter the stated rate or set rate. In cell E2 enter the compounding frequency per year. For example, if it is annual – i.e. interest is compounded only once at the end of the year, enter 1; if semiannual (compounded every 6 months), enter 2; if quarterly (every 3 months), enter 4; if monthly, enter 12. We need to adjust the stated annual rate to an appropriate rate for each compounding period by dividing the stated rate by this frequency, because the state rate of 4%, for example, certainly won’t accrue every quarter, but rather by 1% per quarter. In cell F2, enter the above formula for PV. In our problem 12-7, it should be entered as “=b2(1+d2/e2)^c2*e2”, where “^” symbol represents “raised to power of…”. Once you have solved 12-7, the rest of the problems can be solved automatically by copying and pasting the formula into cells F3 through F5 if the given data are already filled in for the rest of the problems. PROBLEM: The amount desired is $3,000 held for 5 years at a rate of 6%. What is the PV mount if the rate is compounded monthly? Quarterly? Annually? Chapter 13 Problems Problem 13-23 places Joe Martin in a difficult situation. His uncle will give him cash or money in an annuity. Joe’s problem is to decide which is better. First we change the cell widths by clicking on the SELECT ENTIRE SPREADSHEET box with is the small rectangle to the LEFT of the column A letter and above the row 1 number. Now click on the FORMAT menu; click on the COLUMN command; click on WIDTH choice; type the number “12”. Now hold the CONTROL key (CTRL) and click on cells A3 and E2 and click the CURRENCY button ($). Lastly hold the CONTROL key (CTRL) and click cells A5 and C2 and click the PERCENTAGE button (%). In cell A1 type “Quarter”, in A2 type “Amount”, in cell A4 type “Interest” and in cell A6 type “Years”. In cell C1 type “Rate”, in cell C3 type “Periods”, in cell D1 type “Table 13-2” and in cell E1 type “Annuity”. Now you are ready to enter the formulas you will need. If you have any problems with the formulas see appendix A. In cell C2 enter the formula to compute the interest rate. This is the interest in A5 divided by four. In cell C4 enter the formula to compute the number of periods. This is the years in A7 times four. In cell E2 enter the formula for the annuity. This would be the table rate in cell D2 times the quarter amount in A3. The spreadsheet looks like this: We enter the data from the text. In cell A3 enter the amount of the gift ($900). In cell A5 enter the percentage. In cell A7 enter the number of years. In cell D2 enter the table value from Table 13-2. When you compare the annuity amount in cell E2 with the cash gift of $12,000 the choice is obvious. Joe should take the annuity. As is the case with Chapter 12, an alternative approach is to enter the Present Value of Annuity (PVA) formula directly into the solution cell. Once again, the benefit of this approach is the freedom from dependency on the table that is bound to limitations in terms of timeframe and precision in the range of rates… etc. Also, the accuracy in the values we solve for is an added advantage of this technique. The PVA can be defined as follows: ,where PMT = regular annuity payment r = stated annual rate adjusted for the frequency of payment per year n = number of payments or if we factor out , which is exactly how the PVAIF (Present Value of Annuity Interest Factor)is calculated in the table. Now, we build the following table, where int = stated APR or set rate f = compound or payment frequency per year r = stated annual rate adjusted for the frequency of payment per year t = number of years to maturity n = number of payments to maturity Now, enter the data and you will instantly have the result exactly to the cent as follows. PROBLEM: Your Great Aunt Pheobe sends a gift on the birth of your first child. She is offering $10,000 or a quarterly annuity of $1,000 for three years. At 8% interest for the annuity, which would you take? Chapter 14 Problems When purchasing an automobile, most of us will pay over time. This spreadsheet will find the finance charge, deferred payment, monthly payment, and the APR. We will use Problem 14-10 from the text to buy our PT Cruiser LHS 4 door sedan.. First we widen all the columns. Click on the FORMAT menu; click on the COLUMN command; click on STANDARD WIDTH; type the number 16; click on the OK button. Now set the currency format. Hold the CONTROL key (CTRL) down and click on B1, B2, B4, D1 and D2 being careful not to let go of the CONTROL key. After clicking on all cells let go of the CONTROL key. Click on the CURRENCY button ($). In cell A1 type “Purchase Price”. In cell A2 type “Monthly Payment”. In cell A3 type “Number of Months”. In cell A4 type “Down Payment”. In cell A5 type “Trade-in”. In cell C1 type “Amount Financed”. In cell C2 type “Finance Charge”. Your spreadsheet should look like this: Now we are ready to enter the formulas. If you have any problems with the formulas see appendix A. We will now enter the data from your text. In cell B1 enter the selling or purchase price of $17,270. In cell B2 enter the monthly payment of $296.69. In cell B3 enter the number of months of 48. In cell B4 enter the down payment of $1,500. In cell B5 enter the Trade-in value of $3,500. In cell D1 enter the formula for the amount financed. In cell D2 enter the formula for finance charges. Another application of this type of problem is a model to find the regular (monthly) payment amount. While the above problem is mainly addressing the issue of finance charges (total amount of interest), which is a fairly simple and straight-forward task with everything already known, people are usually also keen to know if their monthly payment is an accurately calculated, fair and legitimate amount. It is called amortization problem, because it is usually the method used to amortize or pay down debt. Although the above example also addresses the monthly payment, but the method relies heavily on the look-up factor table (PVAIF), which is rather crude and cumbersome, since the calculation is bound to be severely restricted by the range of the table and the precise degree of decimal places used in the table. Besides, the table is not something ubiquitously available. It cannot be conveniently carried around on you any time any where. These are certainly just a few of the many shortcomings of the table method. For example, if you finance your car, you owe the finance company the dealership price of the car plus the finance charges, which is equivalent to the purchase price in the above format. So over the next X-number of months (periods), you amortize or pay down this amount in X-number of equal payments. Although this monthly payment amount is always the same, the way it is structured – i.e. the ratio by which it is split between principal owed (debt or the dealership price) and finance charges (interest) - changes over time. It is usually structured in such a way that the most interest is to be paid early on and to decrease as time goes by, whereas the least principal is to be paid early on and to increase as the number of payments advances. Viewed from the finance company’s perspective, this is exactly the annuity, because they are receiving a stream of even payments periodically until the full maturity value is paid up. So, this is exactly the flip side of the same coin, and the solution is the reverse process of the PVA problem. In other words, in a PVA problem, our objective is to solve for the PVA, given other variables. In an amortization problem, the objective is to solve it for the PMT, given other variables. We know that the PVA can be found by the following formula: ,where PMT = regular annuity payment r = stated annual rate adjusted for the frequency of payment per year n = number of payments or And since it is the PMT this time that we want to find out while everything else is known, all we need to do is solve this equation for PMT: So, we can now apply this formula to Problem 14-10 assuming that all else is given to solve for the monthly payment. If this figure is equal to the value already given in the problem, our solution checks out. Now the solution can be programmed as follows. Once the given data are entered, then the solution is obtained instantly. Our computed solution is a little different from the solution obtained from the table. This is mainly the rounding error, but the computed solution is undoubtedly the most accurate, because the value from the PVAIF look-up table itself wasn’t the exact value to begin with. (Remember that sometimes the values in the table are at best approximations, not exact to the dot, so we are forced to take the value that is the nearer of the two adjacent values in the table. - Cf. LU 14-1) That is why there’s a clear benefit in programming the formula into the model. We may also formulate it alternatively as follows. PROBLEM: You see a Ford Thunderbird for sale with the following terms: Selling price of $28,500; $2,000 down; Financed for 48 months; with a monthly payment of $565.50. The APR is listed at 6.5%. Check to see if the dealership has the right figures for this car. Chapter 15 Problems Using a spreadsheet you can quickly calculate monthly payments for a home mortgage. We will be using the information from problems 15-1, 15-2, and 15-3 in your text. The first step is to change to currency in columns A, B, C, F, and G. Hold the CONTROL key and click on the column letters A, B, C, F, and G making sure NOT to let go of the CONTROL key. Let go of the CONTROL key and click on the CURRENCY button ($). In cell A1 type “Selling Price”. In cell B1 type “Money Down”. In cell C1 type “Principal”. In cell D1 type “Interest Rate”. In cell E1 type “Years”. In cell F1 type “Payment”. In cell G1 type “Per Month”. Now widen all the columns. Select the ENTIRE spreadsheet by clicking on the rectangle to the LEFT of the column A letter and above the row 1 number; click on the FORMAT menu; highlight the COLUMN command; click on the AUTOFIT SELECTION choice. Now click on the letter at the top of column C; click on the FORMAT menu; highlight the COLUMN command; click on WIDTH and enter the number 13.50 and click OK. Now we can enter the formulas. If you have any problems with the formulas, see appendix A. In cell C2 enter the formula that will calculate the loan principal using the selling price minus the down payment. In cell G2 enter the formula to calculate the monthly mortgage payment using the loan principal divided by 1000 and multiplying by the payment. Your spreadsheet should look like this: 013716000 Enter the information in Problem 15-1 into the proper cells. In cell F2 enter the number from TABLE 15-1 for the payment per $1,000. Now you can substitute the values from problems 15-2 and 15-3. This problem can also be done in the same manner as in Chapter 14 problems. We apply the same PMT formula. Build the table as follows. Once we enter formula in D2, G2, I2 and J2, we don’t need to enter one for each problem. All we need to do is copy the desired cell and paste it over the desired range. We can automate it by first placing the mouse on the lower right-hand corner of the cell border, and then the thick white cross cursor will turn into a thin black cross. It means that Excel is ready to copy that cell. Hold the left mouse button and drag it down over the range of cells to paste it into. Once we fill the cells with the relevant values, the answers will instantly show up. Again, there may be a slight difference from the values obtained from the PVAIF look-up table due to rounding. However, the numbers in the J column are the most accurate monthly payment figures, because they are calculated to the utmost degree of precision with no rounding. PROBLEM: You plan on buying a house for $175,500 and you have $30,000 for a down payment. The loan is for 25 years at 6.75%. What is your monthly payment? Chapter 16 Problem In Chapter 16 we will complete problem 16-3: A horizontal analysis for the J. Brown Company. In column A type each of the labels starting with “Assets” and ending with “Total liab. and owner’s equity”. In cell B1 enter “Year 2008” and in cell C1 enter “Year 2007”. In cell D1 enter “Amount Change” and in cell E1 enter “Percent Change. Next adjust columns B, C, and D for currency. Click on B1. Hold the CONTROL key and click on the letters B, C, and D. Once the three columns are active let go of the CONTROL key and click on the CURRENCY button ($). Now we change column E to percentages. Click on the letter E at the top of the E column and then click on the PERCENTAGE button (%). Adjust the columns by clicking on the rectangle beside the column letter A and above the row number 1. Next click on the FORMAT menu; highlight the COLUMNS command; and then click on AUTOFIT SELECTION choice. You will need the following formulas. If you have any problems with the formulas, see appendix A. In cell B7 enter the formula to add the numbers from cash to prepaid advertising and copy formula to cell C7. In cell B11 enter the formula to add building and land values and copy formula to cell C11. In cell B12 enter the formula to add total current assets to total plant and equipment and copy formula to cell C12. In cell B17 enter the formula to add accounts payable and salaries payable and copy formula to cell C17. In cell B20 enter the formula to add total current liabilities to mortgage note payables and copy formula to cell C20. In cell B23 enter the formula to add total liabilities and owner’s equity and copy formula to cell C23. In column D enter the formulas to calculate the dollar amount of increase or decrease by using the 2004 amount minus the 2003 amount. In column E enter the formulas that will calculate the percentage of change by using the dollar amount of the increase or decrease and dividing it by the 2001 amount. Your spreadsheet will look like this: Using the figures from your text, enter the values into the appropriate cells. As you fill in each value the formulas will calculate automatically. Some cells will display a series of pound (#) symbols but they will be replaced by the correct figures as you finish entering all the data. One last adjustment must be made to the percent column (column E). We need to increase the decimals to two decimal places. Click on the letter E at the top of column E. Now click TWICE on the INCREASE DECIMALS button. More about Financial Statements There are largely four major types of financial statements, namely Balance Sheet, Income & Expenses Statement (or simply Income Statement), Cash Flow Statement, and Retained Earnings Statement. These statements are usually updated and reported quarterly. Any publicly traded company must disclose these statements to public - their current or prospective investors (shareholders & stakeholders in general) - at least once a year in the annual report, and file an annual 10K report, which is a kind of annual report, with the SEC. Some firms also file 10Q report quarterly. 1. Balance Sheet A typical balance sheet consists of two aspects of the financial make-up of a firm: Assets vs. Liabilities & Equity. When a firm starts up, it needs start-up capital or initial investment. How the founding members of the firm raise that capital depends on the fund availability. The capital thus raised can be used to rent, lease or purchase office equipment, the building for offices & plant, company vehicles, operating expenses during the start-up period including the lead time… etc – i.e. they invest all of it initially in the business. If you purchased these items, they are your (the firm’s) property, and will be valued at their purchase prices. They will be entered into the record (the book or the balance sheet) initially at these values, hence referred to as book value. These items are also called assets as they will be asset to your business that will be used to generate income for you. Balance Sheet Items Balance sheet is mainly divided into two sides, Assets vs. Liabilities & Equity. Under each of these categories, line items are listed in the order of liquidity. The assets side shows what asset form the initial infusion of capital is held in – i.e. how it got used. The liabilities & equity-side shows where that initial capital came from. The assets side usually consists of all the liquid assets starting with cash and all the physical assets such as equipment (production and/or office), buildings (plants and/or offices), land… etc. In the above example, liquid assets are also labeled “Current Assets” which also include inventory. Inventory in general is not liquid by its inherent nature, but is considered part of current asset in the sense that it normally doesn’t take as much time as such physical assets as land or buildings (Non-Current Assets) to liquidate. After all, if the firm is having a cash flow problem, the firm will try to stave it off by liquidating its current assets – i.e. it tries to convert whatever it can into cash to meet the payment obligation that caused this cash shortage in the first place. Stocks, bonds, notes will be sold for cash, and accounts receivable can also be sold at discount. If it still doesn’t meet the cash need, the firm will even start selling its physical assets after liquidating inventory. The liabilities & equity side consists of all the liquid-type liabilities first, physical or non-liquid type, and finally owner’s equity. The liquid types are called Current Liabilities, and the non-liquid types are called Non-Current Liabilities. The current liabilities generally consist of short-term debt obligations the firm has created through sale of these debt instrument issues. The non-current liabilities consist of long-term debts the firm incurred through bank loans, private placements, or bond issues usually publicly traded in the established bond exchange. In building the above balance sheet table, it is important to understand that one only needs initial data set, not all, to begin with – i.e. the subtotals and totals don’t need to be manually entered. These data, of course, are basically collected from the firm’s books & journals. For example, let’s assume that we are given only some initial data to begin with as in the following table. This means some data may be missing. This table would be a more realistic picture of what one might encounter right after the data have just been gathered from the firm’s bookkeeping record, and entered into the spreadsheet. It is obvious at a glance that the subtotal cells and the total cells are blank and still to be filled in. These cells are highlighted in colors, meaning that they are to be solved for. Of course, in real practice, these cells won’t necessarily be highlighted, but it would be visually helpful to highlight them for instructional purpose. Now, finding the solution for these blanks is a fairly straightforward and easy exercise with Excel. There are several ways one can obtain the solution for this summation problem as follows. Excel has a feature called “Autosum” denoted by the summation symbol “” in the tool bar. The toolbars may be expanded or collapsed depending on the MS Office version, and hence, the icon may be hidden, but in any case one can easily locate it by clicking on the double arrow tip of the toolbar. Once the “” icon is located, then move the cursor to the last cell, where the summed result should be entered, at the bottom of the data string to be summed up. Then, click on the “” icon, et Voila! The result is automatically computed. (See Fig II.A.1.c. Autosum) You can also perform the same operation step by step using basic Excel commands. Place the cursor on the target cell to enter the summed result. Enter equal sign “=”. Once you enter the equal sign, Excel shifts into the formula mode. That is to say, that Excel expects a mathematical expression in the cell. Now enter the Excel command “sum” followed by a parenthesis, so that the cell would now have an expression “=sum( )” in it. The interpretation of this expression is that the data in the current cell is the sum of certain values. Now, it’s time to designate the cells that contain values to go in the parenthesis. The values to fill the parenthesis are contained in a range of data, so we will designate that range. To do this, move the cursor to the first cell in the contiguous range of data in a column or in a row to highlight the data range to sum. (See Figs II.A.1.d. & e.) Last but not least, the crude, no-frill but straightforward way to do it is quite intuitive and fail-safe. Place the cursor on the cell to enter the sum. Enter “=” and all the cell numbers to sum with “+” in between. In this example, to find the solution for Total Current Assets, enter “=b4+b5+b6” in the solution cell b7, and you will obtain the same result as in ii) and iii). (See Fig II.A.1.f.) Designating a range to sum data & clicking on green “Enter” checkmark Summing the data mathematically Therefore, managing the balance sheet is made a lot easier and routinized that would otherwise have been a tedious number crunching. It saves you trouble of constantly making sure all the numbers are right. If there was any data entry error, the result can be instantly adjusted once you single out only the wrong data and update it. Just keep track of the record for any change in data until the next update, and you will always have an automatically adjusting statement that is accurate and correct to any degree you set it up to. Horizontal and Vertical Analysis As the firm operates through time, the management wants to know how well it has performed and where it stands. In terms of growth or decline in the size of the firm on annual basis, this can be easily achieved by comparing the balance sheets over a two-year period. This is usually called “horizontal analysis”, as one can check side by side the change in the same line item over time. It may be also considered a kind of “comparative statics” to borrow the terminology from economics, because what it does is to compare the status of the firm between two static points in time. No matter what you may call it, the important thing to remember is that all you need is the initial data set, not the entire set of numbers, to build this table. i.e. you are not just entering the raw data and the results calculated elsewhere manually and tediously. The idea is that you only sow the data seed and make Excel do all the rest. The above table is simply a finished product, and would not look, at the beginning, as it appears now. So, let’s start at the very beginning. First, we need initial data set compiled from the book periodically. This does not mean in the least that this step must be done only manually. Once we network everything into a database system, all the raw data gathered periodically will be entered directly from the source into the system, automatically linking and updating the data we need constantly. In the absence of such a system, however, we will have to manually enter only the initial data into the table. As you can see, the initial data are already in place, and the line items that need to be solved for are highlighted. These line items are not difficult to solve for at all as they were already covered in section II.A.1.a. Simply by using “autosum” command, you will be able to have Excel fill in the blanks instantly. The rest of the blank line items can be filled also in the same way. Instead of hitting “autosum” every time, you may also use “copy and drag” to find the solution for the adjacent cell. i.e. copy and drag the formula in the cell “Total Current Assets” (TCA hereafter) for 2004 into 2003. “Total Assets” (TA hereafter), by definition, can be found by summing TCA and TNCA. However, you may not simply autosum everything, because there are already subtotals (TCA and TNCA) in the column. An “autosum” will simply double-count all the items. Therefore, it is absolutely necessary to evaluate critically the operation you are performing, and not to follow it just mechanically. No matter how sophisticated the software, it eventually behooves on every individual that implements the program to make sure that the program is executing the correct set of operations. You can do the same to the liabilities side for both 2004 and 2003. You may also copy and paste, but this will work only if the number of line items on both sides is the same. However, we have a small problem with the equity for 2004, because the data is not given. So, what do we do to find the equity? We have a clue here. By definition, the “Total Liabilities + Equity” (TL+E hereafter) must be equal to the “Total Assets”(TA hereafter), so we have this item already solved for. Therefore, all you need to do is to make a reference to the cell for TA in TL+E cell, and then put the difference between the TL+E and the TL in the “Equity” cell. The rest of the line items for 2003 can be solved in the same way, or you may also enter “=sum(c25,c27)” in c29 for TL+E. Now, the next column is the comparative statics. The columns D and E will show the change – increase or decrease in both dollar amount and percentage – in these line items over time (1-year period to be exact). It is an accounting convention to put the negative numbers in parenthesis or in red to represent the negative change or decrease in value. Excel is preset to use parenthesis or red font by default although you may also set it to use minus sign. To find the change in dollars, all you need to do is subtract 2003 data from 2004 data after “= sign” for just the first row to begin with. Once the computed result appears in the target cell, then copy, drag and paste it down the column. To find the change in percentage, divide the result obtained in the previous operation (change in dollars) by the line item in the previous year. This is because the previous year’s value is the base of the change. Finally, as a cosmetic step, clean the cells that contain no data such as d10, d16, d17, d20, d21, d26, and d28. They are actually blank cells that contain no data, but accidentally picked up a hitchhiker ($ sign) in the process of copy-drag-&-paste. If you don’t want the nuisance, do not copy & drag, but just copy & paste only where necessary. v) Let’s have a brief overview of this horizontal analysis. Over a year period, TA increased 11.11%. This also means its counterpart (TL+E) has also increased by equal amount. This is definitely a good sign, because it means that the firm grew by as much. It is all the more a happy news because this growth is mainly due to 87.5% growth in equity. On the other hand, the TL rather decreased by 2.17%. Put two and two together, one can easily understand that the firm’s growth is due to good business, and that at least based solely on this one-year horizontal analysis, this was certainly a viable business for the given year. You can expand this type of evaluation to the rest of the line items. Now, let’s move on to the vertical analysis. Vertical analysis refers to finding the proportion of each line item in the composition of the total entirety in a given year. Therefore, it expresses each line item as a fraction of the TA or TL&E, and helps us evaluate if any particular item is in optimal proportion. Of course, this type of judgment is rather discretionary although there is a general rule of thumb for the sector or the industry average. The figure below demonstrates vertical analysis. The concept as well as the technique is simple. To find the fraction of cash in TA for 2004, enter “=b5/b$15” in the target cell f5. Remember to lock the cell b15 by inserting a “$” sign before 15. It is self-explanatory why we do this. We want to avoid entering the same b15 repeatedly just because all the line items on the asset side need dividing by the TA. Then, move the cursor to the lower right corner of the cell border, wait for the thick white cross to turn into a thin black cross, and drag to copy all the way down to the TA row. Repeat the same process for the liabilities side as well. This time, remember that the denominator of the fraction must be b$29, TL&E. Et voilà! You have the vertical analysis for 2004. For 2003, you will repeat the same process with the 2003 data. This time, since we are dealing with vertical analysis for 2003 only, the columns that were used for horizontal analysis are not immediately needed, and hence, may be collapsed to hide some columns to save the space. This is done by simply placing the cursor on the border between the two column labels such as E and F, and when the cursor turns into a thin black cross with two-way arrow arm, dragging the border line left until columns D and E are completely eclipsed by the column F. One can easily tell whether the firm had unusually high cash position or not in 2003…etc. As a general rule, we use only up to two decimal places – i.e. round up to the nearest hundredth – for the consistency in the format. This can be done simply by highlighting the entire table and clicking on the decimals icon on the toolbar to the desired level. Income & Expenses Statement We now move on to the Income & Expenses Statement or simply Income Statement for short. The income statement shows all the sources of income and the uses thereof (expenses). One must not confuse Income Statement and Balance Sheet. The balance sheet is not about how much money a firm makes, but about how it raised capital and what asset forms they are held in, whereas the income statement is all about how much money (gross income) the firm brings in, how much it spends, and hence, how much profit (net income) it ends up with. It is reported quarterly as well as annually, but the data are collected and compiled constantly for internal managerial purposes. Two-Step Profit Statement The above is an example of one type of Income Statement. We will call the following format “Two-Step Profit Statement” or “Type A” for short for the reasons to be discussed below. Once again, those highlighted areas were not given to begin with, and need to be solved for, which will be discussed later in this section. First, as a matter of convention, most accounting practices would follow two-column table which places all the “money-ins” (income) in one column and all the “money-outs”(expenses) in the next column, but we will use a single column format for ease of computation. Let’s review each line item by broader groups. The first line item is the “Gross Revenue”, which is the sales revenue before any adjustments. The adjustments are mainly “Returns” – i.e. all the goods returned for which refund was made, and hence, a minus item from your income list – and “Allowances”, discounts the firm gave to the customers or the distribution channel members, also a minus item from your income list. Then, there is usually an investment income item, which is not the case with this firm. When all these line items are summed up, then the “Net Revenue” is obtained. Then, as we move down to the “Expenses Account”, the first item is the “Beginning Inventory”. However, you need to look past this item to see the big picture, because the first major expenses item is the “Cost of Goods Sold” (Total CGS), not the “Begin Inventory”. Regardless of the type of business – manufacture or retail, firms must sell goods to bring in income. When a manufacturer produces goods, they do not know with accuracy how many units will be sold, so their target production level may overshoot or undershoot the actual sales level. The production cost may vary in each case, but as long as they sell some quantity, it brings in revenue or income. Lines A7 through A11 show how this Total CGS is obtained: The firm starts a month or a quarter with the beginning inventory (A7), which is actually the ending inventory of goods carried over from the previous period. This may not be sufficient to meet the expected sales for the current period, so the firm places a new purchase order (A8) with its supplier (distributor or vendor). Some of these new orders may be defective or wrong item, so the firm returns them or gets some discounts (A9). Then, the sum of these three items makes the “Goods Available for Sale” (A10 GAS), and all the goods they sold during the period make the Total CGS (A12). So, by definition GAS less CGS will give “Ending Inventory” (A11). Logically, End Inventory follows CGS, but for our purpose, the example switched the order so that we can end the analysis of our first major expense item with CGS. In this case, the End Inventory must be subtracted from the GAS to arrive at CGS. From i) Net Revenue, if we subtract ii) Cost of Goods Sold, we get the Gross Profit. It is not the real profit yet since there are still expenses that are not directly related to production or procurement of goods, but simply with administrative/ operational aspect of the firm. These Other Expenses or Operating Expenses are such expenses as Salaries, Rent, Utilities, Administrative, and Depreciation… etc. Subtracting v) Total Other Expenses form iv) Gross Profit, we arrive at Operating Income. Only now are we free from any expenses. However, we are not yet free from Interest Payments to the outstanding loans or bonds that we have as part of our liabilities, nor from Taxes. Fortunately, our sample firm doesn’t owe any interest payments. Subtracting vii) Interests and Taxes from vi) Operating Income, we finally arrive at the “Net Profit”. Income Statement Practice Now, let’s have a hands-on number-crunching experience with income statement. The following table is a simulation that contains only some data with the rest missing. Your job is to piece together the puzzle and find the missing values. At a first glance, you might be clueless as to what should be the net revenue much less the gross revenue. You simply don’t seem to have enough data. However, if you look a few rows down, you will see TTL CGS and Gross Profit. Now, as Gross Profit = Net Rev – TTL CGS, you can figure out Net Rev = Gross Profit + TTL CGS. Once you’ve found Net Rev, then Gross Sale (Gross Rev) is automatic. Since Gross Rev – Returns = Net Rev and you have data for Net Rev and Returns, Gross Rev = Net Rev + Returns. Keep in mind, however, that Returns in already in negative, so if you add them up, it will be like subtracting Returns from Net Rev. To prevent it, you’ll need to put a minus sign before Returns, and enter “=b4-b3” in b2, and Voilà the Gross Sales. The next line item is Purchase, which can be obtained by subtracting Begin Inventory and Returns & Allowances from Goods Available for Sale. However, GAS data is not available yet, so this item cannot be solved for just yet. However, the following clues now emerge as we move on to solve for GAS. Then, as TTL CGS = GAS – E.I., and we have data for both CGS and End Inventory, now we can solve for GAS = TTL CGS + E.I. Since GAS = B.I. + Purchase – Returns, and we now have data for both GAS and B.I., so Purchase = GAS – (B.I. + Returns). Also, pay attention to the algebraic sign of Returns. It is already negative, so you’ll need to make adjustments accordingly as in ii). Now, we move on to the Expenses. However, the first missing item, depreciation, comes with no immediate clue. Since “b21= sum(b16:b20)”, meaning that the operating expenses is the sum of salaries through advertising expenses, we need data for Operating Exp before finding out Depreciation. So, we need to figure out Operating Expenses first. Fortunately, you have a clue to Operating Exp in the next column. Cell C21 indicates that Operating Exp is 15% of… Net Rev. How do we know it is 15% of Net Rev? Well, 15 out 100%, which is the Net Rev in b4. Now enter “=b4*c21” in b21, and you have the Operating Exp. Then, Salaries is simply operating exp – sum of all other expenses excluding salaries, which can be solved for by entering “=b21-sum(b16, sum(b18:b20))” in cell b17. You now have data to solve for Operating Income, which is simply Gross Profit – Operating Expenses. Finally, Net Income = Operating Income – (Interest + Taxes), where interest payment is 0 in our example. PROBLEM: Using any figures of your own, replace the figures from Problem 16-3 on your spreadsheet. Chapter 17 Problems In Problem 4 of the Summary Practice Test we will calculate depreciation and difference between the book value and amount Victor of Prestige.Com received using the straight-line method. In cell A1 type “Purchase Price”. In cell A2 type “Life Expectancy”. In cell A3 type “Years in Service”. In cell A4 type “Residual (Salvage) Value”. In cell A5 type “Selling (Resale) Price”. In cell A6 type “(Accumulated) Depreciation”. In cell A7 type “Book Value”. In cell A8 type “Difference” Click on the letter A at the top of column A and then click on the FOMAT menu, highlight COLUMN command and click on AUTOFIT SELECTION. Next hold down the CONTROL key and click on cells B1, B3, B5, B6, B7, and B8. Now let go of the CONTROL key and click on the CURRENCY button ($). Now you will enter the formulas. If you have any problems you will find the formulas in appendix A. In cell B6 enter the formula to calculate the straight line figure by taking the purchase price, subtracting the residual value, dividing by the life expectancy and multiplying by the number of years in service (owned). In cell B7 enter the formula for the book value which is the selling price minus the depreciation. In cell B8 enter the formula to calculate the difference by taking the book value and subtracting the selling price. Your spreadsheet should look like this: Now enter the values from the problem. In cell B1 enter the Toyota’s purchase price. In cell B2 enter the life expectancy. In cell B3 enter the years in service (owned). In cell B4 enter the residual (salvage) value. In cell B5 enter the selling (resale) price. The straight-line depreciation book value is $13,000 and the difference is $6,000. We can also easily build the depreciation schedule as follows. Once the given data are entered, then the schedule will automatically be calculated as follows. PROBLEM: You are a mechanic and buy a tow truck. The cost is $45,000. The life expectancy is 15 years and there is a residual value of $7,500. You sell the truck after 8 years for $15,000. Using the straight-line method what would the difference be between the book value and the selling price? Chapter 18 Problems We will do inventory accounting using the Last In, First Out (LIFO) method found in Problem 18-21. In cell A1 type “Footlocker’s Sketchers Inventory”. In cell A3 type “09/01:”.* In cell A4 type “Quantity:”. In cell A5 type “Cost:”. In cell A6 type “Total:”. In cell A8 type “11/05:”. In cell A9 type “Quantity:”. In cell A10 type “Cost:”. In cell A11 type “Total:”. In cell A13 type “12/04:”. In cell A14 type “Quantity:”. In cell A15 type “Cost:”. In cell A16 type “Total:”. In cell A18 type “Total Value:”. In cell A19 type “Ending Inventory (Q’ty):”. In cell A20 type “Ending Inventory ($):”. In cell A21 type “Cost of Goods Sold:”. * If the calendar format is not set as default, click on “Format” on the toolbar, select “Cells”, and under the ‘Number” tab, select “Date”, and select the appropriate date format in the menu box. We have changed the width of column A by clicking on the FORMAT menu; select COLUMNS; select AUTOWIDTH. You will see by using the centering and right alignment controls you can move the titles to different positions. Now widen column B by clicking on FORMAT; selecting COLUMNS; select WIDTH and type in 16. Now enter the values from the text. Again, if you have any trouble with the formulas you will find them in Appendix A. In cell B4 enter the number of pairs of the first purchase. In cell B5 enter the cost of those shoes. In cell B6 enter the formula to calculate the total. In cell B9 enter the number of pairs of the second purchase. In cell B10 enter the cost. In cell B11 enter the formula to calculate the total. In cell B14 enter the number of pairs of the third purchase. In cell B15 enter the cost. In cell B16 enter the formula to calculate the total. In cell B18 enter the formula to total the entire cost of all shoes. In cell B19 enter the number of shoes in ending inventory. In cell B20 enter the formula to calculate the cost of the ending inventory. In cell B21 enter the formula to calculate the cost of goods sold using the LIFO method. You should have as answers $2,998.91 as the total cost, $1,457.00 as the cost of the ending inventory, and $1,541.91 as the cost of goods sold. We can use an alternative method to solve the problem as follows. With this method, one can find inventory values by all three inventory valuation methods (Average cost, FIFO, and LIFO) as well as the resulting Cost of Goods Sold respectively. The following table produces automatically the desired solution values. PROBLEM: You work for a store that received 15 scented candles at $12.00 each on Monday; 10 scented candles at $25.00 each on Tuesday; 20 scented candles at $15.00 on Wednesday. Calculate your total cost. Now on Thursday you find you have 30 candles left. Calculate the ending inventory and the cost of goods sold using LIFO. Chapter 19 Problems We will use problems 4 and 6 from the Summary Practice Test. Before building the table, enter problem numbers in cells A1 and A5 as follows. Enter appropriate labels for each problem in column B. Column widths can be automatically adjusted by double-clicking on the border between column heads. Now enter appropriate data and formulas in column B as follows. Once the data are filled in, the solutions are automatically obtained. PROBLEM: Modify the above format accordingly and do problems 3 and 5 in the Summary Practice Test. Chapter 20 Problems We will use problems 20-8, 9 and 11. Enter appropriate labels for each problem in column A. Enter appropriate labels for each problem in column A. Column widths can be automatically adjusted by double-clicking on the border between column heads. Now enter appropriate data and formulas in column B and C as follows. One thing to bear in mind is that although the factors by the table are for every $100 or every $1,000 (Life insurance), we would rather use factors for every $1. It won’t be too much of a trouble to revert them back to factors $1 basis. (This applies to all factors except the short-rate, which is nothing but a straight-forward %.). All we need to do is simply divide them by 100 or 1,000 accordingly. This way we can put everything back on the level playing field, and not bother any more whether it is a life insurance problem or fire insurance problem. Once the data are filled in, the solutions are automatically obtained. PROBLEM: Modify the above format accordingly and do problems Practice Quiz 1,2 and 3. Chapter 21 Problems We will use problems 21-19. Enter appropriate labels for each problem in column A. Column widths can be automatically adjusted by double-clicking on the border between column heads. Now enter appropriate data and formulas in column B and C as follows. Once the data are filled in, the solutions are automatically obtained. Tracking a Stock What would be the most critical factor in picking a stock? Rate of return, Of course! Then, how do you find the rate of return (or simply return)? Well, if you bought a baseball card for $100, and sold it some time later for $110, you made $10 out of it. That $10, or the proceeds from that sale, is the return, and 10% in %. Why is it 10%? Since the return, $10, is 1/10 of the base ($100), or what you paid for it in the first place. This gives you a simple and straightforward formula for “r”, the rate of return as follows: where Pt is the price at current period, Pt-1 is the price at previous period. Originally, the formula should also include “Dividend” in the numerator as , but since we will use “Adjusted Price”, which has the effect of dividend payout already incorporated in, it wouldn’t matter at all to leave it out of our example. Let’s have an actual hands-on exercise in finding the rate of return. Go to yahoo.com and click on Yahoo Finance. Once you are there, you will see a small window to enter ticker symbol to get the stock quotes. We want to see the price quotes for Microsoft stock, so we enter “msft”, which is the symbol for Microsoft. Then you will see the following result. First, click on the max under the graph to see how the Microsoft has been behaving since it was first listed in the stock exchange. Click on “Linear” for the scale, and “L” for the size, and the graph will appear as follows. It is visually clear that over ten year period between 1990 and 2000, the stock grew by almost 6,000% even after splits, and even after 2000, the stock is still trading around $20~ $30 range even after several splits, which is still about 2,000~3,000% growth without considering splits. Now, click on the “Historical Prices” link in the left-hand side panel, and once you are in the new page, select data frequency as monthly, and set the data range to include from Jan 01, 2000 to date, and then click “Get Prices”. Once you have the desired results, scroll down to the bottom of the screen and click “Download to Spreadsheet”. When the dialogue box opens, click “Save”, and select My Documents folder to download the file into. Save it under a file name that is relevant to the data and easy to remember. You may also create a new folder just for the downloaded data. Now locate and open the downloaded file from the folder you saved it in. Once you open the file, highlight the column headings and delete all columns other than “Date” and “Adj. Close*” columns. “Adj. Close*” means that it is the closing price adjusted for dividend payouts and stock splits. Since we are interested in finding the rate of return, let’s apply what we have intuitively understood to the data available to us. As defined above, return is where Pt is the price at current period, Pt-1 is the price at previous period. So, if we rename the “Adj. Close” Pt, then Pt-1 is the Adj. Close just one period prior. To facilitate the calculation we will copy column B from B3 all the way down into column C, we will have Pt and Pt-1 next to each other. Now in cell D2, enter the equal sign (=) followed by the formula for return with each variable referring to the cell containing relevant data – i.e. B2 for Pt, and C2 for Pt-1. Pay attention to the use of parenthesis to avoid any error. Excel will execute multiplication and division before addition and subtraction by default, so without enclosing the numerator in the parenthesis, Excel will recognize only C2 as the numerator, and perform C2/C2 first and then subtract it from B2. The computed result will appear simply as a ratio, so we will need to convert it into %. It can easily be done by clicking on the % icon on the tool bar. You may also increase or decrease decimals, but we will set it to two places by convention. Now that we have the monthly rate of return for June 1 through June 30 (July 3 in this example), we can copy and paste the formula over the entire data range to obtain monthly rate of return for each month. Once we have these monthly returns, they can also serve us by providing the average of this trend of returns during the period in observation. This average can be used as an approximation of the return to be expected in the future. To find average, take the following steps. First, locate the bottom of the returns data string. This is where the average of the returns will appear. Next to that cell, type in the descriptive label “Average”. Then, click on fx (past function) icon on the toolbar to locate the Excel command “AVERAGE”. You can also type it in the cell following an equal sign. (When you manually type it in, it doesn’t matter whether you use upper case or lower case letters.) Once you selected the desired command, then click “OK”. Now, the following dialogue box will open up. It’s merely asking you to enter the range of data that you wish to take the average of, so click on the spreadsheet icon in the first data window to highlight the data range into it. In our example it is N2 through N79, so if you highlight the exact range into it, Excel will automatically make it appear as follows. Then, hit “OK” and you will have the calculated average in the designated cell. Stock Valuation Ratios Valuation ratios are very useful tools for valuing stocks. There is a couple of ratios most commonly used, which are P/E ratio, PEG ratio and M/B ratio respectively. P/E (Price/Earnings) Ratio Also known as “earnings multiples”, P/E ratio is used to measure how cheap or expensive its share prices is. The lower the P/E, the less you have to pay for the stock, relative to what you can expect to earn from it. , where EPS = Earnings per share The price per share is the market price of a single share of the stock. The earnings per share is the EAT (earnings after taxes or net income or net profit all before dividend payout) divided by number of shares outstanding. One most common misbelief in the stock market is that a high P/E ratio is an evidnece of a growth stock. It is allegedly due to the anticipated high and rapid growth, which is factored into price, pushing up the (often irrational) demand for the stock. Consequently, it is only pushing the price even further resulting in high P/E ratio regardless of the actual EPS. Also, this misbelief gets coupled with the seemingly plausible rationale that the fast growing firm uses all its available funds for expansion, leaving no room for earnings in the short run, which will be more than handsomely rewarded afterwards when the expansion is completed. However, nothing could be more grossly misguiding. The P/E ratio certainly helps one to analyze the market's valuation of a firm's shares relative to the profit the firm actually generates, but not everyone understands that there are only two conditions, by its own mathematical construct, under which P/E can be high: i) Both P and EPS are high, but the P is far greater than the EPS. ii) P is high while EPS is minimal or none. If it is i), there is no reason for concern. It is exactly the situation you want. However, if it is ii), then what drives P up is only the “irrational exuberance”, and this is certainly not the ideal stock to hold no matter how high P/E is. For this reason, it is probably the most consistent signal to excessive optimism and over-investment. One reason to calculate P/E is to compare the value of stocks within the same industry sector. If one stock has a P/E twice that of another, it is probably a less attractive investment. But comparisons between industries, between countries, and between time periods are misleading. For the P/E ratios to be meaningful, one should compare comparable stocks within the same industry. For examples, go straight to Figs II.B.1.a and II.B.1.b. PEG (Price-Earnings-Growth) Ratio The PEG ratio is a valuation metric that supplement the afore-mentioned shortcomings of P/E ratio. It compares the P/E against the earnings grwoth rate, which would justify the P/Es within the vicinity of the growth rate. , where g = earnings growth rate. It is generally accepted that a PEG = 1 is an acceptable arbitrary bottom line. A lower ratio is better, because it means that the stock is cheaper for its growth, and a higher ratio is worse for the stock is expensive for its growth. It is not always clear whether the earnings used is the past year's EPS or the expected future EPS. It is always the expected future growth rate that is used. PEG is more intuitively appealing, because it can offer a suggestion of whether a firm's high PE ratio reflects an excessively high stock price, or is a reflection of promising growth prospects for the firm. For this reason, it is a better measure than P/E ratio. However, the PEG ratio is less appropriate for measuring firms without high growth. Large, well-established firms, for instance, may offer dependable dividend income, but little opportunity for growth, but PEG does not offer much of such qualitative information as the firm's management. M/B (Market Price/Book Value per Share) Ratio Market-to-book ratio or M/B ratio, is used to compare a stock's market value to its book value. It is calculated by dividing the current closing price of the stock by the latest quarter's book value. Book value is the shareholders' equity (assets minus libilities) divided by the total number of outstanding shares. A lower M/B ratio could mean that the stock is undervalued. However, it could also mean that something is fundamentally wrong with the firm. One caveat is that this ratio varies a fair amount by industry. Industries that require higher infrastructure capital, such as manufacturing or air lines, will usually trade at M/B much lower than the M/B of consulting firms for instance. This ratio also gives some idea of whether one is paying too much for what would be left if the firm is forced into bankruptcy immediately. For examples, go straight to Figs II.B.1.a and II.B.1.b. PROBLEM: Modify the above format accordingly and do Summary Practice Test problems 2 and 3. Chapter 22 Problems We will use problems 22-20 and 22-22. In problem 22-20, we will learn to create a bar graph (histogram) from the given data, and in problem 22-22, we will create a line graph from the given data. First, enter appropriate labels for each problem in column A. Column widths can be automatically adjusted by double-clicking on the border between column heads. Then, enter the data as shown below. To do this, we need Excel special feature called “Data Analysis” to be turned on. This feature is turned off in most low-level application, so if you don’t see this function in the “Tools” menu as shown below, take the following steps. First, select “Add-Ins”, and when the dialogue box opens up, select “Analysis ToolPak” as shown below. You may also select “Solver Add-in” for future applications if it is not already selected. Once it is done, now you may notice that the data were entered at random. It will be nice if we sort this data in an ordered array – either descending or ascending. We can achieve this by selecting “Sort” in the “Data” menu in the toolbar. Once you click on “Sort” in the menu box, a new dialogue box will open up. The sorting criteria is usually by the data labels, and since the “Customer $” (the amount each customer spent) is the only data label for now, and since we want to sort this data in descending order, we will select “Customer $”, and check “Descending”, and hit “OK”. Also, make sure that you check the “Header row” since we have the data label in the first row. Now, we return to the “Data Analysis” in the “Tools” menu, and select “Histogram”. Once the dialogue box opens up, we tell Excel where the data range for input is. We do it by the same manner as we have already done. Highlighting the data range and clicking it in by the spreadsheet icon in the corner of the data window. Next, we need to designate the “Bin Range”. Bin is nothing but the intervals of data that will serve as bracket for each group of data. In our example, we already have a given intervals. We can enter only the upper bounds or the lower bounds only, because each upper bound will also serve as the lower bound for the next interval, and vice versa. Then, we will enter the “Bin Range” in the same manner as before. Make sure you check the “Labels” box, because the labels are included in the data range. Next, we will designate the “Output Range” to place the result in. We only need to select a single cell although the result will be a table. Excel will automatically take care of the rest. Now, hit “OK”, and voilà the Frequency Distribution table! While it is still highlighted, we will put a thick borderline around it by clicking the borderline icon in the toolbar. And while it is still highlighted, we will sort the table by interval in descending order. Now, we are ready to generate a graph. First, locate the “Chart Wizard” icon from the toolbar as in the figure above and click on it while the table is still highlighted. (If the icon is not readily available, you can always bring it up by clicking on “Insert”, “Picture”, and “Chart” from the standard toolbar, or by clicking on “Tools”, “Customize”, “Command” and by locating the icon from the “Insert” menu.) A dialogue box will open up as in the figure below. Since we want a bar graph, select the “Column” type in the left-side panel, and the available sub-types will appear in the right-side panel. You may pick any type you deem the best, but for our example, the ones in the middle row would do well since these types give 3-D effects. Pick one and then click on “Next”. You will see a graph as in the figure below. Keep in mind that this graph is based on the data in the highlighted table, so it would naturally contain two sets of data in the range – “Interval” and “Frequency”. These data series are in columns (hence, you will see the “columns” checked in the dialogue box) of which the “Interval” is not the data we want to plot as vertical bar. It is actually the data to be plotted along the horizontal axis. Therefore, we will click on the “Series” tab to modify the data series to be graphed. In the left-side panel you will see the two data series. The data series in columns are already highlighted. Just select the data string you want to remove, and hit “Remove” button. By the same token, you can also add new data series that you may also want to be on the graph. Now that we have only the data that we want to show as vertical bars, we will put the Interval data on the horizontal axis. Go to the “Category (X) axis labels:” at the bottom of the dialogue box and click on the icon in the right corner of the data window as indicated in the figure below. Once the data window opens, highlight only the range of intervals and click on the icon to enter it. Make sure that you don’t include the label “Interval” in the data range. If the data range is correctly entered, it will appear as in the following figure. Then, click on “Next”. Now, it’s time to put the final details such as “chart title”, “axis label”… etc. You may change the chart title to “Customer Spending Frequency” or whatever you think best describes the chart. You may also use whatever appropriate label for the horizontal axis. For our example, “Customer Spending Level” would be fine. And click “Next”. Finally, we will decide where to place the chart. Normally, you would want to see the chart next to the data it represents. So, we will select the “As object in:” option to place it within the same worksheet as the data table. And click on “Finish”. Once the chart is in place, you may move it around, modify and/or resize it for the desired visual effect. By the same token, we can create the line graph with Problem 22-22, which we will skip in this exercise to avoid any redundancy. Now, let’s turn our attention to the statistical issue in managing data. You may recall from Chapter 21 how we used Excel to find average. An average is a nice approximation, representative of a series of data. By its mathematical construct, however, an average of a long data series is an innocuous neutral number that doesn’t capture the variation in data. To extend our example in Chapter 21, it is also of crucial importance for an investor to grasp the band of fluctuation in returns. However, it doesn’t necessarily help the investor or a researcher to measure this band by the upper and lower bounds, because they don’t happen all the time. What a rational investor or research would need is only some approximation of average band of swings in the data (returns) – i.e. how much the data varies on average around the mean (average return). So, we need some metrics to capture this average range of variation in the data. There are largely two measures for doing this – variance and standard deviation, which are actually the same metrics in two outfits. Now, let’s review how we found average in Chapter 21. The following figures are the reproduction of what we have already seen in Chapter 21. We need another data series called the “difference terms”, which we will label “”, which literally means “return at time “t” – mean return”, and thus the difference between individual returns and the average. Once you have found this term for the first row as in the figure above, the rest is just mechanical. All you have to do is just copy and paste it over the entire range below the first cell. Make sure that you have locked the cell containing the mean return (d80 as d$80 or $d$80 in the above example), so it can be copied without the hassle of adjusting for every row. Then, we will need another term called “difference squared” labeled “”, which is simply the square of the difference term. This term is there purely for technical reason, because if you sum all the difference terms, it will simply sum to zero. It is designed to be so by the very nature of the average and the difference term – i.e. the positive and negative difference terms will exactly offset each other. Then, the average of these difference terms will always be zero. So, it becomes inoperable to work with the difference terms. However, since squared terms of all real numbers are always positive, if we work with the squared terms, we won’t have to worry about this problem. So, we will simply raise the difference term in e2 to the 2nd power as in the figure above, and copy and paste it all the way down. Once you have all the squared difference terms, you will need to sum them up in order to find an average. This summed result is call “sum of squares (SSQ)”. Once we have SSQ, then we need to divide it by the “number of observations – 1”. (This is due to an important statistical factor called degree of freedom.) The result is called the (sample) “variance”, which is expressed mathematically as , meaning that the variance of returns (r) is the approximate average of all the SSQ’s, where number of observation runs from 1 to n. Statistically, we need to distinguish between sample variance and population variance, where population variance is the average of all the SSQ’s, where number of observation runs from 1 to n. Mathematically, it is expressed as , of which the interpretation is pretty much the same as the sample variance above. The only difference, however, is that the latter uses n, not n–1, in the denominator. We won’t dwell on the theoretical background for this difference, as a course in statistics will well expound it, and thus, focus mainly on the practical implication for large samples. Theoretically, as the n reaches infinity, the difference between n and n–1 will be insignificant. Practically, as the sample size “n” increases, the difference between the two also becomes meaningless as long as we have a decent-size sample with normal distribution. We can also find the variance using Excel’s built-in command. The procedure is the same as in finding average. Locate the fx icon in the toolbar or the formula window (The location of the fx icon varies by Excel version.). When the “Paste Function” dialogue box opens up, look for “VAR”. If you don’t see it from the list of “Most Recently Used” functions, then go to “All functions”, and select “Statistical” in the left-side panel. Then locate “VAR” and click on it. A data window will open up. Click on the spreadsheet icon in the right corner, highlight and enter the data series you want to find the variance of. In our example, we want to find the variance of the monthly returns data series. So, we enter the data range f2 through f79 as shown in the figure above. Once done, hit “O.K.” and voilà the variance! (If you used the same data, you would get 1.2% either by dividing SSQ by 77 (n–1) or by using Excel’s VAR command.) Now that the variance is found, we have found the approximated average variation of the returns around the mean, or have we not? Yes, and not quite yet. One thing we need to remember is that the variance is based on squared difference terms, remember? – i.e. we had to square all difference terms to avoid summing up to zero. So, the variance isn’t quite the life-size picture of the average variation, but rather a blown-up picture of it. Therefore, we need to revert it back to its original size. And since the original self of the squared terms is the square root of them, all we need to do is to simply take the square root of the variance to revert it back to its original self. This is called the “standard deviation” (STDEV). Mathematically it is defined as for sample, or for population. Compare these formulas with those of the variance. The relationship between the two is quite self-explanatory. Especially, the relationship between symbol for population variance (?2) and population standard deviation (?) is quite illustrative. In order to find standard deviation, we can simply use Excel command “=SQRT(cell # of the variance)” next to the variance, and label it “Stdev”, or just look for the STDEV command from Excel’s fx (“Paste Function”) feature, and highlight and enter the data series to find the standard deviation of. PROBLEM: Apply the above procedures to do Challenge Problems 22-23. Appendix A: FORMULAS FOR PROBLEMS CHAPTER 4 CELL B14 =B3+B5-B7-B9-B11 CELL E14 =E3+E5-E9-E10-E11 CHAPTER 6 CELL B7 =B3-B5 CELL C5 =B5/B3 CELL C7 =B7/B3 CHAPTER 7 CELL A5 =1.00-A3 CELL B5 =1.00-B3 CELL D5 =1.00-D3 CELL E5 =1.00-E3 CELL B7 =1.000-A5*B5 CELL E7 =1.000-D5*E5 CHAPTER 8 CELL B3 =A3*.80 CELL C3 =B3*.90 CELL D3 =C3*1.12 CELL E3 =D3*.95 CELL F3 =(A3-E3)/A3 CHAPTER 9 CELL C7 =C3*C5 CELL C13 =C9*C11 CELL C15 =C7+C13 CHAPTER 10 CELL E2 =D2-C2 CELL F2 =A2*B2*E2/360 CELL G2 =A2+F2 PROBLEM: CHANGE CELL F2 for 365 days CELL F2 =A2*B2*E2/365 CHAPTER 11 CELL B4 =B1*B2*(B3/360) CELL B5 =B1-B4 CELL B6 +B1 CELL B7 =B4/((B5*(B3/360)) CHAPTER 12 PROBLEM 12-7: CELL E2 =B2*2 CELL F2 =C2/2 CELL H2 =A2*G2 PROBLEM 12-8: CELL E2 =B2*12 CELL F2 =C2/12 PROBLEM 12-9: CELL E2 =B2*4 CELL F2 =C2/4 PROBLEM 12-10: CELL E2 =B2*1 CELL F2 =C2/1 CHAPTER 13 CELL C2 =A5/4 CELL C4 =A7*4 CELL E2 =A3*D2 CHAPTER 14 CELL D1 =B1-B4 CELL D2 =(B3*B2)-D1 CHAPTER 15 CELL C2 =A2-B2 CELL G2 =C2/1000*F2 CHAPTER 16 CELL B7 =SUM(B3:B6) CELL B11 =B9+B10 CELL B12 =B7+B11 CELL B17 =B15+B16 CELL B20 =B17+B19 CELL B23 =B20+B22 CELL C7 =SUM(C3:C6) CELL C11 =C9+C10 CELL C12 =C7+C11 CELL C17 =C15+C16 CELL C20 =C17+C19 CELL C23 =C20+C22 CELL D3 =B3-C3 CELL D4 =B4-C4 CELL D5 =B5-C5 CELL D6 =B6-C6 CELL D7 =B7-C7 CELL D9 =B9-C9 CELL D10 =B10-C10 CELL D11 =B11-C11 CELL D12 =B12-C12 CELL D15 =B15-C15 CELL D16 =B16-C16 CELL D17 =B17-C17 CELL D19 =B19-C19 CELL D20 =B20-C20 CELL D22 =B22-C22 CELL D23 =B23-C23 CELL E3 =D3/C3 CELL E4 =D4/C4 CELL E5 =D5/C5 CELL E6 =D6/C6 CELL E7 =D7/C7 CELL E9 =D9/C9 CELL E10 =D10/C10 CELL E11 =D11/C11 CELL E12 =D12/C12 CELL E15 =D15/C15 CELL E16 =D16/C16 CELL E17 =D17/C17 CELL E19 =D19/C19 CELL E20 =D20/C20 CELL E22 =D22/C22 CELL E23 =D23/C23 CHAPTER 17 CELL B6 =(B1-B3)/B2*B4 CELL B7 =B1-B6 CELL B8 =B7-B5 CHAPTER 18 CELL B6 =B4*B5 CELL B11 =B9*B10 CELL B16 =B14*B15 CELL B18 =B6+B11+B16 CELL B20 =(B4*B5)+(B19-B46)*B10 CELL B21 =B18-B20 CHAPTER 19 CELL C6 =C4/C5 CELL C10 =C8*C9 CELL C12 =C10*C11 CHAPTER 20 PROBLEM 20-8: CELL D8 =C9*D6 CELL D9 =C9*D7 CELL D10 =SUM(D8:D9) PROBLEM 20-9: CELL C15 =C13*D14 CELL C16 =C13-C15 PROBLEM 20-11: CELL C20 =C19*D20 CELL C23 =C22*D23 CELL D23 =C21/C20 CHAPTER 21 PROBLEM 21-19 CELL B8 =B6/B5 CELL B9 =B7/B6 CHAPTER 22 APPENDIX B: Examples and Problems Using Excel Templates for Problems and Examples The Excel spreadsheets included in this package are designed to work with Practical Business Math Procedures (8e) by Jeffrey Slater. There is one exercise per chapter which demonstrates a particular lesson within that chapter. In addition, there are two problems for each chapter taken from the problems section at the end of the chapter. Students are encouraged to make a copy of the entire disk and use it in the lab or at home. A knowledge of Excel would be helpful in doing the problems but it is not essential. Students place data in the yellow cells and the formulas will automatically calculate. Do not be concerned with the how the spreadsheet looks on the screen. Students who are familiar with Excel will note that each of the Chapter Exercises contains the letters “EX” followed by the chapter it is found in (i.e. EX1, Ex2, etc.). The same is true of the Chapter Problems (i.e. Chpt.01, Chapt.02, etc.) There also is the extension .XLS. Accessing these files is through the OPEN command and students can open as many chapters as they wish. It is not necessary to close each chapter file in order to open the next chapter file. General Instructions Each Excel template is designed the same way. The problems are numbered to match the problem numbers at the end of each chapter. The exercises are numbered to match the chapter the exercise can be found. Number and title identify each problem on the screen. Please follow these steps: Step 1: You may use the files from the disk/CD or transfer them to the your hard drive. Most college labs will not allow students to transfer the files to the lab hard drive. If you are able to transfer the files to your own disk/CD, follow the instructions for copying the files. Step 2: If Excel is part of the Microsoft Office, double click on that work group to open it. Step 3: Double click on the Excel icon. Your version of Excel may look different than the screens printed in the workbook. All the problems and exercises work with any version of Excel. With the Excel program loaded, you are now ready to start your work. Each time you will use the FILE pull down menu, click on that menu once, and then you will use the OPEN command, again with one click. Now you may choose the chapter or the example you wish. Each chapter is headed by the letters CHPT. and followed by the chapter number. Each exercise is headed by the letters EX and followed by the chapter number. You may disregard the file extension .XLS. If you wish chapter one you would place the mouse arrow on CHPT.01.XLS and click twice. If you wish the example you would place the mouse arrow on EX1.XLS and click twice. When you are ready to move on to another chapter or exercise, you again select the FILE pull down menu, click once, and then select OPEN and click once. You may open as many chapters and/ or examples as you wish. When done you can close any chapter and/or example by selecting FILE pull down menu, click once, and then select CLOSE and click once. You will also note that you may do the chapters and/or the exercises in any order you wish. If you save your work, use the SAVE AS feature and use a different name for your work files. Later, you can open those files. There is a conversion table file, named TABLES.XLS for doing some of the math conversion work for you. This table will find compound interest, present value, amount of annuity, present value of annuity, sinking fund, and amortization. You enter the interest rate and the time. The table is rounded off after four (4) decimal places. There is no limit on the interest rate or the number of years. Using A Floppy Disk or CD Drive If you use a floppy disk or CD drive it will be necessary for you to change the disk drive when you use the FILE - OPEN method of retrieving your files. After placing the floppy disk in your disk drive, or CD in CD drive, select FILE pull down menu and click once. Then select the OPEN command and click once. Now you will have to change the disk drive by using the DISK DRIVE selection box arrow beside the “MY DOCUMENTS” title. You would place the mouse arrow on the arrow and click once. A series of choices, starting with A: for the A drive will appear. Select the proper drive letter and click. That will switch the disk drive or CD drive. Now return to the FILENAME listings, select the chapter, click twice and the file will load. Using Excel Without The Mouse If you do not have a mouse, or if the mouse is not working, you can use Excel through the keyboard. To select the FILE pull down menu, press the ALT key on either side of the space bar. The FILE pull down menu name will be highlighted. Press the ENTER key and it will open. Using the DOWN ARROW key you can highlight the OPEN command. Again press ENTER. Now you can type, from the keyboard, the chapter you wish and press ENTER and the file will load. Check your Excel manual for additional keys and how they work. Instructions On Screen Each exercise contains information and yellow cells for the information you will find in the lesson in the book. Each chapter file has two problems from the book. Only one is on screen. In order to move to the other problem follow the instructions at the bottom of the screen. You need only click on the sheet name at the bottom of the spreadsheet to go from one sheet to the other. You may change sheets without loosing data. The spreadsheets are locked so you cannot accidentally put information into the wrong cell. Disk Directory for Examples FILENAME CHAPTER LESSON Ex1 Converting to a Whole Number Ex2 Working with Fractions Ex3 Rounding to Tenths, Hundreds, Thousands Ex4 Format of a Check Ex5 Solving Equations Ex6 Converting Decimals and Percents Ex7 Trade Discount, Net Price, Complement of Trade Discount Ex8 Calculating Dollar and Percent Markup Ex9 Calculating Employee’s Pay, Including Overtime Ex10 Calculating Simple Interest and Maturity Rate Ex11 Discounting Interest-Bearing Note Before Maturity Ex12 Simple vs. Compound Interest Ex13 Calculating Present Value Ex14 Cost of Installment Buying Ex15 Calculating Interest Over the Life of a Mortgage Ex16 Calculating Net Sales, Cost of Merchandise Sold, Gross Profit, Net Income Ex17 Straight Line Method of Depreciation Ex18 Retail and Gross Profit Method for Inventory Ex19 Calculating Sales and Excise Tax Ex20 Calculating Fire, Life, and Auto Insurance Ex21 Calculating Return of Investment Ex22 Calculating Mean, Median, and Mode Disk Directory For Problems FILENAME CHAPTER TITLE IN BOOK CHPT.01 Whole Numbers CHPT.02 Fractions CHPT.03 Decimals CHPT.04 Banking CHPT.05 Solving For The Unknown CHPT.06 Percents and Their Applications CHPT.07 Discounts: Trade and Cash CHPT.08 Markups and Markdowns; Insight into Perishables CHPT.09 Payroll CHPT.10 Simple Interest CHPT.11 Promissory Notes, Simple Discount Notes, and the Discount Process CHPT.12 Compound Interest and Present Value CHPT.13 Annuities and Sinking Funds CHPT.14 Installment Buying, Rule of 78, and Revolving Charge Credit Cards CHPT.15 The Cost of Home Ownership CHPT.16 How to Read, Analyze, and Interpret Financial Reports CHPT.17 Depreciation CHPT.18 Inventory and Overhead CHPT.19 Sales, Excise, and Property Tax CHPT.20 Life, Fire, and Auto Insurance CHPT.21 Stocks, Bonds, and Mutual Funds CHPT.22 Business Statistics TABLE Table to Calculate Compounded Interest, Present Value, Amount of Annuity, Present Value of Annuity, Sinking Fund, Amortization