Excel_6E_HiresProject Description:In this Project, you will assist Linda Wong, Employer Relations Manager, in tracking the number of people who get hired by an employer at each fair. You will create and format a chart to display the number of job candidates hired at the fairs over a five-year period, create a diagram of the communities served, and create a funnel chart to visualize the number of candidates hired as a result of the Job Fairs in Year 5.Steps to Perform:StepInstructionsPoints Possible1Open the file Student_Excel_6E_Hires.xlsx downloaded with this project.02On the first worksheet, select the data in the range B4:F10, and then use the Quick Analysis tool to insert Sparklines using the Line format. Place the sparklines in the range adjacent to the Year 5 column, show the High Point and Last Point. Apply a sparkline style using the first sparkline style in the first row.(Mac users: Use the Insert Line Sparkline command on the Insert tab.)83Type Trend in the cell above the sparklines.34Using the data for the years and for each location (not the totals) and the Recommended Charts command, create a Clustered Column chart on a separate chart sheet named Hires byLocation Chart105Display the Change Chart Type dialog box. On the All Charts tab, if necessary, on the left click Column, and then on the right, at the top of the dialog box, click 3-D Clustered Column. Click OK.Mac users: Instead of the Change Chart Type dialog box, use Change Chart Type button on the ribbon.86Apply Chart Style 5 to the chart, and then add a chart title above the chart with the text JobHires by Location Set the title’s font size to 28.67Format the Chart Area with a solid fill—in the first column, click the third color. Format the Plot Area with a solid fill two shades darker—in the first column, click the fifth color.68Format the floor and the side wall with a solid color—in the next to last column, click the last color. Set the transparency for both at 60%. Add the chart element Axis Title to the vertical axis with the text Number of Attendees Hired and change the font size to 16. Click outside of the chart to deselect it.79On the Job Hires by Location worksheet, using the data for Dallas including the years , insert a Line with Markers line chart. Resize the chart to position the upper left corner within cell A13 and the lower right corner within cell G26.1010Change the chart title to Successful Job Hires in Dallas and set the title’s font size to 16. Format the Vertical (Value) Axis so that the Minimum value is 100 and the Major unit is 20611Add a Linear Trendline. Format the trendline by applying a color—in the sixth column, click the last color. Set the Width of the line to 2 pt. Click cell A1.612Display the List Chart worksheet. In cell A1, type Three Largest Communities We ServeMerge and center this title across the range A1:G1 and apply the Title cell style.513Insert a SmartArt graphic using the Vertical Box List. In the three boxes, type, in order, Dallas,Fort Worth, and Carrollton714Resize the graphic to position the upper left corner in cell A3 and the lower right corner in cell G16. Apply the Inset style and change the colors to Colorful Range – Accent Colors 4 to 5. Click cell A1 to deselect the graphic. Display the Funnel Chart worksheet. By using the data in the range A3:B5, insert a funnel chart. Apply the third Chart Style. As the Chart Title, type Year 5 Candidate Hires. Display the Format Axis pane, and then click the Fill & Line icon. Format the Line using a solid line and then in the color gallery, in the sixth column, click the last color. Set the line Width to 2 pt and then close the pane.615Display the Format Chart Area pane, and then apply a 2 pt Solid line border using same color as the axis line. Close the pane. Drag the upper left corner of the chart into cell A6, positioned slightly below the G in Granted. Click cell A1 to deselect the chart. Display the Hires by Location Chart sheet. From the Page Layout tab, use the Dialog Box Launcher to display the Page Setup dialog box. Insert a Custom Footer with the File Name in the Left section.416Display the Job Hires by Location sheet. Hold down CONTROL and select the remaining two worksheets to group the three sheets. Insert a Custom Footer with the File Name in the Left section, and then center the sheets horizontally.817Click the Hires by Location Chart sheet tab to make it the active worksheet and to ungroup the worksheets. Ensure the worksheets are in this order: Hires by Location Chart, Job Hires by Location, List Chart, and Funnel Chart. Save and close the file, and then submit for grading.0Total Points100Excel_7E_Condo_Loan_AdvertisingProject Description:In this project, you will create a worksheet for Jacques Celestine, President of Jesse Jewelers, which analyzes loan options for a condo in Toronto that the company is considering purchasing. Jacques wants to provide a lodging facility for company visitors but would like to keep the monthly loan payment below $6,250. You will also define names for ranges of cells in a worksheet containing quarterly advertising costs.Steps to Perform:StepInstructionsPoints Possible1Open the file Excel_7E_Condo_Loan_Advertising.xlsx downloaded with this project.02Display the Condo Purchase worksheet. In cell B5, insert the PMT function using the data from the range B2:B4—be sure to divide the interest rate by 12 multiply the years by 12 and display the payment as a positive number. The result, $6,598.44, is larger than the maximum payment of $6,250.103Click cell B5, and then use Goal Seek to change the amount of the loan so that the payment goal is 6250 Then, in cell A7, type Option #1 Reduce the LoanUse the Format Painter to apply the cell style from cell A1 to cell A7. Copy the range A2:B5, and then Paste the Values & Number Formatting to cell A8. In cell B2, type 615000 to restore the original loan amount. If necessary, press ESC to cancel the moving border.74Click cell B5, and then use Goal Seek to change the period of the loan (of the original data) so that the payment is 6250 Then, in cell A13, type Option #2 Increase YearsFormat the cell the same as cell A7. Copy the range A2:B5, and then Paste the Values & Number Formatting to cell A14. Display the value in cell B15 with two decimal places, and then in cell B3, type 10 to restore the original value. If necessary, press ESC to cancel the moving border. Click cell A1, and then Save your workbook.55Display the Payment Table worksheet, in the range A2:B4, enter the following row titles and data. (The Currency [0] cell style is already applied to cell B2.)Amount of Loan 615,000Period (months) 120Interest Rate (per year) 5.25%56In cell C8, type 60—the number of months in a 5-year loan. In D8, type 120—the number of months in a 10-year loan. Fill the series through cell H8. Apply Bold and Center to the range C8:H8.107Beginning in cell B9, enter varying interest rates in decrements of 0.5% beginning with 7.5%and ending with 4.0% If necessary, format all the interest rates with two decimal places, and then apply Bold and Center to the range B9:B16.108In cell B8, enter a PMT function using the information in cells B2:B4. Be sure that you convert the interest rate to a monthly rate and that the result displays as a positive number.109Create a Data table in the range B8:H16 using the information in cells B2:B4 in which the Row input cell is the Period and the Column input cell is the Interest rate. Copy the format from cell B8 to the results in the data table. Format cell D16 with the Note cell style as the payment option that is close to but less than $6,250 per month. Click cell A1.1010Display the Advertising Costs by Quarter sheet. Name the following ranges:B6:E10 Newspaper_Costs B11:E14 Digital_Costs B15:E16 Magazine_Costs B17:E17 Billboard_Costs Insert a new row 15. In cell A15, type Business Podcasts In cell B15, type 12500 In cell C15, type 11525 In cell D15, type 14455 In cell E15, type 130091211Display the Name Manager, click Digital_Costs, and then in the Refers to box, edit as necessary so that the end of the range is cell E15. Select the Billboard_Costs and then Edit the name to Outdoor_Costs Click cell A1, and then Save your workbook.512Display the Annual Advertising Costs sheet. In cell B5, type =sum(N and sum the values using the appropriate range name in the displayed list of functions. Repeat for the other named ranges. From the Formulas tab, in the Function Library group, use AutoSum to sum all the costs. To cell B9, apply the Total cell style. Click cell A1, and then click Save.713At the bottom of the workbook window, right-click any sheet tab name, and then click Select All Sheets. With the four worksheets grouped, from the Page Layout tab, display the Page Setup dialog box, and then insert a Custom Footer in the left section that includes the file name. Click the Margins tab, and then center the worksheets Horizontally on the page. Click the Payment Table worksheet, and then set the Orientation of this sheet to Landscape.714Display Backstage view, click Show All Properties. On the list of Properties, in the Tags box, type payment table, advertising costs In the Subject box, type your course name and section number. On the left, click Save.215Ensure that the worksheets are correctly named and placed in the following order in the workbook: Condo Purchase, Payment Table, Advertising Costs by Quarter, Annual Advertising Costs. Save and close the file, and then submit for grading.0Total Points100

Please answer the attached document and follow the instructions below:Maximum possible points: 50Please show steps to solve each problem;Round your numbers to the nearest two decimal places and circle your final answers (you might want to round the intermediate results to the nearest three decimal places);15 multiple choice questions @ 2 points each (30 points)4 computational problems @ 5 points each (20 points) Please write clearly.