Excel Project 2023

COMP7012 Computer Applications for Business Excel Project (15%) (Due on November 19, 2023 at 11:59PM in Excel Project Dropbox on FOL) This is a group project with 2 - 3 people in each group. You choose your own partners. One Excel workbook from each group is submitted to the drop box (Excel Project) on FOL. In your Excel workbook file: • enter your group member’s names (First and last names) in the Author box under document properties. • Name your file name as your group member’s last name, for example Lee_Smith_Wang.xlsx. The project is to create a workbook with multiple sheets. The Pension Inquiry and Amortization schedule files can be downloaded from FOL, others to be created by yourselves. One workbook Excel file is submitted by each group. To sign up into a group, on FOL course site, go to Communications  Groups, pick a group, make sure you and your partners enroll into the same group. You can use discussions to communicate within the group and use locker to store your group files. Task 1: Comparative income statement: 1. Start a new worksheet. 2. Name the worksheet as” Comparative income statement ”, choose your favorite theme. 3. Search online and look for a company’s income statement of two consecutive period (annually or quarterly); it must be actual data by real company. In put the date into the worksheet named Comparative income statement” Cite the source of the data on your worksheets. 4. Format the income statement: a. Format the first row of number and total with accounting style. The other numbers are formatted with Comma style. b. Single underline before each formula calculations c. Double underline after the Total amount. d. Display dollar amounts with no decimal places. Round percent values to one decimal place. Ratios (such as earnings per share) are rounded to two decimal places. e. Add header for company names f. Place the revenues, expenses... as section titles g. No indentation for the section titles. Indent section contents below the title. h. Total amount of each section should be calculated by formulae i. Highlight any negative numbers using conditional formatting.

COMP7012 Computer Applications for Business 5. Extend two extra columns beside the original data and name the two columns with a common title “Increase in xxxx” and individual titles, “Amount” and “Percent” as shown in the following sample. 6. The “Amount” column represents the changes (dollar amounts) between the consecutive two-year data. The “Percent” represents the percent changes. Use proper formulas, proper cell references and auto fill features in Excel for calculation (only values without formula will receive zero grade). Use Red font for the calculated results in the two extended columns (as shown in the following sample result). The values of two extended columns should be calculated by formulae in Excel. 7. Cite your resources on your worksheet. 8. The following sample is only for demonstration purpose. You sheet may have different contents and formatting colors. Task 2: Common-size Comparative Balance Sheet 1. Add a worksheet and name it “ Comparative Balance Sheet ”. Choose a theme. 2. Search a balance sheet with two consecutive periods (annually or quarterly) from the internet, as the income statement; cite the source of the data.

COMP7012 Computer Applications for Business

