INTERMEDIATE ACCOUNTTNG 1 COMPUTER PROJECTs FAL.. 2023 Created by Terry Unuh Phone Numbers: wotk 918-495-7781; bome 918-496-9044; cell 918-850-8431 e-malli turnhatoriedu COMPUTER PROJKCT 13 - Time Valie of Noner and Spreadiliest Teoh - 25 poists Doe in my office or in D2L by TUESDAX, NOVEMBER 24, 2023 BY 4BY P.M. You are to prepare a sproadshect which will be a model to solve the following situations. Yoe should attempt to create the spreadsheet with as many formulas as possible so that it mary be used to solve similar problems. You are encouraged to work with others but your spreadsheet should show some individuality and should NOT be EXACTLY like the spreadsheets of those with whom you worked. (If it is determined that files have been copied, each student involved will have their scores reduced by 75%.) ASSIGNMENTS TURNED IN AFTER THE DEADLINE LISTED ON THE ASSIGNMENT WIL.L BE CONSIDERED LATE AND A 50% PENALTY WIL. BE ASSESSED FOR EVERY SCHOOL DAY THEY ARE LATE. Exceptions will only be considered if a written request is submitted prior to the due date. Case A - You estimate that you will have $42,000 of school loans by the time you graduate: Your school loans are to be paid off over a 4 year period with payments of principal and interest being made at the BEGINNING of each MONTH for the 4 years following graduation. Asaume your school loans charge 6% interest compounded MONTHLY. Prepare a schedule to show (1.) the month number (i.e. #1 - #48 ), (2.) the beginning loan balance each month, (3.) the payment each month, (4.) the amount of interest in each monthly payment, (5.) the amount of principal in each monthly payment, and (6.) the ending balance each month. Format each of the amounts with two decimals. Case B - Your company borrowed $200,000 and signed a 5 year, 9% note payable. This note requires payments of principal and interest at the END of each MONTH for 5 years. Prepare a schedule to show (1.) the month number (i.e. #1 - #60 ), (2.) the beginning loan balance each month, (3.) the payment each month, (4.) the amount of interest in each monthly payment, (5.) the amount of principal in each monthly payment, and (6.) the ending balance cach month. Format each of the amounts with NO decimals. Please include totals at the bottom of vour spreadshect for the amount of payments, the amount of interest, and the amount of principal which was paid during the 5 year term of the note. Case C-You want to retire in 40 years with $2,000,000. You plan to make annual deposits of $6,000 into a fund at the BEGINNING of each of the next 10 years. Then you will change the deposits for the next 30 years to be an amount necessary for the fund to reach the $2,000,000 goal by the end of the 40t year. The fund will earn 8% interest compounded annually throughout the entire 40 year period. Prepare a schedule to show (1.) the year (1 through 40), (2.) the beginning balance each year, (3.) the amount of the deposit each year, (4.) the amount of interest each year, and (5.) the ending balance each year. Format each of the amounts with two decimals. Case D-Using the following condensed income statements, prepare pro forma (projected) financial statements to show 2023, 2024, 2025, and 2026 using the following amounts and assumpt 1. Sales and cost of goods sold will increase by 4% each year. 2. Wages, utilities, and advertising expense will increase by 3% each year. 3. Depreciation expense is calculated on a straight line basis. No sales of fixed assets are planned for the next few years but the following purchases of fixed assets are planned in the future: a. January 1,2024$30,000 with a 5 year life and $6,000 salvage value. b. Julv 1, 2026 - $200,000 with a 20 year life and a $30,000 salvage value. 4. Bad debts are estimated as 1% of sales. 5. Other revenue is expected to DECREASE by 2% each year. 6. Interest expense of $14,000 will continue throughout the next 4 years because that is the interest on a 10 year bond which is due in 2030. But Interest Expense will increase in 2026 because you plan to borrow $200,000 to pay for the fixed asset in 3b above. Use the table you created in case B to determine how much interest expense will increase in 2026. 7. Income tax rates for corporations are expected to drop to 25% beginning in 2025 When a new US President takes office. Case E-Prepare a graph of your choice (bar graph, pie graph, line chart, etc.) to show EACH of the operating expenses for each of the 4 years in Case D above