Program an Excel Spreadsheet to complete a Fixed Payment Loan Amortization Schedule based on the 5 inputs (see 1a below). This spreadsheet should work for any scenario chosen using the 5 inputs below that does not exceed 360 periods. You may start with any 5 inputs as these will be changed multiple times during grading. All cells with number format should have no decimal places. All cells with accounting format should have 2 decimal places. All cells with percentage format should have 3 decimal places. Formulas must use appropriate cell addressing and be efficiently copied to complete the schedule. Every number within the spreadsheet must be positive. If you are given a template, you must use the given template and follow the directions within the template. The required part of this assignment is All of Objective I: Building a basic amortization schedule. The spreadsheet should have the following characteristics in a logical order: Objective I: Building a basic amortization schedule

1. A data table must appear at the top left that includes: a. Inputs for: Purchase Price accounting format Down Payment accounting format Number of Years number format Compounding periods per year number format Annual Interest Rate percentage format b. Formulas for: Principle accounting format Payment accounting format

2. A summarization table must appear at the top right that includes: a. Formulas for: Total Amount Paid accounting format Total Interest Paid accounting format

3. An amortization table able to accommodate any scenario must appear at the bottom containing 6 columns and 360 rows of numbers and formulas with the following titles: a. Period Number number format b. Beginning Balance accounting format, dollar sign in first row only c. Payment accounting format, dollar sign in first row only d. Interest accounting format, dollar sign in first row only e. Principle Repaid accounting format, dollar sign in first row only f. Ending Balance accounting format, dollar sign in first row only

4. Using logic and the Excel capabilities, format the spreadsheet as follows. When a number of periods smaller than 360 are required, all rows below the last significant row must be blank (i.e. no rows visible beyond the total number of periods, no negative or extremely large and unnecessary numbers).