COMP7012 Computer Applications for Business Task 3: Create a car loan amortization schedule 1. Use the worksheet amortization schedule as a template, rename the sheet as “ Amortization Schedule ”. Format the sheet with a theme you choose and proper fonts, colors and fills. 2. Search the internet and newspapers to find an advertisement of a car with its price as the loan amount (PV), the annual interest rate (I/Y) to finance the car. We assume the car payment is made monthly P/Y = 12, and interest rate is compounded monthly C/Y = 12 to avoid complications. Decide how many years you plan for the car loan. Cite your source of data. Insert your car image in Cell A1, format the image as you want. 3. Input the car loan information into the loan information table. Use function PMT and other formulae to calculate the values in the loan Summary area. 4. Complete Amortization Schedule 1 Area using the financial functions learned in the course. 5. Calculate the total payments and total interest made in the amortization schedule and fill into the Loan Summary area. 6. Create a column chart using Payment Number horizontal axis, and Balance as vertical axis. Give the bar chart a title of Balance Change. See how the balance is gradually changed to zero with number of payments. 7. Create a line chart with Payment Number as horizontal axis with two lines of Principal Portion and Interest Portion to observe how the two values change along with the number of payments. Explain 8. Create another amortization schedule on the same sheet ( Amortization Schedule 2 ). In this schedule, the monthly payment is rounded to the next 100 dollars from previous calculated from PMT function. The formulas are listed in the following: a. Payment 0 row, enter the loan amount as the balance leave others blank b. Payment 1 row i. Monthly Payment : Round the previous monthly payment(PMT) to the next 100 dollars. ii. interest portion is calculated from the formula 𝑖𝑖 × 𝑏𝑏𝑏𝑏𝑏𝑏𝑏𝑏𝑏𝑏𝑏𝑏𝑏𝑏 𝑓𝑓𝑓𝑓𝑓𝑓𝑓𝑓 𝑡𝑡ℎ𝑏𝑏 𝑝𝑝𝑓𝑓𝑏𝑏𝑝𝑝𝑖𝑖𝑓𝑓𝑝𝑝𝑝𝑝 𝑓𝑓𝑓𝑓𝑟𝑟 (Watch out the cell references). iii. Principal portion = Payment – Interest portion iv. Balance = balance from previous row – Principal Portion c. Auto fill the rest of the rows until you reach the balance of zero or close to zero. d. Adjust the payment on the last row when the balance is close to zero in order to make the balance as zero. 9. Compare Amortization Schedule 2 with Amortization Schedule 1 and explain the differences.

COMP7012 Computer Applications for Business (Hint: the lookup_value in the VLOOKUP function should be the Employee ID: B4 on worksheet Pension Inquiry . Test your input by changing the employee ID.) 5. Age is a calculated field based on today's date minus the employee's birth date: ( Today’s date – Birth date )/365.25 Format the field using the Comma style. (Remember that dates are stored as serial numbers since Jan 1, 1900. So if you subtract the birthdate from today's date you still need to divide the result by 365.25 to get age. use 365.25 rather than 365 to account for leap years every 4 years.) 6. Retirement date is going to be the employee's birth date + 65 years. Remember that we are using 365.25 to equal 1 year so the formula is: birth date + 365.25 * 65. Ensure that the cell is formatted as Short Date. 7. Service is similar to calculating age. 8. Months till Retirement is (65- Age (G10 ) )* 12 9. Monthly Pension Contribution is: Gross Pay * Retirement rate + Voluntary pension contribution . The Retirement rate is listed in small table Pension_Plan_Lookup table on the Data worksheet with different plan group. 10. Calculate the Pension Value at Retirement (D16) using the FV function. All of the arguments required are now available. The result will show as a negative number so you will need to reverse the sign of the formula to have the number display as a positive. Remember that the Interest rate argument is monthly. As the Current Interest Rate is an annual rate you will need to divide it by 12. Make the result as positive number. 11. Goal Seek Section: Create a Goal Seek sheet by copying the Pension Inquiry worksheet and name the copied sheet as GoalSeek . Move the new worksheet to the right side of the Pension Inquiry worksheet. Create a question with your own number like following and answer the question using the Goal Seek Feature on Excel: How much do I need to contribute monthly to the Voluntary Pension Contribution to have a Pension Value at Retirement of $xxx,xxx? State your question and answer on the worksheet. 12. Solver Section: Create a Solver sheet by copying the Pension Inquiry worksheet and name the copied sheet as Solver. Move the new worksheet to the right side of the GoalSeek worksheet. Create a question with your own numbers like: What is the optimum interest rate (constrained to a max of xxx) and Voluntary_Pension_Contribution (constrained to a max of xxx) to reach my goal of xxx,xxx as a Pension Value at Retirement? State your question and answer on the worksheet This is the end of the project. In your Excel workbook, you should have the following sheets: • Comparative Income Statement

COMP7012 Computer Applications for Business • Common-size Comparative Balance Sheet • Amortization Schedule • PivotTable • Pension Inquiry • GoalSeek • Solver • Data (to support the Pension Inquiry) Submit your completed workbook to the Excel Project dropbox on FOL. You will need to submit one file (Excel Workbook) per group.