Time Consideration

The time for this assignment will depend on each students level of skill in Excel. It will take more time for those that are going to be learning as they complete the assignment.

Allocate 1.5-3 hours for this assignment.

Steps to work through to complete this assignment:

• Familiarize yourself with the requirements of the assignment (including grading scheme on rubric)
• Use the outline provided to create an Excel document
• Create formulas.
• Format document as required
• Review completed work (cross reference with rubric to ensure all expectations have been met).
• Save and submit document.

Resources

• Microsoft.com website: Excel video training
• OER - Business Computers 365: Business Computers 365 - Excel
• Excel Custom Guides: Excel Custom Guides

Technology Skills HOSP 101 Excel Assignment 1 Follow the steps below to complete the Excel assignment: Workbook: Begin by creating a new workbook. In the workbook you will want to have 2 worksheets. Name your worksheets as follows: Worksheet 1: Monthly Income & Expenses Worksheet 2: Loan Repayment Plan Personalization of your work: Use the chart below to determine the business type and the monthly income (as a dollar value) that you have been assigned based on your student number. This value will be placed into the first cell in the right-hand column in Worksheet 1. BUSINESS TYPE MONTHLY INCOME Hotel (Student # ending in 8, 9) $160 000.00 Restaurant (Student # ending in 6, 7) $40 000.00 Event Planning Company (Student # ending in 4, 5) $35 000.00 Bakeshop/Cafe (Student # ending in 2, 3) $30 000.00 Travel Agency (Student # ending in 0, 1) $45 000.00 Now based on your business type, choose a creative company name that includes your first name and add it to the title of Worksheet 1.

Technology Skills HOSP 101 Excel Assignment 2 WORKSHEET 1: Monthly Income /Expenses To begin, copy & paste the following info in your Excel Workbook, Worksheet 1: Distribution of Monthly Income for [Company Name that includes your first name] Monthly Income 100.00% INPUT MONTHLY INCOME HERE BASED ON STUDENT # Expenses (listed below): Cost of Sales 33.80% (add a formula to calculate) $ Salaries & Wages 32.00% (add a formula to calculate) $ Rental & Leasing 7.10% (add a formula to calculate) $ Amor5za5on & Deprecia5on 2.40% (add a formula to calculate) $ Marke5ng & Entertainment 2.80% (add a formula to calculate) $ U5li5es 2.20% (add a formula to calculate) $ Repair & Maintenance 1.90% (add a formula to calculate) $ Professional & Business Fees 2.20% (add a formula to calculate) $ Office & Computer Related Expenses 0.70% (add a formula to calculate) $ Business Taxes, Licenses & Permits 0.80% (add a formula to calculate) $ Insurance 0.60% (add a formula to calculate) $ Subcontracts 0.60% (add a formula to calculate) $ Telephone, Internet & Other Telecommunica5ons 0.30% (add a formula to calculate) $ Travel, Mee5ng & Conven5ons 0.30% (add a formula to calculate) $ Other Non-Produc5on Related Costs & Expenses 0.30% (add a formula to calculate) $ Profit before taxes last 2 digits of student # as percentage ie; 301125199 = 9.9% (add a formula to calculate) $ Remaining Amount for Monthly Loan Payment (use a formula to calculate remaining % from 100% ex. 2.10%) (add a formula to calculate) $ *These expense percentages are realistic and borrowed from 'Restaurants Canada Research. Operations Report 2019 Now calculate the following: o Profit before taxes (%), based on your student number (see example above). o Remaining amount for loan repayment (%) of the monthly income (use a formula). o Calculate the monetary value ($) of each row of expenses (use formulas). o Calculate the monetary value ($) of the remaining amount for loan repayment (use a formula)

Technology Skills HOSP 101 Excel Assignment 3 WORKSHEET 2: Loan Repayment Plan To begin this worksheet, you will need to copy & paste the starting information from these three di^erent tables: Table 1 containing the following cells: Table 1 Loan Amount Use a formula: (Monthly Principal Payment * 24 Monthly payments) Annual Interest Rate (prime rate from website) Current rate from Website (Prime Rate + 2%) Start Date of Loan Choose a date (must be after date of completion of assignment) Table 2 containing the following cells: Table 2 Monthly Principal Payment Link from Results of Worksheet 1 (Remaining Amount for Monthly Loan Payment) Number of Payments 24 Total Interest Calculated from Result of Table 3 Total Cost of Loan Calculated from Result of Table 3 Table 3 containing the following cells: Table 3 Payment # Payment Date Beginning Loan Amount Monthly Principal Payment Monthly Interest Ending Balance 1 dd/mm/yy Link from table 1 (Loan Amt) Link from worksheet 1 (remaining amount for loan repayment) use a formula use a formula 2 3 etc.

Technology Skills HOSP 101 Excel Assignment 4 Complete the following tasks to finalize Worksheet 2 Table 2 Input the Principal Payment in Table 2 to start your calculations - Link from Results of your calculations in Worksheet 1 Use 24 as Number of Payments Note: The remaining cells in Table 2 can only be calculated once Table 1 & Table 3 are completed. Table 1 Use Table 2 to calculate the Loan Amount in Table 1 Create a Formula for the Loan Amount cell = Principal Payment * # of Payments To find the annual interest rate, go to the Bank of Canada website to find the most current interest rate. It will be listed as the prime rate under the interest rate category. Input the Annual Interest Rate as the current Prime Rate + 2%. Therefore, if Prime Rate is 5%, Your interest rate should be 7%. Choose a start date for your loan repayment, must be after current date. Table 3 Use Auto-Fill function to help you quickly populate the first two columns (Payment No. and Payment Date). Payments should occur once a month for 24 months. Beginning balance = Loan Amount from Table 1, you can use a link to this cell. Each subsequent beginning balance will need a formula applied to it consisting of the previous ending balance. Principal Payment is an absolute value. It should already be inputted in Table 2 from the results of Worksheet 1. The principal payment column will be the same for all cells in Table 3. To calculate the monthly interest rate, apply a formula to calculate the interest rate from the progressive decreasing beginning balance each month. (Hint: An interest rate of 7% annual will be expressed as 0.07. To calculate per month, it will be expressed as 0.07 /12) Finally, for the ending balance, apply a formula that subtracts the principal payment from the beginning balance. Note: loans decrease on the basis of the principal each month, interest is not subtracted from the outstanding balance, it is an extra expense.