Complete an excel sheet
2 115.112 Assessment 2 2201 ASSESSMENT 2 INSTRUCTIONS How to Approach this Assessment Please read all instructions in this document before starting the assessment. group has its own set of instructions with slightly different data. If you are found to have you will be penalised. This is an individual assessment. To complete the assessment, you need access to a computer, and a spreadsheet package. The recommended spreadsheet software is Microsoft Excel and the template provided is an Excel one. If you are not familiar with Excel you should work through the Excel Tutorial 1, Excel Tutorial 2, and Using formulas in Excel video, provided on Stream, under the Assessment Resources tab. You should first study the relevant material in the text and make sure you understand the concepts covered. Contact a member of the Teaching Team if you have any problems with Microsoft Excel, or with downloading the files from Stream and submitting the assessment on Stream. Completing the Assessment Entering Data You will complete the assessment on an Excel template, which is provided to you. You will find the template with the Assessment 2 booklet under the Assessment section. Use the naming convention Smith88888888.xls (or xlsx) where Smith is your surname and 88888888 is your i.d. number. Please note that if you try to modify the excel file without downloading it first, your modifications will NOT be saved. Remember to save to your own computer and to make regular backups. You must take care in how you prepare your answer. If the following instructions are not followed, you may lose marks: The Data sheet is where you enter the input data, typing amounts and words directly into the relevant cells. The other sheets contain the output of financial and numeric data. o On the output sheets, you should only use formulas, as this creates a workbook, which is truly flexible. (Refer to Budget Spreadsheet Completion on the following page). This assessment is marked directly on the file. The column, shaded in green, is dedicated for allocating marks. There are formulas used in these columns that pull your marks through to the Mark sheet. DO NOT make any changes to the sheets, e.g., add rows or columns. Contact me, Christelle Roos, if you feel you need to make any changes, and I will advise you. Budget Spreadsheet Completion Marks will be awarded for the functionality and flexibility of your spreadsheet. The aim of this assessment is to produce a workbook of spreadsheets in which any changes in financial and numeric data in the Data Input sheet, will result in an update of the whole workbook, therefore you must link the output with the Data Input sheet, and you should not enter numbers directly into the output areas on the other worksheets. To help you, certain cells have been shaded pink and other blue: Entries of numbers or names from the information given should be entered in pink cells. Entries of formulas should be made in blue cells. The formulas must use cell names to pull data in, e.g., =(A1+B5). Typing =500+250 does not constitute a proper formula. Not all blue or pink shaded cells require entries. Do Not use the green shaded cells. Submission of the Assessment (Uploading your file to Stream) Follow the nstructions for Submitting Assessments to Stream Assignment boxes under the Assessments section. Stream records the submission date and time. See the Course Guide for policies re extensions and late submissions. Penalties will be applied to late submissions for which an extension has not been granted. Assessment Queries Please feel free to keep in touch with the 115.112 team regarding this assessment, preferably through the orum. That way all students benefit and often we have found that the best way of learning is through discussion with your peers as well as teaching staff. Please note that this assessment is at individual, not group level. Discussion is fine but do not post your answers to the assessment on Stream through discussion forums, as that will lead to penalties. If you are uncertain, please contact the teaching staff. There are a large number of students in this course but that does not mean that you are not each individually very important to us. We value each student and will try to provide appropriate guidance to the best of our ability. Return of marked assessments and feedback You will be notified when your marked assessment is available, and it will be located at the same point on Stream where you uploaded your assessment. Note that the normal turnaround of assessments is three weeks. Feedback will be provided in the form of suggested solutions and overall comments by the marker. All the best with your assessment! The 115.112 Teaching Team 4 115.112 Assessment 2 2201 Assessment Brief John Maylor owns Maylor Tents, a business that is based in Auckland which sells deluxe yurt tents. The following is financial information relating to this company. Sales related information: Maylor Tents sold 150 units per month from July 2022 to September 2022. As the result of a planned advertising campaign (see detail , John expects that sales will increase to 300 units in October and then by 10 units per month until April 2023 the following year after which they are expected to decrease to 200 units. Currently, yurts sell for $480 per unit, but the price will be increased in the lead-up to Christmas with the selling price increasing to $500 in November and December and then reverting back to $480 in January and onwards. Sales are 55% cash and 45% credit. Credit sales customers pay 85% in the month following sale and 15% in the month after that. Purchases and Inventories: The yurts are imported from Australia at a cost of $210, which includes all import duties and other costs related to the purchases and importing of the tents. These purchases and import costs are paid in cash in the month that they incur and are not expected to change in the next six months. To prevent loss of sales due to stock outs, Maylor Tents has a target ending inventory level of Other information: The yurts are packaged and sent to customers by courier at a cost of $31 each. This cost is carried by Maylor tents who has an account with the courier company and pays the delivery costs the month following delivery. The business is planning to spend $1,650 per month on advertising in October and November in the lead-up to the New Zealand summer. This expense will be paid in the month it is incurred. Monthly salaries for Maylor Tents employees amount to $8,000 a month. The salaries are paid on the 20th of each month for that month. (Ignore PAYE and other deductions). General costs (including utilities, telephone, stationery, internet provider etc.) are estimated at $600 a month, paid on the 20th of the month following the month they are incurred. The annual insurance premium is $2,400 and will be paid in full on 1 October. John will take $12,000 a month from the business for his personal use. The balance of Cash at Bank on 1 October 2022 is $100,000. The balance of Accounts Payable for the Courier costs on 1 October 2022, is $4,500. The balance of Accounts Payable for General costs on 1 October 2022, is $585. Phoenix Dingle Phoenix Dingle Phoenix Dingle Phoenix Dingle Phoenix Dingle Phoenix Dingle Required: (i) On the Data Input sheet, enter the data as provided in the assessment brief. Some data has been pre-entered. You are only required to complete the missing data in the pink cells. (11 marks) (ii) On the Sales, Purchases & NCA sheet, complete the Sales Collection budget and Purchases budget for the quarter 1 October to 31 December 2022 by entering formulas in the blue cells. (37 marks) (iii) On the Cash Flow Sheet, complete the Cash Flow Forecast for each of the three months October, November, and December and the totals for the quarter 1 October to 31 December 2022 by entering formulas in the blue cells and descriptions (revenues or costs) in the pink cells. (25 marks) (iv) On the Income Statement Sheet, complete the Projected Income Statement for the quarter ended 31 December 2022 by entering formulas in the blue cells and descriptions (revenue or costs) in the pink cells. A month-by-month Income Statement is not required. (12 marks) (v) Check the flexibility of your workbook by changing the purchase price of the tents on the Data Sheet, from $210 to $180. Enter the resulting Net Profit and ending Cash Balance in the pink boxes provided on the Income Statement Sheet. (Remember to change the purchase price back to the original amount before submitting your assignment) (15 marks) [Total = 100 Marks] XXXXXXXXXX