Question
Instructions: In the Excel spreadsheet keep the problem data separate in total and per unit basis in one section of the spreadsheet. Then link the
Instructions: In the Excel spreadsheet keep the problem data separate in total and per unit basis in one section of the spreadsheet. Then link the data using Excel formulas to answer questions 1 to 5. Then change the data section as per question 6. If properly done, then the new answers to question 1 to 5 will be automatically computed. You dont need to recreate again. Print both original answers question 1 to 5 and the sensitivity output modified as per question 6 (new answers to question 1 to 5). For grading. DO NOT email the EXCEL Spreadsheet.
Microsoft Excel Modelling Assignment. The following data relate to Feather Friends Inc. PROBLEM 4-18 Cost--Volume-Profit Analysis; Degree of Operating Leverage [LO1, LO3, LO4, LO5, LO8] Feather Friends Inc. distributes a high-quality wooden birdhouse that sells for $20 per unit. Variable expenses are $8 per unit, and fixed expenses total $180,000 per year. Its operating results for last year were as follows: Required: Answer the following independent questions: I. What is the product's CM ratio? 2. Use the CM ratio to determine the break-even point in sales dollars. 3. Assume this year's total sales increase by $75,000. If the fixed expenses do not change, how much will operating income increase? 4. Assuming that the operating results for last year were as above: a. Compute the degree of operating leverage based on last year's sales. b. The president expects sales to increase by 20% next year, Using the degree of operating leverage from last year, what percentage increase in operating income will the company realize this year? Calculate the dollar increase in operating income. 5. The sales manager is convinced that a 10% reduction in the selling price, combined with a $30,000 increase in advertising, would increase this year's unit sales by 25%. If the sales manager is right, what would be this year's operating income if his ideas are implemented? Do you recommend implementing the sales manager's suggestions? Why? Q6: For the upcoming year, Feather Friends Inc. in anticipating increasing the selling price to $25 per unit instead of $20 per unit. How would this affect questions 1 to 5 above. All other data remains the same. Part 2 (10 marks) ABC Company Ltd. costs products using normal costing. For last year the following data are available. Q1: Overhead is applied based on direct labor hours (DLH) Required (5 Marks) Q1.1 What was the pre-determined overhead rate? Q1.2 What was the applied overhead for last year? Q1.3 Was the overhead over- or underapplied, and by how much? Q1.4 What was the normal cost per unit produced? (to four significant digits). Q2: Overhead is applied based on machine hours (MH) Required (5 Marks) Q2.1 What was the pre-determined overhead rate? Q2.2 What was the applied overhead for last year? Q2.3 Was the overhead over- or underapplied, and by how much? Q2.4 What was the normal cost per unit produced? (to four significant digits). Microsoft Excel Modelling Assignment. The following data relate to Feather Friends Inc. PROBLEM 4-18 Cost--Volume-Profit Analysis; Degree of Operating Leverage [LO1, LO3, LO4, LO5, LO8] Feather Friends Inc. distributes a high-quality wooden birdhouse that sells for $20 per unit. Variable expenses are $8 per unit, and fixed expenses total $180,000 per year. Its operating results for last year were as follows: Required: Answer the following independent questions: I. What is the product's CM ratio? 2. Use the CM ratio to determine the break-even point in sales dollars. 3. Assume this year's total sales increase by $75,000. If the fixed expenses do not change, how much will operating income increase? 4. Assuming that the operating results for last year were as above: a. Compute the degree of operating leverage based on last year's sales. b. The president expects sales to increase by 20% next year, Using the degree of operating leverage from last year, what percentage increase in operating income will the company realize this year? Calculate the dollar increase in operating income. 5. The sales manager is convinced that a 10% reduction in the selling price, combined with a $30,000 increase in advertising, would increase this year's unit sales by 25%. If the sales manager is right, what would be this year's operating income if his ideas are implemented? Do you recommend implementing the sales manager's suggestions? Why? Q6: For the upcoming year, Feather Friends Inc. in anticipating increasing the selling price to $25 per unit instead of $20 per unit. How would this affect questions 1 to 5 above. All other data remains the same. Part 2 (10 marks) ABC Company Ltd. costs products using normal costing. For last year the following data are available. Q1: Overhead is applied based on direct labor hours (DLH) Required (5 Marks) Q1.1 What was the pre-determined overhead rate? Q1.2 What was the applied overhead for last year? Q1.3 Was the overhead over- or underapplied, and by how much? Q1.4 What was the normal cost per unit produced? (to four significant digits). Q2: Overhead is applied based on machine hours (MH) Required (5 Marks) Q2.1 What was the pre-determined overhead rate? Q2.2 What was the applied overhead for last year? Q2.3 Was the overhead over- or underapplied, and by how much? Q2.4 What was the normal cost per unit produced? (to four significant digits)Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started