vba代写：ACST101 S1 2018 Excel Assignment (7%)

程序代写 CS代考 / vba

ACST101 S1 2018 Excel Assignment (7%)

GETTING READY

This assignment counts toward a maximum of 7% of overall marks for this unit. However, you must also remember to complete the 10 Excel online quiz questions (Quiz 4) which counts for a further 5%.

The worksheets are protected. You can only enter data into the cells that are not locked to you. In the excel file provided, if you attempt to change the contents of a locked cell, you will receive a message saying that you are trying to change protected content. Leave the file protected.

Use MS Office Excel for Windows or Mac. The version can be no older than MS Office 2013. Use a computer lab at the University, if in doubt and don’t just switch to use another computer unless you know it uses the correct version. A penalty of 2 marks applies if the sheets are found to be unprotected (which can arise through use of an incorrect version).

Review the Kickstart Excel Videos plus the video in Week 4 lecture.

SUBMISION DUE DATE

To submit, load your saved file “Studentfamilyname”_”IDnumber”.xlsx Eg. Wong_44967012.xlsx to the Excel Assignment- Submission link under iLearn’s ‘Excel Assignment and Quiz’ page BEFORE THE DUE DATE and TIME (MON 9th April 9.55pm). Directions for ilearn submission are further below.

EXTENSIONS

No time extensions will be granted. (Where a Special Consideration application is made and approved, a supplementary assessment task will be offered…see the Unit Guide for link for online application).

1

A. THE AMORTISATION SCHEDULE SPREADSHEET WITH AN INTEREST-ONLY PERIOD of 5 YEARS

  1. Download the Excel Assignment file from ‘Excel Assignment & Quiz’ in iLearn.
  2. Save it in with the name: “Studentfamilyname”_”IDnumber”.xlsx Eg. Wong_43567859.xlsx
  3. →Place your student number digit by digit in cells F2 to M2
  4. →Enter your name to the cells shown at cells O2 and O3
  5. In cell C2 is the number 60. This represents the period (in months) which the loan requires

    only repayments equal to the month’s interest charge. i.e. The interest-only period.

    An interest-only (IO) period is more common where loans are for residential real estate purchased for investment purposes, rather than for owner-occupation. The bank requires the borrower to repay interest each month for the IO period. After the IO period, the loan reverts to Principal & Interest (P&I) basis (unless IO terms can be renegotiated).

  6. →In cell C3 show the total of the first three digits in your student number using the =SUM() function in Excel and referencing cells F2 to H2. This is interpreted as the number of years of further years of the loan, after the 60 months of interest only repayments. This further period is the period in which the loan will have equal monthly P&I repayments that fully amortise the loan to zero.

    In cell C4, the number of months of the P&I repayments based on 12 times the number of years in cell C3 is shown.
    For example, if C3 was 21, the total months in the loan term is then:

IO period (cell C2): P&I period (cell C4): TOTAL months :

60 months =C3*12 = 252

312 months

  1. In cell C5 the nominal interest rate of 6.00% p.a. compounding monthly is given. It is displayed as a percentage. In cell C6 the monthly rate is automatically computed.
  2. Cell C7, shows the amount of the initial loan, $500,000.
  3. →Cell C8 is the repayment you must calculate being the amount that is paid each month,

    from month 61 to the end of the loan.

  4. You will be preparing a loan amortisation schedule showing:
    • –  IO period: the month-by-month repayment schedule for the 60 months interest only (IO) loan period, followed by,
    • –  P&I period: the month-by-month reduction in the balance of a loan to nil, based on the repayment you calculate in cell C8. Each monthly repayment amount is equal (‘Repayment’ (Column D) should be shown as a negative while ‘Interest’ (Column C) is positive amount).

      P&I Repayments are at each month-end, with the formula to be entered to cell C8. Inputs are the loan amount (cell C7), interest rate monthly (cell C6) and number of months cell C4).

      11. →Create month labels (1, 2, 3 etc) column A. There must be a row for each month of the loan.

      An example of what you should have in Excel so far appears below (although it does not show all the months). You should check now that you have this….with your student number and name, not the sample ones.

2

12. →Complete the amortisation schedule for all months.

  • –  YOU MUST USE FORMULAS WHICH REFERENCE RELEVANT CELLS, FOR ALL ENTRIES TO

    COLUMN B, C, D and E WITHIN THE SCHEDULE. i.e. Don’t type any numbers into these

    columns.

  • –  (‘Repayment’ (Column D) should be shown as a negative while ‘Interest’ (Column C) is a

    positive amount).

  • –  DO NOT ROUND ANSWERS. You should format to display currency (eg $3400.86) where

    relevant but don’t round.

  • –  YOU MUST USE ABSOLUTE CELL REFERENCES IN APPROPRIATE CELLS in columns B, C, D

    and E of schedule TO ENABLE COPYING. (see Kickstart videos for the meaning of

    ‘absolute cell references’)
    13. →Format the Loan Amortisation Schedule. As a minimum, display:

  • –  The relevant dollar currency format to columns (including cents).
  • –  ALL BORDERS [there is a shortcoming in Excel 2013 when adding borders if a worksheet

    is protected, as in the case of this assignment. To apply ALL BORDERS, select area you want all borders around; right click, select Format Cells. Then from the Borders tab, create the various inside and outside border lines to make all borders]. Yes, the format mark includes a half mark for achieving these borders. Source:http://www.excelforum.com/excel-general/847418-unable-to-use-all-borders-in- protected-sheet-for-excel-2007-a.html

    (5 MARK ARE POSSIBLE FOR THIS ELEMENT ‘A’).

