Planilha de Controle-Gastos Mensais e Plano de Amortização de Empréstimo

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 15000000 Restaurants Catering Student ending in 6 7 3500000 Event Planning Student ending in 4 5 2500000 Bakeshop Cafes Student ending in 2 3 1500000 Other Choose your own Business Types Student ending in 0 1 2000000 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 3480 Salaries Wages 3100 Rental Leasing 810 Amortization Depreciation 240 Marketing Entertainment 280 Utilities 220 Repair Maintenance 190 Professional Business Fees 120 Office Computer Related Expenses 070 Business Taxes Licenses Permits 080 Insurance 060 Subcontracts 060 Telephone Internet Other Telecommunications 030 Travel Meeting Conventions 030 Other NonProduction Related Costs Expenses 030 Profit Before Taxes and Interest Use the last 2 digits of your Student Number as percent ie 59 59 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 httpswwwbankofcanadacaratesdailydigest 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 ddmmyyyy Loan Amount remaining balance for loan repayment from Worksheet 1 beginning balance principal payment 2 ddmmyyyy previous ending balance 3 ddmmyyyy 4 ddmmyyyy Use a AutoFill 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 To populate the principal payment amount you will need to apply a formula that divides your loan amount by 24 months 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 650 pa will be expressed as 00650 To calculate per month it will be expressed as 00650 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