Creаte а Fоlder оn yоur Desktop nаmed Exam. Download the following file (click the 3 dots at the end of the line and choose download) SP26_Excel_HO_1_Template.xlsx Filename, & Payment Analysis—9 Points 1. Use the FA25_Excel_HO_1_Template.xlsx file that you downloaded above. “Save As” or Rename this workbook as your last name + your first initial + _Ex1.xlsx. Below is an example of the filename you would use if you were Sam Smith: Filename Example: SmithS_Ex1.xlsx 2. You will be completing a workbook for Experience Alaska, Inc. With your newly renamed file open in Excel, make the Payment Analysis sheet active. 3. Use the PMT function to calculate the monthly payment in cell C12 for a new boat using the information given on the Payment Analysis sheet. Ensure all spelling is correct on the sheet. 4. Save your workbook now, and frequently throughout the exam. Three Year Forecast Excel Worksheet—88 Points 1. Copy the Excel Template sheet to a new sheet in the same workbook and name the new sheet Three Year Forecast. Experience Alaska, Inc. would like you to assist them in completing their forecast information. check exam1.png 2. Numbers in the figure above have been provided for you to check the outcomes of your formulas in Column B. Use only appropriate formulas and functions to complete your worksheet. Utilize the data provided in the Input Variables area of the worksheet to create correct formulas using absolute references and mixed references where appropriate. Do not 'hard code' type in values in your formulas, always refer to the cells in the input variables area. Make the following calculations: a) Use formulas to enter all Fishing / Tour Packages and Guided Hunting Package Sales, including Year 1. b) Calculate Fishing / Tour Package Sales assuming a 3.00% growth rate each year after Year 1. c) Calculate Guided Hunting Package Sales assuming a 1.75% growth rate each year after Year 1. d) Calculate Total Income for each year using the SUM function. e) Calculate all expenses using mixed references, and using the figures in the Input Variables Area. f) Determine Total Expenses for each year using the SUM function. g) Calculate Net Profit Before Taxes as Total Income minus Total Expenses. h) Use the VLOOKUP function in a formula in row 18 to specify the dollar Tax Amount for each year based upon the Net Profit Before Taxes, and using the Tax Rate figures in the Input Variable Area. i) Calculate Net Profit After Taxes for each year (Net Profit Before Taxes minus Tax Amount) in row 19. j) Calculate the three-year Total for each row in column E for rows 7 through 9 (Income), 12 through 15 (Expenses), and 17 through 19 (Profit and Taxes) using the SUM function. k) Calculate the Profit Margin for each year (by dividing Net Profit after Taxes by Total Income) in row 21. Use a Percentage format showing two decimal places. Do not calculate a Profit Margin in column E. Note: Make sure you have entered formulas and/or functions for all three years in columns B, C, and D, and row totals in column E. Formulas should refer to the Input Variables Area when appropriate and results should not be hard-coded or constant numbers. 3. If Net Profit Before Taxes is $113,000 or more in any year, the guides will receive an award of a cash bonus, otherwise, they will not. Use the Bonus Award Threshold parameter in the Input Variables Area with the IF function to show when the guides will receive a “Cash Bonus” award or will receive “No Bonus”. Use row 23 for your calculations. Use the input variables area , do not type in 113,000. 4. In cell B25, determine the average of Net Profit After Taxes for all three years using the correct Excel function. 5. Format the worksheet. At a minimum, complete the following: a) Change the font type, font size, and font color of the headings in rows 1 and 2. b) Merge and center the heading in row 1 across columns A through E. Do the same for row 2. Use a fill color and a Thick Outside border for these headings. c) Center, Bold, and Underline the column headings in cells B5 through E5. d) Apply consistent formats to all numbers including those in the Input Variables section. Format all dollar amounts using the Accounting number format with the dollar sign ($) and zero decimals. Format all percentages using the percentage format with two decimals. 6. Check the spelling of your worksheet. 7. Set your Three Year Forecast tab/worksheet print settings to the following: a) Landscape orientation b) Fit to one page c) Center both vertically and horizontally d) Create a custom Header using the appropriate buttons. Insert the File (Workbook) name in the left section, and the Sheet (Tab) name in the right section. e) Create a custom Footer with Your First and Last Name in the center section 8. Copy the Three Year Forecast sheet to a new sheet and name it Formulas. Change the option for formulas to show. AutoFit all column widths. Excel Chart—26 Points 1. Make your Three Year Forecast sheet active. Create a 3-D Clustered Column chart demonstrating the sales for both income categories for all three years. a) Place your chart on a new Chart sheet and name the tab “Sales Comparison Chart”. b) Create a title above the chart as Annual Sales Comparison by Your Name. c) Show the Legend for both income categories, at the Top of the Chart d) Include appropriate titles for both the horizontal and vertical axes. e) Format the Vertical Axis Title as a Stacked Title (Text direction: Stacked). f) Apply a Texture Fill to one of the Sales Categories. g) Ensure all spelling is correct. Finally—2 Points 1. Make sure your worksheets are in the following order: Three Year Forecast, Formulas, Payment Analysis, and Sales Comparison Chart. Delete the Excel Template worksheet. (if you have not done so, spellcheck ALL your sheets) 2. Save your workbook one last time and exit Excel. 3. In the answer box below , click on the attachment icon and attach your file to it. ** Make sure your file is saved and attached properly to this question, otherwise your instructor will not receive your exam to grade. Screenshot 2025-02-26 124438.png d) Once your exam has been submitted, delete your exam files from your computer..