B. ADDED QUESTIONS SPREADSHEET
14. →Complete the 4 questions on the ‘Added Questions’ spreadsheet using formulas

referencing the inputs to your ‘Amortisation Schedule’ spreadsheet cells. The answers MUST USE EXCEL fx functions (only answer using either PV(), FV(), RATE(), NPER(), PMT(), IPMT(), PPMT(),CUMIPMT(), CUMPRINC() functions). These functions were demonstrated in lecture 4.
DO NOT round the answers. Answers must be positive (not negative). Display answers using the dollars and cents format. You MUST reference cells in Amortisation Schedule for each and every key parameter within your formula. Eg if you use =PMT(.005,252,-500000) , this will be marked incorrect as it should reference the relevant cells on the Amortisation Schedule rather than using numbers as inputs to the function)

(2 MARKS FOR THIS ELEMENT ‘B’ being 0.5 each question)

15. Save the spreadsheet

The third worksheet (‘Markers Use ONLY’) is for markers’ use. It shows where marks can be deducted consistent with the requirements in these instructions (see further below).

3

SUBMISSION

16. To submit, load your saved file “Studentfamilyname”_”IDnumber”.xlsx Eg. Wong_44967012.xlsx to the Excel Assignment- Submission link under iLearn’s ‘Excel Assignment and Quiz’ page BEFORE THE DUE DATE and TIME (MON 9th April 9.55pm). Follow the guide below.

How do I submit the excel assignment in iLearn?

  1. Scroll down in the iLearn section called “Excel Assignment ” to “Excel Assignment – Submission links”
  2. Click on the assignment link. This will be your tutor for the unit. Example:

In the Submission status

page, click Add submission.

3.

Your tutor’s name shows on your submission link…not your name

4. On the Edit submission page: Read the appropriate declaration, for an individual submission, then select the check box above the declarations.

5.

In the File submissions box, either drag-and-drop your assignment file(s) from the Explore or

Finder window, or manually add them, as follows:

Click Add icon:

4

6.

  1. In the File picker, click Choose file.
  2. In the File Upload window, browse for and select the file. Click Open and then

    click Upload this file.

Click Save changes after following steps 1-5

and you will have successfully submitted.

7.

Your submitted text displays. From this page, you can edit your submission, or return to the

assignment link before the due date and click

submission.

Edit submission icon, to make changes to your

5

8. Click on the submitted file, and you will be given an option to delete file and then you can repeat 4, 5 and 6 and make a resubmission.

You may edit and resubmit unlimited times before the due date and time. But, whatever is there at the deadline is the file that will be marked.

6

DETAILED MARKING GUIDE

Failure to follow the instructions will mean a deduction of marks as follows: Overall

  • –  Do not attempt to ‘unprotect’ any of the three sheets in the Excel workbook. If there is evidence of this, you will receive no marks. The sheets are protected to maintain the strict structure to enable auto marking.
  • –  Deduct 0.5 for not following file name convention or wrong lodgement
  • –  Deduct 2.0 if not using MS Office Excel for Windows 2013 or Mac 2013 or more recent

    version (if the sheets are not password protected, you are taken as having used the

    wrong version).

  • –  Deduct all marks for Amortisation Schedule (of possible 5) if do not use own student

    number in set up instructions Step 3 (suggests it’s not your work!!)

A. AMORTISATION SCHEDULE (TOTAL POSSIBLE MARKS: 5)

Deductions are made from base marks (5 in total) for submitted assignments. Students cannot receive less than zero for this section

Amortisation calculations (of 2 possible marks)

  • –  Cell C3: Deduct 1⁄2 mark for wrong number of years or not using SUM() function or not

    referencing cells to determine it.

  • –  Cell D11: Deduct 1⁄2 mark if wrong Interest Only repayment amount in cell D11 or for

    not referencing cell locations.

  • –  Cell C8: Deduct 1 for arriving at an incorrect repayment amount in cell C8 in the

    amortisation schedule based on your inputs or if not referencing cell locations.

  • –  Column D: Deduct 0.5 marks if repayment is as a positive rather than negative and

    other amounts positive.

  • –  Deduct 2 marks (all marks this section) if don’t fully complete the amortisation

    schedule (to reveal an ending zero balance).

  • –  Deduct 2 if fail to include the 60 month Interest Only period months 1 to 60. Absolute vs Relative Cell References (of 2 possible marks)

– Deduct 1/2 mark for each column (B,C,D,E) that does not use absolute cell references

where required, or, uses absolute cell references when relative cell references

required (step 12)
Formatting (of 1 possible mark)
– Deduct up to 1 mark for not complying with formatting step 13

B. ADDED QUESTIONS (of 2 possible marks)

  • –  Deduct 1⁄4 mark each if provide answer as negative, (positive is required), or if don’t reference the cells on Amortisation Schedule that contain the key parameters
  • –  Deduct 1⁄4 mark each question where answer not formatted as currency or whole number as applies.
  • –  Deduct 1⁄2 mark for each other inaccuracy per steps 14.

    The above detailed instructions and guide are to make it as clear as possible as to the basis of marks allocation and to make the marking basis uniform. Nevertheless, if there is any circumstance that is not clear from the above, email ACST101@mq.edu.au. The Unit Convenor will be sole arbiter.

7

Related Posts