Hi Chegg , could you please help me to solve the missing sheets .. some of your expert person solved it but i forgot provid him 2 sheets. His name Nik_Aga
THANK YOU IN ADVANCE
1. sheet Margin of Safety
2. sheet Purchase of Laser
did you mean this 2 sheets ?!
You are eager to demonstrate to your boss that she had made a good decision in hiring you. You decide to prepare your analysis by using excel, therefore your first task was to create an excel file and naming it "Azad Inc. Cost Volume Profit Analysis". Into the first excel sheet you enter the relevant data. Putting the names of the income statement accounts in one cell and the values of that account in an adjacent cell. (See the attached excel file) You begin with sales. Next is the section called the Cost of Goods Sold, which has beginning finished good + the cost of goods manufactured minus the ending finished goods. You are told that Azad Inc. has a production policy to maintain the same level of finished goods inventory throughout the year as well as the work in process inventory on the shop floor. Management can do this because most of the company's production is geared to customer orders. With this information about inventory levels remaining the same from the beginning of the year until the end, you are then able to focus on the three categories of manufacturing costs (Direct Materials, Direct Labor and Manufacturing Overhead). You recall that in managerial accounting a cost that is "direct" is a cost that can be measured exactly, therefore you rightly conclude that both Direct Labor of 3,000,600 and the 4,209,000 of Direct material are both variable costs. However, Manufacturing Overhead are all product costs that are not Direct material nor Direct labor, therefore you must analyze each of manufacturing overhead costs to determine if they are variable or fixed. After talking with production workers you are able to determine that the Variable Overhead Manufacturing costs for 2016 totaled 1,264,000 and the Fixed manufacturing overhead costs were 1,325,670. The selling expenses and the administration expenses have both fixed costs and variable costs. The variable selling costs consists of sales commissions and travelling costs of 390,00 and fixed selling costs of advertising rent salaries and affine denaristian However, Manufacturing Overhead are all product costs that are not Direct material nor Direct labor, therefore you must analyze each of manufacturing overhead costs to determine if they are variable or fixed. After talking with production workers you are able to determine that the Variable Overhead Manufacturing costs for 2016 totaled 1,264,000 and the Fixed manufacturing overhead costs were 1,325,670. The selling expenses and the administration expenses have both fixed costs and variable costs. The variable selling costs consists of sales commissions and travelling costs of 390,00 and fixed selling costs of advertising, rent, salaries and office depreciation totaled 423,000. The administration costs can also be divided into variable costs of 198,700 and fixed costs of 212,800. You have entered the above data into the first excel sheet that you renamed "relevant data". You can now proceed to create a second excel sheet named CVP Statement and begin by centering Azad Inc. CVP Income Statement for the Year ending 2016 Required: 1) Complete the CVP statement and calculate the variable cost ratio and the contribution margin ratio. Part 2 Fatima has noticed you working hard on the assignment and mentions that she is impressed. However, the owner of the company Mr. Azad has stated to her that although the company was profitable last year, he would like to know what was the amount of sales that the company needed to breakeven last year - because he wanted to know what was Azad Inc.'s margin of safety ratio. He also wanted to know what amount of sales would be necessary in 2017 to provide for a twenty percent increase in profits. Required - because he wanted to know what was Azad Inc.'s margin of safety ratio. He also wanted to know what amount of sales would be necessary in 2017 to provide for a twenty percent increase in profits. Required A) On the second sheet calculate the margin of safety ratio for Mr. Axad. B) Calculate the amounts of sales needed to achieve a 20% increase in profits. Part 3 The production manager, Mr. Awah met with Fatima to talk about the purchase of a laser cutting machine. The machine would cost 1,200,000 and would increase production efficiencies over the next three years. The new machine would require less Direct labor. He estimated that direct labor costs could be reduced by 800,000. Fatima pointed out to him that the fixed manufacturing overhead costs would increase by 400,000. Required: 1) Calculate a new variable cost percentage and contribution margin percentage if the company purchases the laser cutting machine. 2) What would be the breakeven sales be in 2017? 3) What would be the level of sales to attain the target profit that the president is looking for? 4) If the sales target is achieved what would the margin of safety ratio be at the end of 2017? Fatima has asked you to provide her with a complete written report of your analysis. Your excel files should be printed and placed at the back of your report and each headed as Appendix 1, then Appendix 2 and finally Appendix 3. Then in the body of the report you simply reference the Appendix. You must submit your excel files, a soft copy of your report and a hard copy of your report. written report of your analysis. Your excel files should be printed and placed at the back of your report and each headed as Appendix 1, then Appendix 2 and finally Appendix 3. Then in the body of the report you simply reference the Appendix. You must submit your excel files, a soft copy of your report and a hard copy of your report. Note: Your report should be addressed to Fatima. You should identify each issue that you analyzed, provide a summary of your calculations and your recommendation regarding each issue. APPENDIX 1 Sales 12,500,000 Cost of Goods Sold Direct Material. 4,209,000 Direct Labour 3,000,200 Manufacturing Overhead 2.589,670 9,798,870 2,701,130 Gross Profit Operating Expenses Selling Expenses 813,000 General Administration Expenses 411.500 Net Income 1.476,630 You are eager to demonstrate to your boss that she had made a good decision in hiring you. You decide to prepare your analysis by using excel, therefore your first task was to create an excel file and naming it "Azad Inc. Cost Volume Profit Analysis". Into the first excel sheet you enter the relevant data. Putting the names of the income statement accounts in one cell and the values of that account in an adjacent cell. (See the attached excel file) You begin with sales. Next is the section called the Cost of Goods Sold, which has beginning finished good + the cost of goods manufactured minus the ending finished goods. You are told that Azad Inc. has a production policy to maintain the same level of finished goods inventory throughout the year as well as the work in process inventory on the shop floor. Management can do this because most of the company's production is geared to customer orders. With this information about inventory levels remaining the same from the beginning of the year until the end, you are then able to focus on the three categories of manufacturing costs (Direct Materials, Direct Labor and Manufacturing Overhead). You recall that in managerial accounting a cost that is "direct" is a cost that can be measured exactly, therefore you rightly conclude that both Direct Labor of 3,000,600 and the 4,209,000 of Direct material are both variable costs. However, Manufacturing Overhead are all product costs that are not Direct material nor Direct labor, therefore you must analyze each of manufacturing overhead costs to determine if they are variable or fixed. After talking with production workers you are able to determine that the Variable Overhead Manufacturing costs for 2016 totaled 1,264,000 and the Fixed manufacturing overhead costs were 1,325,670. The selling expenses and the administration expenses have both fixed costs and variable costs. The variable selling costs consists of sales commissions and travelling costs of 390,00 and fixed selling costs of advertising rent salaries and office deneristian Azad Manufacturing Inc. Rubric Number Du Evaluation Points 1-2-3-4-5 Introduction Performance Element/Criteria productior Addresses the report to Fatima Purpose: Describes the problem, identifies the issue or questions to be answered, completely covers the problem requirements Quantitative 1-2-3-4-5 - Organization: Proceed logically though the development of quantitative analysis. Separate worksheets. No hard coded values Data Sheet l: Named properly: CVP Income Statement, dated in Total Var costs - 9,061,900: total fixed 1.961.470 & Con Margin 3,438,100 Points Data Sheet 2: All data properly defined; Organized in an easy manner to follow. No hard-coded values. - BE 2016 - 7,131,373; MSR 42.9% sales needed in 2017 - 13.573,725 1-2-3-4-5 1-2-3-4-5 1-2-3-4-5 Data Sheer 3: All data properly defined: Organized in an easy manner to follow. No hard-coded values. . CMR = 33.9%; BE = 6,965,002: Sales for TNI - 12.191.271; MSR = 42.9% 1-2-3-4-5 1-2-3-4-5 Qualitative Analysis 1-2-3 1-2-3 1-2-3-4-5 Cover page Presents a table of content Present an executive summary. Provides an quick Overview of the CVP Margin of Safety and purchase of laser. Summarizes the results of CVP. Referring to the statement as an appendix. Summarizes the results of Breakeven analysis leading to the development of the Margin of safety - both total and ratio. Referring to the statement as an appendix. Summarizes the results of the purchase of the laser. The change in variable costs and fixed costs - therefore a new cost structure Referring to the new Breakeven and TNI and margin of safety total and ratio statement as an appendix 1-2-3-4-5 1-2-3-4-5 1-2-3-4-5 Conclusions) 1-2-3-4-5 1-2-3-4-5 Concluding remarks to the president Suggest an action plan for Mr Azad regarding the laser Concludes with overall recommendation (s) marks Professionalism Well organized; Spell checked: Direct and concise. 1-2-3-4-5 1-2-3 1-2 S marks TOTAL Additional commentsotes: Total Points [Type here) Relevant Data CVP Income Statement Margin of Safety Purchase of laser Sales Direct Material Direct Labor Manufacturing Overhead Variable Manufacturing Overhead Fixed Selling Expenses Variable Selling Expenses Fixed General Administration Expenses Variable General Administration Expenses Fixed Net Income Desired profit increase Cost of laser Labor savings Depreciation expense 12.500,000 4,209,000 3.000,200 1,264,000 1.325,670 390,000 423,000 198,700 212.800 1.476,630 20% 1.200,000 800,000 400.000 Brerakeven sales for 2016 Contribution margin ratio Fixed costs Breakeven =FC/CMR Margin of Safety in 2016 Sales in 2016 BE point Margin Safety Margin Safety Ratio - MS/Sales Sales required to achieve Target Net Income in 2017 Profit in 2016 Increase of 20% in 2017 Sales needed = FC+TNI/CMR Relevant Data CVP Income Statement Margin of Safety Purchase of laser OBS Azad Matung M V FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW POWER QUERY PROTECTED VIEW Be careful--this file may have come from an unsafe source Unless you need to edit, it's safer to stay in Protected View H18 1 for Enable Editing G H New Cost Structure Sales Variable Costs Reduce 800,000 Contribution Margin Fixed Costs increase 400,000 Profit Breakeven in sales 2017 - FC/CMR Sales needed to meet target Net income FC+TNI/CMR Margin of Safety in 2017 Sales in 2017-Breakeven in 2016 Margin of safety ratio Margin of safety/sales CVP Income Statement Margin of Safety Purchars of laser D Brerakeven sales for 2017 Contribution margin ratio Fixed costs Breakeven =FC/CMR Margin of Safety in 2017 Sales in 2017 BE point Margin Safety Margin Safety Ratio - MS/Sales Sales required to achieve Target Net Income in 2017 Profit in 2017 Increase of 20% in 2017 Sales needed = FC+TNI/CMR levant Data CVP Income Statement Margin of Safety Purchase of laser New Cost Structure Sales Variable Costs Reduce 800,000 Contribution Margin Fixed Costs increase 400,000 Profit Breakeven in sales 2017 - FC/CMR Sales needed to meet target Net income FC+TNI/CMR Margin of Safety in 2017 Sales in 2017-Breakeven in 2017 Margin of safety ratio Margin of safety/sales