F G H I N 0 P Source $390 Use (38) (464) (107) (12) $131 $478 (748) (108) $464 $107 $12 $704 $46 $169 $1,204 704 777 46 169 J K L M Statement of Cash Flows Operating Activities Net Income Adjustments Non Cash Depreciation Changes in Working Capital Accts. Rec. (Increase/Decrease) Inventory (Increase/Decrease) Prepaid Exp. (Increase/Decrease) Accts. Pay. (Increase/Decrease) Accruals (Increase/Decrease) Taxes Payable (Increase/Decrease) Net Cash from Operating Activities Long Term Investing Activities Cash used to acquire fixed assets Increase in other assets Net long-term investing activities Financing Activities Sale short term investments Increase in notes payable Decrease in bonds Increase common stock Payment of dividends Net cash from financing activities Summary Net change in cash Cash at Beg. of year Cash at End of year $1,226 $108 $1,334 (459) 45 195 $777 $459 $45 $195 $168 A B C D E 1 Balance Sheet December 31, (Figures in 000's) 2 1999 2000 3 Cash $186 $224 4 Accounts Receivable $815 $1,279 5 Inventories $1,544 $1,651 6 Prepaid Expenses $119 7 Total Current Assets $2,664 $3,285 8 Gross Fixed Assets $11,459 $12,685 9 Less Acc. Depreciation $3,378 $3,856 10 Net Fixed Assets $8,081 $8.829 11 Other Assets $1,292 $1,400 12 Total Assets $12,037 $13,514 13 14 Accounts Payable $396 $1,100 15 Notes Payable $223 $1,000 16 Accruals $429 $475 17 Taxes Payable $56 $225 18 Total Current Liabilities $1,104 $2,800 19 Long-term Debt $4,659 $4,200 20 Total Liabilities $5.763 $7,000 21 Common Stock $349 $394 22 Retained Earnings $5,925 $6,120 23 Total Liab./ Net Worth $12,037 $13,514 24 25 Total 26 27 Income Statement (Figures in 000's) 28 2000 29 Sales $3,700 30 Cost of Goods Sold 48% $1,776 31 Gross Profit $1,924 32 Other Expenses 12% $444 33 Depreciation $478 34 Total Operating cost $922 35 EBIT $1,002 36 Interest $373 37 EBT $629 38 Tax 38% $239 39 EAT $390 40 Dividends 50% $195 41 Addition to Retained Earnings $195 42 43 44 45 46 47 48 49 50 51 52 1936 (1936) $38 $186 $224 Ratios Liquidity Ratios Current Ratio Quick Ratio = 1.17 0.58 Asset management ratios Inventory turns = 2.24 Average Collection 124.44 Fixed Asset Turns 0.42 Total Asset Turns = 0.27 Debt Ratios Debt Ratios = Times Int. Earned Equity Multiplier 2.13 2.69 45.28 Profitability Ratios Profit Margin- Earning Power = ROA= ROE = 0.11 0.11 0.04 0.06 C D E 1 Amortization Schedule sum/prnt $187 881 26 3 4 5 Principal Number of years Payments per year Annual interest rate $163,000.00 10 12 2.89% 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 28 27 28 29 30 31 32 33 34 35 38 37 38 39 40 41 Period 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 28 28 29 30 31 32 33 Beginning Amount $163,000.00 $161.828.88 $160,650.94 $159.472.16 $158.290.55 $157,108.09 $155,918.77 $154,728.60 $153,535.56 $152,339.65 $151,140.85 $149,939.18 $148,734.60 $147,527.13 $146.316.74 $145,103.45 $143,887 23 $142,888.08 $141.445.99 $140.220.97 $138.992.99 $137,782.05 $138,528.15 $135 291 28 $134,051.43 $132.808.59 $131,562.76 $130,313.93 $129,082.09 $127 807 24 $126,549.37 $125 288.46 $124.024.52 $122,75754 $121, 487.50 $120 214.41 $118.938.25 $117,659.01 $116,378.70 $115,091 29 $113,802.79 $112,511.19 $111 216.48 $109,918.65 $108,617.69 $107 313.60 $108,008.37 $104,695.99 $103,382.46 $102.085.76 $100,745.89 $99.422 85 $98,098.61 $98,767.18 $95,434.55 $94.098.72 $92,759.88 Payment $1.565.68 $1,565 68 $1,585.68 $1,565.68 $1,565,68 $1,565.68 $1,565.68 $1,565.68 $1,585.68 $1,565.68 $1,585.68 $1,565.68 $1,585.68 $1,585.68 $1,585.68 $1,585.68 $1,565.68 $1,585.68 $1,565.68 $1,585.68 $1,585.68 $1,585.88 $1,585.68 $1,585.68 $1,565.68 $1,585.88 $1,585.68 $1,585.68 $1,565.68 $1,565.68 $1,585.68 $1,585.68 $1,585.68 $1,585.68 $1,585.68 $1,585.68 $1,585.88 $1,585.68 $1,585.68 $1,565.68 $1,565.68 $1,585.68 $1,585.68 $1,565.68 $1,585.68 $1,565.68 $1,585.68 $1.565.65 $1,565.68 $1,565.68 $1,565.68 $1,565.68 $1,585.68 $1.565.68 $1,565.68 $1,585.68 $1.565.68 Interest $392.56 $389.73 $398.90 $384.08 $381 22 $378.38 $375.50 $372.64 $389.78 $388.88 $384.00 $381.10 $358.20 $355.29 $352.38 $349.46 $348.53 $343.59 $340.65 $337.70 $334.74 $331.78 $328.81 $325.83 $322.84 $319.85 $316.85 $313.84 $310.82 $307 80 $304.77 $301.74 $298.69 $295.64 $292.58 $289 52 $288.44 $283.38 $280.27 $277.18 $274.08 $270.98 $267.85 $264.72 $281.59 $258.45 $255.30 $252.14 $248.98 $245.81 $242.63 $239.44 $238.25 $233.05 $229,84 $226.62 $223.40 Repayment of Principal $1.173.12 $1,175.94 $1,178.78 $1,181.62 $1,184.46 $1,187-31 $1.190.17 $1.193.04 $1,195 91 $1,198.79 $1.201.68 $1.204.57 $1.207.47 $1.210.38 $1213.30 $1 216.22 $1.219.15 $1222.08 $1 225.03 $1227.98 $1.230.94 $1.233.90 $1.238.87 $1.239.85 $1.242.84 $1.245.83 $1.248.83 $1.251.84 $1.254.85 $1.257_87 $1.280.90 $1.283.94 $1.288.98 $1.270.04 $1.273.09 $1.276.16 $1.279.23 $1.282.32 $1.285.40 $1.288.50 $1.291.60 $1.294.71 $1.297.83 $1,300.98 $1,304.09 $1,307 23 $1,310.38 $1,313.53 $1.316.70 $1,319.87 $1,323.05 $1,328 23 $1,329.43 $1,332.63 $1,335.84 $1,339.08 $1,342 28 Remaning Balance $161 826.88 $160,850.94 $159.472.16 $158.290.55 $157, 108.09 $155,918.77 $154,728.80 $153,535.58 $152,339.65 $151.140.85 $149,939.18 $148.734.60 $147,527.13 $146.316.74 $145, 103.45 $143.887 23 $142.888.08 $141.445.99 $140.220.97 $138.992.99 $137.782.05 $138,528.15 $135.291 28 $134,051.43 $132.808.59 $131,562.76 $130,313.93 $129,082.09 $127 807 24 $126,549.37 $125,288.48 $124.024.52 $122.75754 $121.487.50 $120.214.41 $118.938.25 $117,659.01 $116,378.70 $115.091 29 $113,802.79 $112,511.19 $111.216.48 $109,918.65 $108,617.69 $107 313.60 $108,008.37 $104,695.99 $103,382.46 $102,085.76 $100,745.89 $99.422.85 $98,098.61 $98,767.18 $95.434.55 $94.098.72 $92,759.68 $91.417 38 35 37 38 39 40 42 44 45 49 50 51 52 53 54 55 58 57 58 59 61 50 51 52 53 54 55 56 57 65 B C D E 5 0 1 1.417 4071 111 37111 1 190 02.10 190 815 43721 0552 736700 72 202 49591 07 952 2907 47061 1055 6 3921 8. 921 B1 5 221.16 16.92 137 2015 200 2000 1972 192 1912 17l 319 10 51 17:00 103 31737 5160 S1603 # 1 150 1470 143 120 31333 129 128.2 12.95 119 115 112.50 S1000 105.50 01 90 91 2 9 9 10 101 1 1555 195 1 1565 51 565.68 15 1 15 1 S1 1 15 15 S1 1 1555 1 15 1 15 51.55 1565 15 1 15658 1565 1 1 1. 1 1555 1 1. 51 565.60 155 1 15 51 565.60 1565 15 1 1565 15 15 1 15% 15 15 15658 15% 15 15 1 15% 15 15 15658 15 15 15658 1385.51 15 13520 135 1382 11 107 1 171 137 13797 1 1 12 1 11 1. 5 1 31 101 10505 1403 5111.3 11523 11 122 l 1091 125 1435 14928 51.2.2 1 820 198 51 45317 14887 10.19 13 17 1 51.74 17 111 1 1 48855 1.42.16 1495. 1.4993 51 52% 1506.5% 15 1511 15177 1 21 13 1524 15 15215 155 153 124 15 150 15 1516 112 0071 2011 37111 015 65733 0295.53 1990 0.10 1914 43721 75.055 222 0915 5 07 ? 2017 33 161 1055. 094 3501 ! 3539275 292.15 51.052 9.61017 11637 25111 38% 40 4133# 37 28 913 33582 2005 5% 9043 75% 24.5455 23030 21 52009 2001 5184979 1976. 3151.37 13922) 520 1082 315 72.16 225 11 10 11.2 000 91 91 2 | 27.95 32.15 1052 98107 1997 71 456111 30%, 34 09 4133# 372 28 338 9143 3358 24 5 0.535.50 043 2809 24.555 3030 21 520 20016.6 ? S16976.16 15.17 31329 512391.76 5082 315 72.] 225 1 750 3 12000 1581.2 5 1 10 1 106 10 10 19 110 111 112 13 114 11 11 17 11 119 120 121| 12 123 124 15 12% 127 10 9 100 101 1 109 1 105 1 107 10 109 10 111 112 113 4.2 %? 900 7.12 3 38.11 355 516 721 2013 28.1 115 116 117 11 119 120 11 751