You have been recently hired by the Azad Inc., a manufacturing company to work as a cost analyst in the accounting department. The head of the department has asked you to examine the income statement that was completed at the end of 2016 The income statement was compiled by the accounting department and audited by an outside professional auditor who verified that the statement was prepared according to the Generally Accepted Accounting Principles (GAAP).

Your supervisor, Fatimawants you for your first assignment, to reformat the income statement into a Cost Volume Profit (CVP) income statement. That is, examine all the costs to determine which cost would vary in total, in direct proportion to changes in sales activity and which would remain the same in total regardless of the amount of sales (within a relevant range of sales activity of about 30%). Then prepare the CVP income statement.

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.

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 coststo 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 totaled1,264,000and 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 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. 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

Gross Profit 2,701,130

Operating Expenses

Selling Expenses 813,000

General Administration Expenses 411,500

Net Income 1,476,630