All questions are required in excel format, hope can show the details and work process

\fQuestion 1 Inputs Full-Time tuition fee Part-Time tuition fee pre credit Number of credits to distingtuish Full -Time and Part-Time Model Student's name Number of credits Status of the student Part-Time Test Cases Student's name Number of credits Status of the student Maria 15 Fulll-Time John 6 Part-Time Yu 12 Fulll-Time Documentation Student's name B10 Number of credits C10 Satus of the student D10 =IF(C10>=$E$6,"Fulll-Time","Part-Time") The amount of tuition owed E10 =IF(C10>=$E$6, $E$4,$E$5*C10) References Criteria Number of credits Student status = 12 Full-time Additional features Add in reference table for users to refer to. Data validation for number of credits to ensure that only whole numbers between 1 are allowed. $4,320.00 $360.00 12 The amount of tuition owed $0.00 The amount of tuition owed $4,320.00 $2,160.00 $4,320.00 Fulll-Time","Part-Time") $E$4,$E$5*C10) Tuition fee $360(per credit) $4,320.00 1 are allowed. Question 1 At Hill University students who register for at least 12 credits are considered Full-Time students and pay $4,320 tuition. Students who register for less that 12 credits are considered Part-Time students and pays $360 per credit. Create a worksheet that allows you to enter a student's name and the number of credits for which he or she is registering. The worksheet should then display 1) The status of the student (Full-Time or Part-Time) and 2) The amount of tuition owed. Try your worksheet on the following three students: Maria 15 credits, John 6 credits, Yu 12 credits. Correctness ( Documentatio Formatting (2 Bonus (2) Total (20) 12 4 2 1 19 Question 2 References Category Tax rate Salary Range 1 0 to 1,000 Single 0% 2 1,000 to 3,000 10% 3 3,000 to 10,000 20% 4 10,000 to above 30% Model Marriage Status Income Category Tax rate Base tax payable Married 8000 3 15% $160.00 Documentation Marriage Status B15 Income C15 Category D15 =LOOKUP(C15,C7:C10,B7:B10) Tax Rate E15 =INDEX($F$7:$G$10,D15,MATCH(B15,F6:G6,0)) Minimum tax payable F15 =INDEX(I7:J10,D15,MATCH(B15,I6:J6,0)) Lower boundary G15 =VLOOKUP(D15,B7:C10,2,0) Total tax payable H15 =F15+E15*(C15-G15) Total tax payable (%) I15 =H15/C15 Additional features Add in reference table for users to refer to. Data validation for Marriage Status (B15) to ensure only "Single" and ""Married" are allowed. Tax rate Married 0% 1000 8% 2000 0 0 15% 7000 200 160 1600 1210 25% Lower boundary Total tax payable Total tax payable (%) $3,000.00 $910.00 11.38% MATCH(B15,F6:G6,0)) H(B15,I6:J6,0)) Range Question 2 Base tax payable Single Married 0 0 Correctness (12) Documentation (4) Formatting (2) Bonus (2) Total (20) In the country of Free no taxes on the first 1 royals, 20% on any in income over 10,000. royals, 8% on any inc between 3,000 to 10, royals. So, for examp pay 200 royals for the between 3,000 and 5 the tax given the mar the actual percentage royals owed represen 12 4 2 1 19 Question 2 In the country of Freedonia, the national currency is the royal. Single people pay no taxes on the first 1,000 royals, 10% on any income between 1,000 and 3,000 royals, 20% on any income between 3,000 to 10,000 royals, and 30% on any income over 10,000. Married people filing jointly pay no taxes on the first 1,000 royals, 8% on any income between 1,000 and 3,000 royals, 15% on any income between 3,000 to 10,000 royals, and 25% on any income in excess of 10,000 royals. So, for example, a single person with an income of 5,000 royals world pay 200 royals for the income between 1,000 and 3,000 and 400 for the income between 3,000 and 5,000 for a total tax bill of 600 royals. Create a worksheet the tax given the marriage status and income. The worksheet also should show the actual percentage of income owed in taxes. In the example given the 600 royals owed represent 12% of income. Question 3 Input Car Price $18,239.00 Down payment $2,000.00 Rebate of promotion 1 $1,000.00 Interest rate of promotion 1 11.90% Interest rate of promotion 2 1.90% Discount rate 8% Number of years 4 Model Monthly Payment of Option 1 Monthly Payment of Option 2 PV of Cash Outflow Option 1 ($426.84) ($351.60) $18,484.11 Documentation Monthly Payment of Option 1 B15 =PMT(F8/12,F11*12,F5-F6,0) Monthly Payment of Option 2 C15 =PMT(F9/12,F11*12,F5-F6,0) PV of Cash Flow Option 1 D15 =PV(F10/12,F11*12,B15,0)+F6-F7 PV of Cash Flow Option 2 E15 =PV(F10/12,F11*12,C15,0)+F6 Best Deal F15 =IF(D15>E15, "Option 2", "Option 1") Additional notes Although Option 1 gives $1000 rebate right now, the present value of all the cash flow of Option 2 is less than that of O It means the present value of total payment of Option 2 is less than that of Option 1. That's the reason we choose Opt Question 3 You are considering purchasing a new car. would pay $2,000 now and the rest monthly dealership is offering a sales promotion wh 1) you will received a $1,000 rebate check on the loan will be 11.9% or 2) The annual but there is no rebate. Create a worksheet to compare the two op of each of the options, assuming an 8% dis PV of Cash Outflow Option 2 Better Deal $16,402.16 Option 2 15,0)+F6-F7 n 2", "Option 1") ption 2 is less than that of Option 1. the reason we choose Option 2. Correctness (12) Documentation (4) Formatting (2) Bonus (2) Total (20) 12 4 2 0 18 ng purchasing a new car. The price would be $18,239. You 0 now and the rest monthly in a four-year loan. The automobile ring a sales promotion where either ed a $1,000 rebate check right now and the annual interest rate e 11.9% or 2) The annual interest rate on the loan will be 1.9% bate. eet to compare the two options by calculating the present value tions, assuming an 8% discount rate. Which is the better deal? Question 4 Input Excelsior Project Initial investment -$20,000,000.00 Cash flow of Year 1 $8,000,000.00 Cash flow of Year 2 $10,000,000.00 Cash flow of Year 3 $12,000,000.00 Decision Model IRR of Excelsior Project IRR of Indela Project 21.65% 19.35% Documentation Initial investment C6,D6 Cash Flow of each year C7:C9,D7:D9 IRR of Excelsior Project B15 IRR of Indela Project C15 Project to invest D15 Additional Notes The higher the Internal Rate of Return(IRR), the project is more profitable. Indela Project -$20,000,000.00 $0.00 $0.00 $34,000,000.00 Question 4 As Division Manager you have 20 million to invest in a new project. T Team says that if you invest with them you will receive returns of 8 m million after two years and 12 million after three years. The Indela Pro you invest with them you will receive nothing for the first two years an the end of the third year. Create a spreadsheet that determines which the better financial return on the investment and elaborate why. The a project\" or \"Indela project\" should appear in a cell. Project to invest Excelsior Project =IRR(C6:C9) =IRR(D6:D9) =IF(B15>C15,C5,D5) ore profitable. Correctness (12) Documentation (4) Formatting (2) Bonus (2) Total (20) 12 4 2 0 18 n to invest in a new project. The Excelsior Project you will receive returns of 8 million after a year, 10 ter three years. The Indela Project Team says that if othing for the first two years and then 34 million at adsheet that determines which project team offers ment and elaborate why. The answer \"Excelsior ar in a cell. Microsoft Excel 14.1 Sensitivity Report Worksheet: [Assignment2_He Xu_G17 .xls]Question 5 Variable Cells Cell $B$24 $C$24 Name Number of F300 Number of S250 Final Value 4.4444444444 8.8888888889 Reduced Gradient Name Purchasing Budget LHS Daily Maintenance Budget LHS Number of F300 nedded LHS Final Lagrange Value Multiplier $4,000,000.00 0 $328.77 -3.8798148093 4.4444444444 3.8333356858 0 0 Constraints Cell $C$16 $C$17 $C$18 Question 1 Inputs Full-Time tuition fee Part-Time tuition fee pre credit Number of credits to distingtuish Full -Time and Part-Time Model Student's name Number of credits Status of the student Part-Time Test Cases Student's name Number of credits Status of the student Maria 15 Fulll-Time John 6 Part-Time Yu 12 Fulll-Time Documentation Student's name B10 Number of credits C10 Satus of the student D10 =IF(C10>=$E$6,"Fulll-Time","Part-Time") The amount of tuition owed E10 =IF(C10>=$E$6, $E$4,$E$5*C10) References Criteria Number of credits Student status = 12 Full-time Additional features Add in reference table for users to refer to. Data validation for number of credits to ensure that only whole numbers between 1 are allowed. $4,320.00 $360.00 12 The amount of tuition owed $0.00 The amount of tuition owed $4,320.00 $2,160.00 $4,320.00 Fulll-Time","Part-Time") $E$4,$E$5*C10) Tuition fee $360(per credit) $4,320.00 1 are allowed. Question 1 At Hill University students who register for at least 12 credits are considered Full-Time students and pay $4,320 tuition. Students who register for less that 12 credits are considered Part-Time students and pays $360 per credit. Create a worksheet that allows you to enter a student's name and the number of credits for which he or she is registering. The worksheet should then display 1) The status of the student (Full-Time or Part-Time) and 2) The amount of tuition owed. Try your worksheet on the following three students: Maria 15 credits, John 6 credits, Yu 12 credits. Correctness ( Documentatio Formatting (2 Bonus (2) Total (20) 12 4 2 1 19 Question 2 References Category Tax rate Salary Range 1 0 to 1,000 Single 0% 2 1,000 to 3,000 10% 3 3,000 to 10,000 20% 4 10,000 to above 30% Model Marriage Status Income Category Tax rate Base tax payable Married 8000 3 15% $160.00 Documentation Marriage Status B15 Income C15 Category D15 =LOOKUP(C15,C7:C10,B7:B10) Tax Rate E15 =INDEX($F$7:$G$10,D15,MATCH(B15,F6:G6,0)) Minimum tax payable F15 =INDEX(I7:J10,D15,MATCH(B15,I6:J6,0)) Lower boundary G15 =VLOOKUP(D15,B7:C10,2,0) Total tax payable H15 =F15+E15*(C15-G15) Total tax payable (%) I15 =H15/C15 Additional features Add in reference table for users to refer to. Data validation for Marriage Status (B15) to ensure only "Single" and ""Married" are allowed. Tax rate Married 0% 1000 8% 2000 0 0 15% 7000 200 160 1600 1210 25% Lower boundary Total tax payable Total tax payable (%) $3,000.00 $910.00 11.38% MATCH(B15,F6:G6,0)) H(B15,I6:J6,0)) Range Question 2 Base tax payable Single Married 0 0 Correctness (12) Documentation (4) Formatting (2) Bonus (2) Total (20) In the country of Free no taxes on the first 1 royals, 20% on any in income over 10,000. royals, 8% on any inc between 3,000 to 10, royals. So, for examp pay 200 royals for the between 3,000 and 5 the tax given the mar the actual percentage royals owed represen 12 4 2 1 19 Question 2 In the country of Freedonia, the national currency is the royal. Single people pay no taxes on the first 1,000 royals, 10% on any income between 1,000 and 3,000 royals, 20% on any income between 3,000 to 10,000 royals, and 30% on any income over 10,000. Married people filing jointly pay no taxes on the first 1,000 royals, 8% on any income between 1,000 and 3,000 royals, 15% on any income between 3,000 to 10,000 royals, and 25% on any income in excess of 10,000 royals. So, for example, a single person with an income of 5,000 royals world pay 200 royals for the income between 1,000 and 3,000 and 400 for the income between 3,000 and 5,000 for a total tax bill of 600 royals. Create a worksheet the tax given the marriage status and income. The worksheet also should show the actual percentage of income owed in taxes. In the example given the 600 royals owed represent 12% of income. Question 3 Input Car Price $18,239.00 Down payment $2,000.00 Rebate of promotion 1 $1,000.00 Interest rate of promotion 1 11.90% Interest rate of promotion 2 1.90% Discount rate 8% Number of years 4 Model Monthly Payment of Option 1 Monthly Payment of Option 2 PV of Cash Outflow Option 1 ($426.84) ($351.60) $18,484.11 Documentation Monthly Payment of Option 1 B15 =PMT(F8/12,F11*12,F5-F6,0) Monthly Payment of Option 2 C15 =PMT(F9/12,F11*12,F5-F6,0) PV of Cash Flow Option 1 D15 =PV(F10/12,F11*12,B15,0)+F6-F7 PV of Cash Flow Option 2 E15 =PV(F10/12,F11*12,C15,0)+F6 Best Deal F15 =IF(D15>E15, "Option 2", "Option 1") Additional notes Although Option 1 gives $1000 rebate right now, the present value of all the cash flow of Option 2 is less than that of O It means the present value of total payment of Option 2 is less than that of Option 1. That's the reason we choose Opt Question 3 You are considering purchasing a new car. would pay $2,000 now and the rest monthly dealership is offering a sales promotion wh 1) you will received a $1,000 rebate check on the loan will be 11.9% or 2) The annual but there is no rebate. Create a worksheet to compare the two op of each of the options, assuming an 8% dis PV of Cash Outflow Option 2 Better Deal $16,402.16 Option 2 15,0)+F6-F7 n 2", "Option 1") ption 2 is less than that of Option 1. the reason we choose Option 2. Correctness (12) Documentation (4) Formatting (2) Bonus (2) Total (20) 12 4 2 0 18 ng purchasing a new car. The price would be $18,239. You 0 now and the rest monthly in a four-year loan. The automobile ring a sales promotion where either ed a $1,000 rebate check right now and the annual interest rate e 11.9% or 2) The annual interest rate on the loan will be 1.9% bate. eet to compare the two options by calculating the present value tions, assuming an 8% discount rate. Which is the better deal? Question 4 Input Excelsior Project Initial investment -$20,000,000.00 Cash flow of Year 1 $8,000,000.00 Cash flow of Year 2 $10,000,000.00 Cash flow of Year 3 $12,000,000.00 Decision Model IRR of Excelsior Project IRR of Indela Project 21.65% 19.35% Documentation Initial investment C6,D6 Cash Flow of each year C7:C9,D7:D9 IRR of Excelsior Project B15 IRR of Indela Project C15 Project to invest D15 Additional Notes The higher the Internal Rate of Return(IRR), the project is more profitable. Indela Project -$20,000,000.00 $0.00 $0.00 $34,000,000.00 Question 4 As Division Manager you have 20 million to invest in a new project. T Team says that if you invest with them you will receive returns of 8 m million after two years and 12 million after three years. The Indela Pro you invest with them you will receive nothing for the first two years an the end of the third year. Create a spreadsheet that determines which the better financial return on the investment and elaborate why. The a project\" or \"Indela project\" should appear in a cell. Project to invest Excelsior Project =IRR(C6:C9) =IRR(D6:D9) =IF(B15>C15,C5,D5) ore profitable. Correctness (12) Documentation (4) Formatting (2) Bonus (2) Total (20) 12 4 2 0 18 n to invest in a new project. The Excelsior Project you will receive returns of 8 million after a year, 10 ter three years. The Indela Project Team says that if othing for the first two years and then 34 million at adsheet that determines which project team offers ment and elaborate why. The answer \"Excelsior ar in a cell. Microsoft Excel 14.1 Sensitivity Report Worksheet: [Assignment2_He Xu_G17 .xls]Question 5 Variable Cells Cell $B$24 $C$24 Name Number of F300 Number of S250 Final Value 4.4444444444 8.8888888889 Reduced Gradient Name Purchasing Budget LHS Daily Maintenance Budget LHS Number of F300 nedded LHS Final Lagrange Value Multiplier $4,000,000.00 0 $328.77 -3.8798148093 4.4444444444 3.8333356858 0 0 Constraints Cell $C$16 $C$17 $C$18