use excel templates. CVP Analysis. problems from book are on docx

CVP ANALYSIS(PROBLEMS 6-25 &7-37) Problem 6-25 Jonathon Macintosh is a highly successful Pennsylvania orchardman who has formed his own company to produce and package applesauce. Apples can be stored for several months in cold storage, so applesauce production is relatively uniform throughout the year. The recently hired controller for the firm is about to apply the high low method in estimating the company's energy cost behavior. The following cost were incurred during the past 12 months: Month Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec Pints of Applesauce Produced 35,000 21,000 22,000 24,000 30,000 32,000 40,000 30,000 30,000 28,000 41,000 39,000 Energy Cost $ 23,400 $ 22,100 $ 22,000 $ 22,450 $ 22,000 $ 23,350 $ 28,000 $ 22,800 $ 23,000 $ 22,700 $ 24,100 $ 24,950 Required for prob: Use the high-low method to estimate the company's energy cost behavior and express it in equation form. 2). Predict the energy cost for a month in which 26,000 pints of applesauce are produced. 3) What peculiarity is apparent from the scatter diagram? What should the cost analyst do? Problem 7-37 * THE BREAK-EVEN POINT IS 32,000 UNITS* Houston-based Advanced Electronics manufactures audio speakers for desktop computers. The following data relate to the period just ended when the company produced and sold 42,000 speaker sets. Sales Variable Cost Fixed Cost $3,360,000 $ 840,000 $2,280,00 Management is considering relocating its' manufacturing facilities to Northern Mexico to reduce cost. Variable cost are expected to average $18 per set; annual fixed cost are anticipated to be $ 1,984,000( in the requirements, ignoring income tax). Required: Required: 1) Calculate the company's current income and determine the level of dollar sales needed to double that figure, assuming that manufacturing operations remain in U.S. 2) Determine the break-even point in speaker sets if operations are shifted to Mexico. 3) Assume management desires to achieve the Mexican break-even point, however operations will remain in U.S. a. If variable cost remain constant, what must management do to fixed cost? By how much fixed cost change? b. If fixed cost remained constant, what must management do to the variable cost per unit? By how much unit variable cost change 4) Determine the impact( increase, decrease, or no effect) of the following operating changes. a. Effect of an increase in direct material cost on the break-even point. b. Effect of an increase in fixed administrative cost on the unit contribution margin c. Effect of an increase in the unit contribution margin on net income d. Effect of decrease in the number of units sold on the break-even point. Populate the cells highlighted in Green Req 1 Variable cost per pint of applesauce produced = Cost equation: Req 2 Populate the cells highlighted in green 1. Current income: Sales revenue.............................. Less: Variable costs..................... Fixed costs......................... Net income.................................. Required sales = (fixed costs + target net profit) unit contribution margin Populate the cells highlighted in green Req 2 1 Break-even point = fixed costs unit contribution margin Req 3 (a) (b) Populate the cells highlighted in green Req 4 (a) (b) (c) (d) Look closely at the textbook for guidance on answering these questions