ACC 3020 Excel Project #1 Fall 2018

You are a financial manager for Halcyon Crop. Consider the following information from its most recent fiscal year, and read the email from your supervisor. Per instructions in the email, determine the contribution margins of the Mountain, Southwest, and Pacific divisions. Prepare an incremental analysis for each division concerning the decision to keep or eliminate it. Prepare a new income statement assuming the divisions that should be eliminate are eliminated.

Halcyon Corporation Income Statement Fiscal Year Ended June 30, 201B - by Division Northeast Sales 20,000,000 Cost of goods sold 13,540,000 Gross profit 5,450,000 Selling, general, and administrative costs 4,070,000 Net income 2,390,000 Percentage of costs that are considered variable Northeast Cost of goods sold 80.0% Selling, general, and administrative costs 11.0% Schedule of allocated corporate costs Sales Northeast 20,000,000 Mid-Atlantic 18,000,000 Southeast 13,500,000 Midwest 14,500,000 Mountain 8,000,000 Southwest 12,500,000 Pacic 11,000,000 Total 97,500,000 Mid-Atlantic 18,000,000 11 ,790,000 5,210,000 3,570,000 2,540,000 Mid-Atlantic 82. 0% 9. 0% Pct. of total sales 20.51% 18.45% 13.85% 14.87% 8.21% 12.82% 11.28% 100.00% Southeast Midwest 13,500,000 14,500,000 8,980,000 9,780,000 4,520,000 4,720,000 3,080,000 2,730,000 1,440,000 1,990,000 Southeast Midwest 79.0% 78.0% 8.0% 11.0% Allocated corrate costs 515,300 553,800 415,500 445,100 245,300 384,500 338,400 3,000,000 All allocated costs are included in the "Selling, general, and administrative costs" line in the income statement, and are to be treated as unavoidable xed costs. Mountain 8,000,000 5,140,000 1,850,000 2,170,000 (310,000) Mountain 83.0% 9.0% Southwest Pacic Total 12,500,000 11,000,000 97,500,000 8,900,000 7,790,000 55,920,000 3,500,000 3,210,000 30,580,000 3,780,000 3,530,000 22,930,000 (180,000) (320,000) 7,550,000 Southwest Pacic 80.0% 80.0% 12.0% 11.0% From: Hector Alonso (ay6233@halcyon.com) Subject: FY1 8 Results Hev.- |:l [just got out of a meeting with the CFO going over our preliminary nal numbers for FYE 5!30!18. I swear I've never seen anyone looking at record sales and record prots be so furious. She's really worked up over the Mountain, Southwest, and Pacic divisions losing money (again), and she's about ready to burn down everything west of the Mississippi. She says we've been trying to get these things to turn a prot for a long time. and, quite frankly, she's right. Where she may not be right, though, is that increasing prots is as simple as shutting those divisions down. True enough, we get rid of all the variable costs for any division we close, and a lot of the xed costs, too. What we don't get rid of is those three million of costs that are allocated down from corporate based on relative sales weight. Those costs are xed and they aren't going anywhere. We close Pacic, and the other divisions just absorb that cost. So, here's what I need from you. For all our money-losing divisions, gure out what our contribution margins are. I've got from each division's accountants a rundown of how their expenses break down xedi'variable, and included it in the spreadsheet attached. From that, do an incremental analysis on what happens to company prot if we close the Mountain, Southwest, or Pacic divisions. Do each analysis separately. Finally, put together a proforma income statement, showing what our company's income would have been (by division and in total) if we closed one or more of the divisions. This should only reect us closing the divisions that would make us more protable based on your incremental analysis. Remember, the allocated corporate xed costs would have to be reallocated based on the new revenue numbers, and I've also attached the schedule of how we currently do it. When you have all that together, just write up a quick one or two paragraph summary of what you're recommending. Which division or divisions should be closed, and why. Also include any other factors we should consider or questions we should ask beyond just what's on the income statement before we set any plans in motion