Not sure if there is a way to send a spreadsheet that I am struggling to do?
I will post the pictures but if there is a way to send the xls please let me know.
4. For each unit level of sales. enter the total sales dollars and total costs. The chart at right will be plotted as you enter the amounts. After all points are plotted. grab and move the labels provided at the left to identify each area. Units Sales $ Costs $ 0 3,000 Cost-Volume-Profit Chart Costs 6,000 -Sales 9.000 12,000 $1 15,000 18,000 21,000 24,000 $1 27,000 $1 Operating Loss Break-Even Area Point $1 SO Sales and Costs Operating Profit Area S 0 3000 6000 9000 12000 15000 18000 21000 24000 27000 Units 5. Marain of estate-EXCEL 3 Contribution Margin, Break-Even Sales, Cost-Volume-Proft Chart, Margin of Safety, and Operating Leverage Youngsville Industries Inc. expects to maintain the same inventories at the end of 20Y5 as at the beginning of the year. The total of all production costs for the year is therefore assumed to be equal to the cost of goods sold. With this in mind, the various department heads were asked to submit estimates of the costs for their departments during the year. A summary report of these estimates is as follows Estimated Fixed Estimated Variable Cost Cost (per unit sold) Production Costs Direct Materials $48 Direct Labor $38 Factory Overhead $150,000 $25 Selling Expenses: Sales salaries and commissions $125,000 $10 Advertising $50,000 Travel $15,000 Miscellaneous selling expense $10,000 $2 Administrative Expenses: Offce and offoers' salaries $140,000 Supplies $10,000 Miscellaneous administrative expense $10,000 $5 $2 Total $510,000 $130 It is expected that 22,175 units will be sold at a price of $170 a unit. Maximum sales within the relevant range are 27,500 units. INSTRUCTIONS: Using formulas/ functions wherever possible, complete the following on the Input Tab 1) Prepare an estimated income statement for 20Y5. 2) What is the expected contribution margin ratio? 3) Determine the break-even sales in units and dollars. 4) Construct a cost-volume- proft chart indicating the break-even sales. 5) What is the expected margin of safety in dollars and as a peroentage of sales? 6) Determine the operating leverage.Excel 3 Name : Using formulas/functions wherever possible, complete the following: Wake Tech Industries Inc. Estimated Income Statemer For the Year Ended December 31, 20Y2 Cost of goods sold: Direct labor Factory overhead Cost of goods sold Gross proft Expenses: Selling expenses: Sales salaries and commissions Advertising Travel Miscellaneous selling expense amoral selling expenses Administrative expenses: Office and officers' salaries Supplies Miscellaneous administrative expense Total expenses Total administrative expenses Operating income Contribution margin ratio: Units * Unit Variable Cost variable costs Contribution margin Sales Contribution margin ratio Break-even sales : Fixed costs Unit contribution margin Sale Price - Unit Variable Cost Break-even sales (units Sale price Break-even sales ( dollars) . For each unit level of sales, enter the total sales dollars and total costs. The chart at right will be plotted as you enter the amounts. After all points are plotted, grab and move the labels provided at the left to identify each area. Units Sales $ Costs $ Cost-Volume-Profit Chart -Costs --Sales 12.090 15,000 18,000 21,000 24,000 $1 27,000 $1 Operating Loss Break-Even "Point $1 Sales and Costs Profit Area 3000 6000 9000 12000 15000 18000 21000 24000 27000 - $ Units Margin of safety: Sale Price X Units xpected sales Margin of safety (i Expected sales Margin of safety (as a percentage of sales) Operating leverage: Contribution margin Unit CM S * Units Operating income Operating leverage
