CIS 1220 Exam level III. I need this done ASAP. Let me know how much time you need for thisCIS 1220 Exam level IIIPost testYou may use the textbook, previous cases and Excel Help during this exam.ÿ You may not use a tutor or a classmate to complete this exam. It is an individual assessment. The second section of this exam is designed to take two hours. The first part is a posttest to the pretest you took at the beginning of the semester. The second section only include topics covered in tutorials 9 and 10.The outcomes for this class and this assessment are as follows:1.ÿÿÿÿÿÿÿDevelop thinking and organizational skills in planning, designing and creating spreadsheets that fulfill a business related function or need.2.ÿÿÿÿÿÿÿAnalyze data presented in a business case requiring information management for decision-making and effective use the tools of Excel to create spreadsheets that address the business problem.3.ÿÿÿÿÿÿÿChoose appropriate presentation styles for visual representation of data.Item 1 Financial Analysisÿ(This is the same problem as was the pretest.ÿ You may wish to start with what you created for the pretest or begin from scratch. Your pretest file can be downloaded from the Pretest folder in D2L.)Your company is looking to provide a company car for its president and needs to know which option, buy or lease, would be the best financial decision.ÿPart I.ÿDesign and create a workbook in Excel to compare theÿpurchaseÿversusÿleaseÿof an automobile.ÿ The car the company wishes to purchase is $18,400.ÿ Monthly net income (after taxes) is $3,024.ÿ You need to decide whether to lease the car over three years or buy the car with the aid of a bank loan for $18,400.ÿ Design and create your solution using Excel.ÿ Format your workbook attractively and appropriately based on professional design principles learned in this course.ÿ You must use proper functions and formulas for all calculations.ÿ Name the worksheetÿVehicle Purchase Analysis. Be sure your calculations support your conclusions. Save your file asÿExam III Item 1.The following information is provided:úÿÿÿÿÿÿÿÿÿIf the company was to lease the vehicle with zero down, the lease payment would be $355 per month.ÿ At the end of the three years, there is an option to buy the vehicle for $12,000.ÿ What would be the total cost toÿownÿthe vehicle at the end of three years?úÿÿÿÿÿÿÿÿÿIf the company was to purchase the vehicle with the aid of a bank loan with zero down for three years (interest rate is 7%), what would theÿmonthly paymentÿbe?ÿ What would be the total cost to own the vehicle at the end of three years?úÿÿÿÿÿÿÿÿÿHow much would the company pay, over and above the price of the vehicle, forÿeachÿoption?úÿÿÿÿÿÿÿÿÿWhat do you recommend as the bestÿfinancialÿalternative:ÿ purchase or lease?ÿ Which option would you recommend the company choose and why?ÿ What other factors should be considered in the decision OTHER THAN cost?Create an attractive chart to illustrate your financial findings.ÿ Be sure to use appropriate labels. Which chart type did you choose and why?Perform all analysis using Excel.ÿ Answer the questions within your Excel workbook.Part 2.ÿAmortization Schedule. Create a new worksheet within the Exam III Item 1 file and name itÿAmortization Schedule. Assume the vehicle was purchased and the payback period was 3 years. Design and create an amortization schedule with a column indicating the # of the payment (there will be a total of 36 payments), the remaining principal, the amount of the payment applied to interest, the amount of the payment applied to principal, and the total amount of the monthly payment (will be consistent over the payback period). Use ppmt and ipmt function for the payment and interest portion of each payment.Save your file asÿExam III Item 1.Item 2 Data Tables and ScenariosRogers Party PlannersYou are the sole proprietor of a small party planning company that specializes in small community and company events (average of 200 people per event). You have one employee who you pay $200 per party. You are also responsible for paying payroll taxes. You rent a small storage unit which you use for storing party materials and supplies. You have to pay for liability insurance. Because of the Seasonal Nature of Party planning, you may increase advertising during specific seasons. The advertising expense is based on an average throughout the year. The number of parties per month is also an average throughout the year.You are concerned that you might have cash flow issues during slow months and want to analyze how the different seasons may affect your income. You also want to see how increasing you?re pricing during busy seasons may affect your income. You are thinking about hiring a second employee and perhaps taking on larger parties. You decide to explore all these different avenues by using the tools provided by Excel. You are also considering purchasing or building a facility that can be used for your events and parties.Part 1.ÿUse the data file Rogers.xls. (Attached) Add a documentation sheet with author, date and purpose of workbook.ÿCreate an Income Statement with the following data:ÿÿEnter formulas to calculate amount in cells with asterisks.Rogers Party Planning Monthly Income StatementSales Informationÿÿÿÿÿÿÿÿÿ Revenue per head for partyÿ$15.00ÿÿÿÿÿÿÿ Average size of partyÿ200Revenue per partyÿ* $3,000.00ÿÿÿÿÿÿÿÿÿÿ Number of Parties per Monthÿ8ÿÿÿÿÿÿÿ Average Revenue per Partyÿ* 3,000.00Total Monthly Revenueÿ* $24,000.00ÿÿÿVariable Expensesÿÿÿÿÿÿÿÿÿ Average Material Cost per Head7.17ÿÿÿÿÿÿÿÿ Total Material Cost per Monthÿ* 11,472.00ÿÿÿÿÿÿÿ Payroll per Party430.00ÿÿÿÿÿÿÿÿ Total Payroll per Monthÿ* 3,440.00Total Variable Expensesÿ*$14,912.00ÿÿÿFixed Expensesÿÿÿÿÿÿÿÿÿ Insuranceÿ90.00ÿÿÿÿÿÿÿ Rentÿ800.00ÿÿÿÿÿÿÿ Advertisingÿ750.00Total Fixed Expensesÿ* $1,640.00ÿÿÿSummaryÿÿÿÿÿÿÿÿÿ Total Monthly Revenueÿÿ*$24,000.00ÿÿÿÿÿÿÿ Total Monthly Expensesÿÿ*$16,552.00Gross Monthly Incomeÿ* $ÿ 7,448.00Taxes (28%)*2,085.44Net Monthly Income*$ÿ 5,362.56a)ÿÿÿÿÿIn the same worksheet as your income statement, create a single variable data table to analyze how the net income per month would change based on the number of parties per month. Use the range of parties from 1 per month to 16 per month.ÿYou can refer to the PDF and Word doc to check your table results. These are posted in D2L under the content area and named monthly income data table.b)ÿÿÿÿÿCreate a CVP Chart using the results of your data table. Save this in the same worksheet at your data table and income statement. Format according to the rubric on the last page of this exam.c)ÿÿÿÿÿÿUse Goal Seek to determine the break-even point for Net Income (Net Income will be equal to zero) with the current values for revenue and expenses by changing the monthly number of parties. Indicate the solution in the documentation worksheet labeled: Break-Even Point Sales by Party Size. Please note the breakeven point in the documentation worksheet, and return the income statement back to the original values. Note that your data table should return to the original values as well.d.) Create the following four scenarios based on the Create a Scenario Summary with Total Monthly Revenue, Total Monthly Expenses and Net Monthly Income as the results cells. Please also name the Total Monthly Revenue, Total Monthly Expenses and Net Monthly Income cells accordingly. Name the cells used as changing cells and results cell in the scenario summary so that their names (and not the cell reference) will appear in the scenario summary. Use the names, Size of Party, Number of Employees, Number of Parties, and Price per Head. Save the summary worksheet as Alternative Scenarios.Set up four scenarios as follows: (use these names for each of the scenarios)ÿÿÿÿÿÿÿÿÿ1)ÿ Average Operations (Status Quo)2)ÿ Reduced Number of Parties, Normal Price3)ÿ Larger Parties, Additional Employee4)ÿ More Parties, Additional Employee, Increased Pricingÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 1ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 2ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 3ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 4Size of Partyÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 200ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 200ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 350ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 400Number of Employees(Payroll Per Party)ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 430ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 430ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 860ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 860Number of Partiesÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 8ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 5ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 8ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 15Price per Headÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 15ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 15ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 15ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 20If you get the following message click OK:Which Scenario do you recommend and why?ÿInclude your recommendations in the scenario summary worksheet.Part 2. Loan Analysis ScenarioAs another scenario you are considering building a facility in which you could hold parties and receptions. After researching your options, you realized that you would need to take out a loan for $500,000, which you would like to repay over a ten year period. This includes kitchen equipment, furniture, and additional licensures. The kitchen equipment and furniture will cost you 25% of your total investment. The current interest rate is the prime rate plus 3%. The prime rate is currently 3.25%, this means your annual interest rate would be 6.25%. You will use Straight Line Depreciation over a 6 year period to determine the cost of your depreciation. The estimated salvage value of your equipment is $75,000.Create a Loan Analysis worksheet, Depreciation Worksheet, and Four New Income Statement worksheets (one for each loan scenario), using the following information:1. What would your monthly payments be under the above scenario?2. What is the future value of your loan after five years assuming you pay $2,500 per month?3. How many total payments would it take to pay off this loan in years if you pay $3,000 per month?4. How much could you borrow if you wanted to pay $3,000 per month over a 10 year period?a.) AnalysisDo any of these scenarios seem feasible given your estimated monthly net income? Determine how depreciation affects your net income and cash flow? Remember depreciation is an expense, but it is a non-cash expense that you can add back in to increase your cash flow. Hint: see tutorial 9.Another hint regarding depreciation and the income statements: you want to create a new income statement under each loan scenario and make sure to include depreciation The amount that you will use for depreciation is going to be 25% of $500,000 with a salvage value of $75,000.What are some other considerations to take into account if you wish to own your facility? How might that affect your analysis? Please save your responses to this section a.), b.) and c.) to a worksheet named Solver Model.b.) Interpreting Solver ModelsUsing your original income statement use solver model to create anÿanswer reportÿfor the following,ÿreturn your original income statement to 8 parties per month.:1.ÿÿÿÿÿÿÿHow many parties can you have per month that minimizes your monthly material costs and returns no less than $4,000.00 per month.2.ÿÿÿÿÿÿÿIf you could reduce your material cost according to this solver model, would it make sense to reduce your material cost. What factors would you consider in your decision?Save your file asÿExam III Item 2.