Follow the steps below to complete the assignment: Workbook: Begin by creating a new workbook. In the

Fantastic news! We've Found the answer you've been seeking!

Question:

Follow the steps below to complete the assignment:

Workbook: Begin by creating a new workbook. In the workbook you will want to have 2 worksheets. Name your worksheets as follows:

Sheet 1: Monthly Expenses
Sheet 2: Loan Repayment Plan

Personalization of your work

Use the chart below to enter in your estimated monthly income as a dollar value, as assigned based on your student number. This value will be placed into the first cell in the right hand column in Worksheet 1.

Hotel (Student # ending in 8, 9)	$150,000.00
Restaurants & Catering (Student # ending in 6, 7)	$35,000.00
Event Planning (Student # ending in 4, 5)	$25,000.00
Bakeshop & Cafes (Student # ending in 2, 3)	$15,000.00
Other "Choose your own" Business Types (Student # ending in 0, 1)	$20,000.00

Now based on your business type, choose a creative company name and input into the title of Worksheet 1.
Note: Beginning balance/ Loan amount for Worksheet 2 will also be personalized based on a result of your calculations.

Worksheet 1

Sheet 1: Monthly Expenses

To begin, recreate the following cells in your Excel worksheet:

Distribution of Monthly Expenses for [Company Name]
Monthly Income	100%	INPUT AMOUNT BASED ON STUDENT # HERE $
Costs of Sales	34.80%	$
Salaries & Wages	31.00%	$
Rental & Leasing	8.10%	$
Amortization & Depreciation	2.40%	$
Marketing & Entertainment	2.80%	$
Utilities	2.20%	$
Repair & Maintenance	1.90%	$
Professional & Business Fees	1.20%	$
Office & Computer Related Expenses	0.70%	$
Business Taxes, Licenses & Permits	0.80%	$
Insurance	0.60%	$
Subcontracts	0.60%	$
Telephone, Internet & Other Telecommunications	0.30%	$
Travel, Meeting & Conventions	0.30%	$
Other Non-Production Related Costs & Expenses	0.30%	$
Profit Before Taxes and Interest	Use the last 2 digits of your Student Number as percent. i.e. 59 = 5.9%	$
Remaining Amount for Loan Repayment	Calculate leftover percentage	$

*These expense percentages are realistic and borrowed from 'Restaurants Canada Research. Operations Report 2019

Now, using formulas, calculate the following:
- Loan repayment as a percentage (%) of the monthly expenses.
- Calculate the monetary value ($) of each expense category.
- Profit % Amount will be based on your student number
- Determine the loan repayment amount as a monetary value ($).

Worksheet 2

Sheet 2: Loan Repayment

To begin this worksheet, you will need to create three different tables:

Note that tables 1 & 2 can be either on top of each other or side by side but they must make up the first part of your worksheet.
Table 1 containing the following cells:

	Enter Values
Loan Amount	Use Remaining Amount from Worksheet 1 multiplied by 24 months $
Annual Interest Rate	Current rate from link %
Start Date of Loan	Must be on/past submission date of assignment dd/mm/yyyy

Calculate the Loan Amount, results from work in Worksheet 1.
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.
Choose a start date for your loan repayment, must be after current date.
Table 2 containing the following cells:

Monthly Principal Payment	link from worksheet 1 - Remaining Amount Left for Loan Payment $
Number of Payments	24
Total Interest	Apply formula from Result of your work in Table 3 $
Total Cost of Loan	Apply formula from Result of your work in Table 3 $

Table 2 requires more technical skill to populate.
- To populate the monthly payment amount, copy and paste the cell Remaining Amount for Loan Repayment as a link from your worksheet 1.
- For the number of payments, 24 months.Note: The number of payments is the key to how many rows you will need in table 3.
- Note: The remaining cells in Table 2 will be from the results of your work in Table 3.
- For the Total Interest, you will need to apply a formula that will calculate the sum of all the cells in the Interest column from Table 3.
- For the Total Cost of the Loan, apply a formula that adds the original loan amount and the Total Interest amount.
- Table 3 containing the following cells:Table 3 must appear below or beside table 1 & 2 in your worksheet 2.

Payment No.	Payment Date	Beginning Balance	Principal Payment	Interest	Ending Balance
1	dd/mm/yyyy	=Loan Amount	=remaining balance for loan repayment from Worksheet 1	$	=(beginning balance - principal payment)
2	dd/mm/yyyy	= previous ending balance	$	$	$
3	dd/mm/yyyy	$	$	$	$
4	dd/mm/yyyy	$	$	$	$

Use a Auto-Fillfunction to help you quickly populate the first two columns (Payment No. and Payment Date). Repayments should occur once a month.
To populate the beginning balance, you will need to copy and paste the cell Loan Amount from section 1 as a link to this cell.
- Each subsequent beginning balance will need a formula applied to it consisting of the previous ending balance.
Principal payment amount has been calculated from Worksheet 1 and should have been linked to "Monthly Principal Payment" in Table 2. Use the fill handle function to populate the remaining cells in this column. The principal payment amount will be the same for all cells in this column.
To calculate the monthly interest rate, apply a formula to extract the interest rate from the beginning balance in each line. (Hint: An interest rate of 6.50% p.a. will be expressed as 0.0650. To calculate per month, it will be expressed as 0.0650 /12)
Finally, for the ending balance, apply a formula that subtracts the principal payment from the beginning balance.
Repeat all the steps until the loan is paid off. Final row should have an ending balance of ZERO (0)

Some important notes to consider:

Do not use Excel as a calculator! Formulas must be created with cell addresses not numbers. Marks will be deducted if this is found.

There are multiple ways to achieve each calculation.

Be sure that all % cells are formatted to % cells and all $ cells are formatted to currency cells. This means that you are not typing the symbols but that you have set the function for them to appear based on the format of the cell.

All numeric cells in this worksheet should be rounded to 2 decimal points.

Once you have applied all of your calculations and all of your cells are populated, complete Worksheets with the following:
- Ensure the title cells are merged and apply a bold font to the titles in each table
- Apply a background colour to the title cells
- Apply a borders to all cells
- Use background and shading to accentuate and differentiate the information in each column.
- Freeze pane the first row of cells Worksheet 2 - Table 3.

Final Steps

Save your Workbook as "Business Name - Loan Repayment.xls or .xlsx" and submit to the assignment folder.

You will be graded on the following four categories:

Cell Formatting

Cell font style & size
Wrap text anywhere that wrap text is required
Cell width to ensure all values can be seen
Cell formatting of numeric values to represent as % or $ as required
Cell formatting of numeric values to 2 decimal points
Borders of all cells
Shading & colouring of cells to accentuate and differentiate information
Freeze pane

Calculations/ Formulas

Basic calculations of addition/ subtraction, SUM function
Intermediate calculations of multiplication/ division
Linking of cell to another cell location

Workbook Formatting

Correct work on each worksheet
Worksheets are correctly named
Workbook is saved in an appropriate file name

Personalization

Profit percentage is calculated based on your student number as outlined.
Company name is original.
Starting loan amount is calculated from Worksheet 1.

Follow the steps below to complete the assignment: Workbook: Begin by creating a new workbook. In the workbook you will want to have 2 worksheets. Name your worksheets as follows: Sheet 1: Monthly Expenses Sheet 2: Loan Repayment Plan Personalization of your work Use the chart below to enter in your estimated monthly income as a dollar value, as assigned based on your student number. This value will be placed into the first cell in the right hand column in Worksheet 1. Hotel (Student # ending in 8, 9) $150,000.00 Restaurants & Catering (Student # ending in 6, 7) $35,000.00 Event Planning (Student # ending in 4, 5) $25,000.00 Bakeshop & Cafes (Student # ending in 2, 3) $15,000.00 Other "Choose your own" Business Types (Student # ending in 0, 1) $20,000.00 Now based on your business type, choose a creative company name and input into the title of Worksheet 1. Note: Beginning balance/ Loan amount for Worksheet 2 will also be personalized based on a result of your calculations. Worksheet 1 Sheet 1: Monthly Expenses To begin, recreate the following cells in your Excel worksheet: Distribution of Monthly Expenses for [Company Name] Monthly Income 100% INPUT AMOUNT BASED ON STUDENT # HERE $ Costs of Sales 34.80% $ Salaries & Wages 31.00% $ Rental & Leasing 8.10% $ Amortization & Depreciation 2.40% $ Marketing & Entertainment 2.80% $ Utilities 2.20% $ Repair & Maintenance 1.90% $ Professional & Business Fees 1.20% $ Office & Computer Related Expenses 0.70% $ Business Taxes, Licenses & Permits 0.80% $ Insurance 0.60% $ Subcontracts 0.60% $ Telephone, Internet & Other Telecommunications 0.30% $ Travel, Meeting & Conventions 0.30% $ Other Non-Production Related Costs & Expenses 0.30% $ Profit Before Taxes and Interest Use the last 2 digits of your Student Number as percent. i.e. 59 = 5.9% $ Remaining Amount for Loan Repayment Calculate leftover percentage $ *These expense percentages are realistic and borrowed from 'Restaurants Canada Research. Operations Report 2019 Now, using formulas, calculate the following: Loan repayment as a percentage (%) of the monthly expenses. Calculate the monetary value ($) of each expense category. Profit % Amount will be based on your student number Determine the loan repayment amount as a monetary value ($). Worksheet 2 Sheet 2: Loan Repayment To begin this worksheet, you will need to create three different tables: Note that tables 1 & 2 can be either on top of each other or side by side but they must make up the first part of your worksheet. Table 1 containing the following cells: Enter Values Loan Amount Use Remaining Amount from Worksheet 1 multiplied by 24 months $ Annual Interest Rate Current rate from link % Start Date of Loan Must be on/past submission date of assignment dd/mm/yyyy Calculate the Loan Amount, results from work in Worksheet 1. 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. Choose a start date for your loan repayment, must be after current date. Table 2 containing the following cells: Monthly Principal Payment link from worksheet 1 - Remaining Amount Left for Loan Payment $ Number of Payments 24 Total Interest Apply formula from Result of your work in Table 3 $ Total Cost of Loan Apply formula from Result of your work in Table 3 $ Table 2 requires more technical skill to populate. To populate the monthly payment amount, copy and paste the cell Remaining Amount for Loan Repayment as a link from your worksheet 1. For the number of payments, 24 months. Note: The number of payments is the key to how many rows you will need in table 3. Note: The remaining cells in Table 2 will be from the results of your work in Table 3. For the Total Interest, you will need to apply a formula that will calculate the sum of all the cells in the Interest column from Table 3. For the Total Cost of the Loan, apply a formula that adds the original loan amount and the Total Interest amount. Table 3 containing the following cells: Table 3 must appear below or beside table 1 & 2 in your worksheet 2. Payment No. Payment Date Beginning Balance Principal Payment Interest Ending Balance 1 dd/mm/yyyy =Loan Amount =remaining balance for loan repayment from Worksheet 1 $ =(beginning balance - principal payment) 2 dd/mm/yyyy = previous ending balance $ $ $ 3 dd/mm/yyyy $ $ $ $ 4 dd/mm/yyyy $ $ $ $ Use a Auto-Fill function to help you quickly populate the first two columns (Payment No. and Payment Date). Repayments should occur once a month. To populate the beginning balance, you will need to copy and paste the cell Loan Amount from section 1 as a link to this cell. Each subsequent beginning balance will need a formula applied to it consisting of the previous ending balance. Principal payment amount has been calculated from Worksheet 1 and should have been linked to "Monthly Principal Payment" in Table 2. Use the fill handle function to populate the remaining cells in this column. The principal payment amount will be the same for all cells in this column. To calculate the monthly interest rate, apply a formula to extract the interest rate from the beginning balance in each line. (Hint: An interest rate of 6.50% p.a. will be expressed as 0.0650. To calculate per month, it will be expressed as 0.0650 /12) Finally, for the ending balance, apply a formula that subtracts the principal payment from the beginning balance. Repeat all the steps until the loan is paid off. Final row should have an ending balance of ZERO (0) Some important notes to consider: Do not use Excel as a calculator! Formulas must be created with cell addresses not numbers. Marks will be deducted if this is found. There are multiple ways to achieve each calculation. Be sure that all % cells are formatted to % cells and all $ cells are formatted to currency cells. This means that you are not typing the symbols but that you have set the function for them to appear based on the format of the cell. All numeric cells in this worksheet should be rounded to 2 decimal points. Once you have applied all of your calculations and all of your cells are populated, complete Worksheets with the following: Ensure the title cells are merged and apply a bold font to the titles in each table Apply a background colour to the title cells Apply a borders to all cells Use background and shading to accentuate and differentiate the information in each column. Freeze pane the first row of cells Worksheet 2 - Table 3. Final Steps Save your Workbook as "Business Name - Loan Repayment.xls or .xlsx" and submit to the assignment folder. You will be graded on the following four categories: Cell Formatting Cell font style & size Wrap text anywhere that wrap text is required Cell width to ensure all values can be seen Cell formatting of numeric values to represent as % or $ as required Cell formatting of numeric values to 2 decimal points Borders of all cells Shading & colouring of cells to accentuate and differentiate information Freeze pane Calculations/ Formulas Basic calculations of addition/ subtraction, SUM function Intermediate calculations of multiplication/ division Linking of cell to another cell location Workbook Formatting Correct work on each worksheet Worksheets are correctly named Workbook is saved in an appropriate file name Personalization Profit percentage is calculated based on your student number as outlined. Company name is original. Starting loan amount is calculated from Worksheet 1