Need help with Management Accounting assignment (need excel part completed)!!!

AYB225 ASSIGNMENT SEMESTER 1 2015 ALL DATA RELATES TO THE PERIOD UNDER CONSIDERATION IN THIS REVIEW, AND ACTUAL AND STANDARD COSTS PER UNIT ARE UNCHANGED FROM THE PREVIOUS PERIOD BUDGET DATA FOR BUDGET DATA FOR BUDGET DATA FOR DANCE Production budget standards for: 5,700.00 units 2.00 metres Direct materials (fabric) Direct labour 1.00 hour Variable overhead Fixed overhead $15.00 per metre $32.00 per hour $9.00 per hour $20.00 per hour Overhead costs for Dance have been determined as follows: An overhead budget is determined for a particular period for the Shoe Section as a whole. This cost is shared across the three product lines for budgeting and variance calculation purposes in proportion to the budgeted direct labour hours. The fixed overhead rate is calculated taking standard (budget) data for all three lines of the product into account . Budgeted fixed overhead for the period is $203,200.00 The budgeted fixed overhead for Dance is $114,000.00 Based on the production budget for the total number of units: DL hour per unit Number of units Dance 5,700 1.00 Run 2,500 0.90 Walk 1,300 1.70 Non-manufacturing costs Budgeted and actual selling and administrative costs related to Dance are as follows: Variable costs per unit are $5.00 ; and Fixed costs for the period total is $19,000.00 ACTUAL DATA There is no opening or closing WIP, and no inventory of direct materials on hand at the beginning of the period. Standard costs in Finished Goods are the same as last period, opening inventory is 270 units Number of units produced in the period is 8,000.00 Units sold in the period 4,700.00 at $120.00 per unit Purchases of direct materials for cash Direct materials used in the period Direct labour hours worked in production Actual variable overhead Dance share of actual fixed overhead 17,000.00 metres at 16,500.00 metres 8,100.00 DL hour at $8.50 per DL hour $109,076 * * Actual fixed overhead incurred by the Shoe Department is $14.50 per metre $32.95 per hour $194,423.19 This is shared for the purposes of variance calculation in proportion to the actual direct labour hours incurred. After consulting data related to Walk and Run, Dance incurs actual fixed overhead as stated above. (For the journal entries, credit Accounts Payable for actual overhead incurred.) ADDITIONAL DATA COLLECTED ON OVERHEAD COSTS FOR ABC COSTING CALCULATIONS Run and Walk include features that make them relatively machine intensive and involve considerable time in setting up equipment involved in design, drawing, cutting, tracing etc . The costs can be grouped into four categories: * costs driven by direct labour hours, * costs driven by machine hours, * costs related to ordering and transportation, which are driven by the number of orders, and * costs related to testing, which are driven by the number of product tests in the period. An analysis of the overhead costs for the Shoe Department into those categories results in the following cost pools: OH Cost Pool Budgeted OH Costs DL-related $105,000 Designing $145,000 Purchasing $18,750 Testing $25,890 Expected usage of the resources by the different products (based on budgeted production) is reproduced below: Dance Run Walk Budgeted production units 5,700.00 2,500.00 1,300.00 DL hours per unit 1.00 0.90 1.70 Design hours per unit 3.00 5.00 10.00 Number of purchase orders 26.00 30.00 102.00 Number of tests 10.00 13.00 43.00 Data Page 1 of 20 2014-1 AYB225 ASSIGNMENT SEMESTER 2_2014 INSTRUCTIONS FOR COMPLETING EXCEL SCHEDULES. All the numbers in the schedules should be drawn from the Data Sheet or other earlier schedules when The point of this exercise is to create a management tool, which can be used by only changing figures o Check your work before submitting it, because if there are 2 or more numbers on the schedules once y that schedule will be zero. This includes numbers entered as text for working purposes. You may add co To be clear, all worksheets are to be completed using formulas that draw values from the data provided 0.5 marks for each cell that contains a numerical value that has not been obtained using an Excel form the column next to the relevant variance value. SCHEDULE ANSWERS ON BLACKBOARD WHEN YOU HAVE COMPLETED DO NOT FORGET TO ENTER THE ANSWERS ON BLACKBOARD There are six tests to complete, with each having detailed instructions. Save and Submit each attempt on Blackboard, otherwise it will record a score of zero. Make sure you enter all answers each time, as your final attempt, you have up to 5 mark. Previous cor You do not have use up all the attempts. The last one counts. SCHEDULE 1 It is not necessary to put U or F next to the variances, if you want to do this insert an extra column and SCHEDULE 2 You can have WORDS in the workings here, ie. ACT QTY x ACT PRICE, but no hard coded figures The figures in the variance summary should come from your workings below on the same schedule You must use an \"if\" statement to enter the label \"U\" or \"F\" in the column next to the relevant variance v SCHEDULES 3 & 4 Complete all the blank boxes to create a logical Income Statement as you learned in previous units Draw the cost per unit from the Calculation Sheet SCHEDULE 5 All your figures should come from previous schedules, do not draw from figures in this schedule as bala which will not allow you to find errors SCHEDULE 6 Calculate your figures on the Calculation Sheet and just have a single reference in each cell Instructions Page 2 of 20 pages js2014 AYB225 ASSIGNMENT SEMESTER 2_2014 Instructions Page 3 of 20 pages js2014 AYB225 ASSIGNMENT SEMESTER 2_2014 #REF! Instructions Page 4 of 20 pages js2014 AYB225 ASSIGNMENT SEMESTER 2_2014 her earlier schedules when that becomes relevant and practical d by only changing figures on the Data Sheet s on the schedules once you press "Show Formulas", then the mark for purposes. You may add column names and that's fine. ues from the data provided. In each worksheet, students will be deducted ained using an Excel formula. Use an IF statement to label \"U\" or \"F\" in WERS ON BLACKBOARD. up to 5 mark. Previous correct answers do not count. nsert an extra column and write an IF statement no hard coded figures on the same schedule t to the relevant variance value (see the example in the worksheet). arned in previous units res in this schedule as balancing figures, as you may create a circular calculation nce in each cell Instructions Page 5 of 20 pages js2014 AYB225 ASSIGNMENT SEMESTER 2_2014 Instructions Page 6 of 20 pages js2014 AYB225 ASSIGNMENT SEMESTER 2_2014 Instructions Page 7 of 20 pages js2014 AYB225 ASSIGNMENT ACTUAL COST VARIANCES Activity (units of production) Direct Labour Direct Materials Variable Overhead Fixed Overhead Total Cost Units Produced Schedule 1 Page 8 of 20 pages js2014 AYB225 ASSIGNMENT FLEXIBLE BUDGET VOLUME VARIANCES STATIC BUDGET Schedule 1 Page 9 of 20 pages js2014 AYB225 ASSIGNMENT SUMMARY OF VARIANCES FOR STANDARD COST F/U #REF! LRV LEV MPV MEV TOTALS $ 7,695.00 U SET UP ALL FORMULAS BELOW AND LINK TO SUMMAR The first calculation has been entered as an example of linking formulas. You can follow the way the c to suit your own approach DIRECT LABOUR ACT QTY 8,100.00 x ACT PRICE x $ 32.95 $ 266,895.00 ACT QTY x 8,100.00 x LRV $ 7,695 U DIRECT MATERIAL VARIABLE OVERHEAD FIXED OVERHEAD Schedule 2 Page 10 of 20 pages js2014 AYB225 ASSIGNMENT OVERHEAD VARIANCES FOR NORMAL COSTING VARIABLE OVERHEAD FIXED OVERHEAD Schedule 2 Page 11 of 20 pages js2014 AYB225 ASSIGNMENT ANCES FOR STANDARD COSTING F/U VARIABLE SPENDING VARIABLE EFFICIENCY FIXED SPENDING FIXED VOLUME BELOW AND LINK TO SUMMARY ABOVE s. You can follow the way the calculations have been set out in tutorials, or change it t your own approach IRECT LABOUR STD PRICE $ 32.00 $ 259,200.00 RECT MATERIAL ABLE OVERHEAD XED OVERHEAD Schedule 2 Page 12 of 20 pages js2014 AYB225 ASSIGNMENT TING Schedule 2 Page 13 of 20 pages js2014 AYB225 ASSIGNMENT Calculation of unit costs for Schedules 3 and This worksheet will help to complete schedules 3 and 4; the cost per unit figures should be linke NORMAL (total $) STANDARD (total $) Direct Labour Direct Materials Variable Overhead Fixed Overhead TOTALS Calculations for 3 and 4 Page 14 of 20 pages js2014 AYB225 ASSIGNMENT Schedules 3 and 4 nit figures should be linked to the relevant schedules COST/UNIT NORMAL COSTING COST/UNIT STANDARD COSTING Ogan Yigitbasioglu: Do not fill the orange area. Fill the two cells below. Calculations for 3 and 4 Page 15 of 20 pages js2014 AYB225 ASSIGNMENT #REF! SCHEDULE 3 NORMAL ABSORPTION COSTING SCHEDULE 4 STANDARD ABSORPTION COSTING Insert figures in non-shaded cells nr of units cost per unit Insert figures in non-shaded cells $ $ nr of units SALES SALES Less COGS - link to worksheet Less COGS - link to worksheet Adjustment for variances Adjustment for variances Gross Margin Gross Margin Less Selling and Administration costs Less Selling and Administration costs Fixed Fixed Variable Variable Net Profit Net Profit Schedules 3 and 4 Page 16 of 20 pages cost per unit $ $ js2014 AYB225 ASSIGNMENT JOURNALS FOR STANDARD COSTING SYSTEM DR CR Purchase of Materials Materials Cash Usage of Direct Material WIP MPV MEV Materials Usage of Direct Labour WIP LRV LEV Payroll (Payable) Overhead Incurred OH Accounts Payable Overhead Applied WIP OH Recognition of Overhead Variances OH Variable Spending Variance Variable Efficiency Variance Fixed Spending Variance Fixed Volume Variance Cost of Goods Finished FG WIP Cost of Goods Sold COGS FG Disposition of Variances COGS MPV MEV LRV LEV Fixed Spending Variance Fixed Volume Variance Variable Spending Variance Variable Efficiency Variance Schedule 5 Page 17 of 20 pages js2014 AYB225 ASSIGNMENT SEMESTER 1_2015 You may add column names. TRADITIONAL COSTING ACTIVITY BASED COSTING Calculations for 6 Page 18 of 20 pages js2014 AYB225 ASSIGNMENT SEMESTER 1_2015 Calculations for 6 Page 19 of 20 pages js2014 AYB225 ASSIGNMENT SEMESTER 2_2014 COST COMPARISON - STANDARD AND ABC SYSTEMS #REF! Existing OH System ABC System OH Rate OH Rates Per DL hour DL-related Designing Purchasing Testing OH costs per unit OH costs per unit Dance Run Walk Dance Run Walk Jenny Staib THE FIGURES IN THIS SCHEDULE MUST COME FROM THE WORKSHEET (CALCULATIONS FOR 6) Schedule 6 Page 20 of 20 pages js2014 QUEENSLAND UNIVERSITY OF TECHNOLOGY QUT Business School School of Accountancy AYB225 Management Accounting SEMESTER ONE 2017 ASSESSMENT ITEM TWO: REPORT (Weighting Total: 30%) Due Date and Time: Submission: Group or Individual: Length: 19 May 3 p.m. Online submission via Blackboard Individual 1,000 words This assessment item assesses the following learning outcomes: Knowledge and Technological Skills (KS) 1.2 Apply technical and technological skills appropriate and effective for real world business purposes and contexts. Higher Order Thinking (HO) 2.2 Exercise independent judgment and initiative in adapting and applying knowledge and skills for effective planning, problem solving and decision making in diverse contexts. Professional Communication (PC) 3.1 Use information literacy skills, and communicate effectively and professionally in written forms and using media appropriate for diverse purposes and contexts. AYB225 - Report Page 1 This assignment is an individual assignment. This means you cannot collaborate with anyone in any way in the completion of the Excel schedules and the answers. Submission Requirements: Your submission has two parts. Both parts must be submitted to Blackboard by the due date and time for a complete submission of this assessment item. Refer to the instructions on Blackboard under Assessment Item 2 regarding uploading of files. 1) Uploading of Files into Blackboard a) Excel file containing the completed Schedules Part A b) Answers to management questions using pro-forma Part B & C Make sure you check your file once uploaded to ensure it is readable. No marks will be given for submissions where the file is corrupt and unreadable. This unit uses the Turnitin software to detect plagiarism. Turnitin compares submissions to other submissions, both current and past, and sources that are available online such as textbooks, scholarly articles, websites and blogs. 2) Entering Your Results to Part A into Blackboard You are required to enter the results to Schedules 1-6 into the \"Assignment Schedule Answers\" forms on Blackboard under Assessment Item 2. You have up to 5 attempts to enter and check your results for each schedule. You do not have to use up all 5 of the attempts. Only the last attempt will count towards your final mark. Late Assessment and Extensions In both your QUT study and your professional life you will be expected to meet deadlines. In keeping with this expectation, assessment submitted after the due date will not be marked and will receive a grade of 1 or 0%. If special circumstances prevent you from meeting the assessment due date, you can apply for an extension. If you don't have an approved extension, you should submit the work you have completed by the due date and it will be marked against the assessment criteria. AYB225 - Report Page 2 Task Description You are to assist the review process of Sneakers Unlimited's costing system by providing management with the following items related to the product line \"Dance\". A. Six technical reports with two supporting schedules. Management is considering various costing system options. To facilitate their decision-making they have requested the preparation of these schedules. The data and the schedules are in the Excel file on Blackboard. B. Short answers to 2 questions from management. Your answers to each of the questions should be complete, but also should be clear and concise, and in a form that is easy for managers to read and understand. You may use bullet points if they assist in the presentation of your answers. Answers must be written in acceptable Business English. C. A discussion based on (i) Schedule 6 (ABC), (ii) the facts of the case (iii) and an article from the literature. **The full requirements of the assignment are on pages 5 and 6** The Company: Sneakers Unlimited Sneakers Unlimited is a manufacturer of men's and women's leisure shoes. What began as a small family business has now grown into a firm with a large and very popular product range. The firm produces three lines in the range - Dance, Run and Walk. The firm's products have a relatively short lifetime and often styles popular in one period are out of fashion by the next period. Costing system currently used The firm accounts for its products using standard absorption job costing, applying overhead to products using a plant-wide rate based on direct labour hours. Materials purchases are recorded at actual cost. Work in process and finished goods inventories are recorded at standard cost. All cost variances in the period are transferred to cost of goods sold at the end of the period. This existing costing system was established when only one type of sneakers was produced and when overhead was a significantly smaller proportion of total cost than it is now. Materials for sneakers are fabric, cottons, and depending on the styles, zippers, buttons, Velcro, laces and logos. At the present time the only material which is traced directly to the product is fabric. Cottons, buttons, zippers, laces, Velcro and logos are treated as indirect materials (overhead). All on-costs such as superannuation, payroll tax, provisions for leave on wages are treated as indirect costs (overhead). Standards are determined at the point when the product is initially designed, and are regularly updated to reflect all changes in the manufacturing process. The firm prides itself on its very efficient labour force, and in the past the company seldom had cost over-runs due to labour inefficiency. Furthermore, to promote efficiency, the firm uses a bonus system based on annual profits. In the period which is the focus of this review, two changes were made in the manufacturing process. First, a change was made in the layout of the manufacturing space which should reduce the standard time per product in the long run. The direct labour standards have not yet been updated to include this change. Secondly, a new type of material was purchased from the current supplier. The supplier had claimed that the new material (which is of high quality, yet cost a similar price to the old material) is easier to work with in terms of both cutting and sewing. The supplier is known to be reliable in terms of material supply. AYB225 - Report Page 3 Review of costing system The firm is commencing an extensive review of its costing system and reporting practices. This review will involve all divisions of the firm. Your task is only a small part of the entire review: One product only (\"Dance\" sneakers), and Selected tasks only - for example, a discussion on implementing Activity Based Costing Some of the issues which have prompted the review are: Various pressures from competitors have alerted the firm to be more cost conscious. The firm has had a number of issues with their product pricing recently. The firm sets prices based on a mark-up on total cost (i.e. inventoriable (product) cost + period cost for each product). Various inconsistencies have been identified when comparing Sneakers Unlimited prices with those of its major competitors. As a result, the firm wants to have more confidence in the product cost generated by the accounting system, in particular the cost of each individual product (Dance, Run and Walk). In addition to product pricing, the costing data provided by the firm is now being used extensively for other decision-making within the firm. This means that the accuracy of costing data is becoming increasingly important. In recent times the firm has been faced with limited storage space at their premises. The firm is unable, at this stage, to consider options involving a significant capital outlay to relocate or rebuild. Further, Just in Time inventory is not an option for this firm. (Note: the recent change in factory layout has no impact in any way on storage capacity.) The requirements of the case begin on the next page. In addition to the tasks management have asked you to complete, they have also indicated that there are a few issues that you are not to include: Ways of reducing costs - You are to take all costs as given, and at this stage not recommend any particular ways of attempting to reduce or eliminate costs. Use all data as given, including all the fixed overhead figures without trying to fine-tune those figures in any way. Management has extensively reviewed the budgeting process of the firm and is happy that all budgets have been appropriately set. Therefore you should not include any issues related to the firm's budgeting process. Management has stressed that your responses to the questions should be based only on the facts with which you have been provided. Do not go outside these facts. The information you provide will be combined with information relating to the other lines (\"Run\" and \"Walk\") for further analysis. This additional analysis and any issues which may result from combining the information are outside the scope of this case. AYB225 - Report Page 4 Requirements of the Assignment PART A: Completed Schedules in Excel (10 Marks) Schedules (5 marks) Complete Excel schedules in the appropriate spaces on pro-formas. Do not delete any parts of the proforma. Show all required working in the relevant spaces, and only show required working. Variance report related to units produced. (Schedule 1) Calculation of standard costing cost variances relating to the product \"Dance\". The firm calculates price variances for all manufacturing costs, efficiency variances for all variable manufacturing costs, and a fixed overhead volume variance. The material price variance is calculated on usage. (Schedule 2) The Normal Absorption Costing Income Statement for the period, with relevant variances expensed to the period and; recast the Income Statement (with relevant variances expensed to the period) assuming Standard Absorption Costing. (Schedules 3 and 4, and a schedule to calculate COGS) Show the journal entries for the period under the standard absorption costing system currently used by the firm. Journal entries must be presented in proper form. (Schedule 5) Prepare a comparison of overhead costs per product under the existing costing system (a plantwide rate based on DL hours) with an Activity Based Costing system using the cost pools and cost drivers identified in the data. (Schedule 6 and workings for Schedule 6) ENTERING YOUR RESULTS INTO BLACKBOARD AND CHECKING OF FIGURES Figures in the schedules (1, 2, 3, 4, 5 and 6) can be entered and checked on Blackboard up to 5 times per student. It will indicate if your answer is correct or not. Zero mark will indicate that the answer is wrong. Excel (5 Marks) If you want to be eligible for up to 5 marks, then use formulas in your calculations. The formulas will have to refer to other cells in the worksheet that either contain the original values, e.g. direct labour cost per hour, machine hours per unit, order times etc. or to formulas from a previous step. This means, every time the values in the data sheet are changed, the results are updated automatically. To be clear, all worksheets are to be completed using formulas that draw values from the data provided. In each worksheet, students will be deducted marks for each cell that contains a numerical value that has not been obtained using an Excel formula. You must use an \"if\" statement to enter the label \"U\" or \"F\" in the column next to the relevant variance value (see the example in the worksheet). AYB225 - Report Page 5 PART B: Answers to management questions (pro-forma provided) (8 Marks) (1) Management is keen to have more confidence in its product costs. One way to achieve this would be to implement Activity Based Costing (as you will discuss in Part C). There are other improvements that this firm could make to the existing standard absorption job costing system that may provide product costs in which management can have more confidence (costs which would be less subjective). Identify (2 Mark) and explain (2 Mark) ONE improvement that can be made to the standard absorption job costing system based on the facts provided in the case. (2) Refer to the variances in Part A, Schedule 2 that you have calculated. Select ONLY ONE (1) variance to follow up, i.e. the one that you think needs the most urgent attention (1 Mark)? Explain why you would choose that particular variance to follow up in the light of the facts provided by the case (2 Marks). Furthermore, in the light of the facts provided by the case, what would be the most likely reason that this particular variance has occurred (root cause) (1 Mark)? Do not use any citations in your answers to Part B PART C: Discussion (pro-forma provided) (12 Marks) Based on your analysis in Schedule 6 and the facts of the case, discuss why you would or would not recommend ABC. Also, provide further evidence for your discussion by referring to at least one (maximum three) article(s) from the literature (use the Internet or QUT's database). Articles from professional or scientific journals/magazines that report on costing system (e.g. ABC) experiences will serve this purpose well. Textbooks such as Horngren et al. (2014) do not qualify as an article. Use the AREA critical thinking framework document on Blackboard under Lecture 2 to structure your discussion. Your discussion should be no longer than two pages (1.5 line spacing). Research and referencing Use in-text citations in your discussion in Part C. Under references, give the source of the evidence, i.e. name of the author, year, title, and name of source. Please adhere to the APA or Harvard referencing style. Your entire assignment must be written in your own words. Students should note that severe penalties are imposed for plagiarism. If you are uncertain about what constitutes plagiarism, refer to the School of Accountancy Blackboard site. Errors and omissions: If you believe there is an error in the assignment, or that an essential instruction has been omitted, please contact the Unit Coordinator. Format and presentation of assignment All students are required to follow the standardised format for their assignment. You must include the items and setting out described below, and only the items requested. Do not add anything, e.g. do not add headers/footers, do not add borders, pictures, extra pages. The assignment must be completed using pro-formas in Excel and Word (see Blackboard) and comply with all instructions on all assignment documents provided. Length of assignment AYB225 - Report Page 6 Answers to management questions and the discussion (Pro-forma Part B & C) must be no longer than three (3) single-sided pages, 1.5 line spacing, font 12. NOTE: The markers will only mark the first 3 pages; any more pages will be ignored. Hints to maximise your assignment mark General comments Make sure that you: Answer the question asked, and only the question asked. Provide complete answers. Be concise Do not include unnecessary padding Use pro-forma Part B&C on Blackboard to structure your responses Do not go over the page limits: B = one page only and C = two pages only Make sure you check your file once uploaded that it is readable. No marks will be given for submissions where the file is corrupt and unreadable. This unit uses the Turnitin software to detect plagiarism. Turnitin compares submissions to other submissions, both current and past, and sources that are available online such as textbooks, scholarly articles, websites and blogs. Comments regarding: Part A: Excel There is plenty of help on Blackboard/Internet regarding the use of Excel formulas. Make sure your Excel model is completely dynamic. This means updating any of the values in the 'Data for current period' worksheet will update the data in all the other worksheets automatically. The formulas do not always have to refer back to the 'Data for current period' sheet but may refer to a cell in a previous worksheet. This will make your formulas more readable and therefore less error-prone. Part B: Question 1 - costing system improvement Do not suggest an alternative costing system such as variable costing, Activity Based Costing, actual costing etc. There are ways to refine a standard absorption job costing system. Part B: Question 2 -variance to follow up Do not provide generic reasons from the textbook/lecture. You should link or base your reasons on the facts of the case. Part C: Discussion. Be convincing and strengthen your argument using evidence from the business (or scientific) press. The evidence should be as specific as possible and integrated into your discussion. For example, you could find a case that describes the benefits of implementing a certain costing system in a company. Conversely, you could use a case that documents some of the problems of a costing system, which would then help you to argue against it. Standard textbook descriptions of advantages/disadvantages of certain costing systems will not be as valuable. AYB225 - Report Page 7 AYB225 Management Accounting Assessment Item 2: Report (Assessment Criteria Sheet) Grade 7 6 Weighting: 30% 5 4