Will tip generously to anyone who can finish this by 10pm tonight EST. Questions 1-4 need to be completed via excel with correct formulas included in the excel blocks.

Barbie Company (Break-Even Analysis and Leverage) After he received his M.A. in chemistry, specializing in plastics, Mickey Mahon joined the plastics division of a major chemical firm. His wife, Leslie, had managed the toy department of Shield's, a large department store in Chicago, before their marriage in 1960. As a hobby, Mrs. Mahon designed and Mr. Mahon produced certain toy items that they gave to their friends on Christmas, birthdays, and other occasions. These toys were very well received, and a number of the Mahons' friends asked to buy additional ones that they, in turn, could use as gifts. Mrs. Mahon's successor in Shield's toy department also urged them to produce additional quantities to be marketed through the store. In the summer of 1966, the Mahons decided to devote their full time to the commercial production of toys, and on January 1, 1967, the Barbie Company commenced operations. The initial plans were well laid. Sales during the first year totaled $1 million, and by 1977 they had grown to $10.5 million. The annual sales for the firm's first 11 years, together with certain other operating statistics, are presented in Table 1. Total toy industry sales are quite stable, but because of fads and fashions, individual firms experience considerably more instability than does the industry as a whole. The Barbie Company, for example, "missed the market" in 1972 and 1975, when its new designs were not especially well received, and sales dropped significantly during both those years. Sales instability presents a financial planning problem in the toy industry, and this problem is heightened by the seasonal nature of the business. About 80 percent of all sales are made during the months of September and October, when stores are stocking up for the Christmas season, but collections are not generally made until January and February, when stores have received their Christmas receipts and are able to meet their obligations to the toy manufacturers. Toy manufacturers have a choice of production techniques. They can either produce heavily during the April-to-September period in anticipation of the Christmas sales, or they can follow a practice of level production during the year, storing output produced during the off-season period. The advantages of uniform production are that fixed-asset requirements are reduced and better personnel can be obtained because of the full-time employment Seasonal production; on the other hand, reduces the danger of obsolescence due to style changes, decreases the storage problem, and reduces the need for financing to carry off-season inventories. The Barbie Company has been following a seasonal production pattern, pattern, producing about 70 percent of its output during the April-through-September period and 30 percent during the remainder of the year. 1 Although the company has been continuously profitable, costs have been getting out of hand in recent years. The main plant was built in 1970, and additional capacity has been provided for in various rented buildings in the west Chicago area. The lack of centrally located production facilities and the need to train new labor during the peak production period are considered to be the primary reasons for the disproportionate increase in cost and the declining profit margin on sales. Leslie Mahon is convinced that the firm should buy some land adjacent to the present plant, construct an automated and integrated production complex, and produce at a more uniform rate throughout the year. She also proposes to build a plant large enough to meet projected sales demand for some years into the future. Mickey Mahon, on the other hand, is worried about increasing fixed costs in a firm characterized by sales fluctuations. He believes that it would be sounder practice to slow down the firm's rate of expansion and consolidate its present position Mickey believes that his wife's approach, which would enable the firm to maintain its rapid growth and perhaps even make the family quite wealthy, would also jeopardize the continued existence of the firm. It is estimated that variable costs will amount to approximately 85 percent of sales during 1978 if the present production setup is continued. Fixed costs for 1978 under the existing setup will be about $1.2 million, and $485,000 of this will be depreciation. If Leslie Mahon's expansion proposal--which calls for expenditures of approximately $3 million for plant, equipment, and increased working capital, all to be financed by a 10-year loan from an insurance company--is carried out, variable costs will fall to approximately 75 percent of sales. At the same time fixed costs will rise to $2,200,000 per year. Depreciation in this case will be an estimated $1,400,000 per year. Economics of expansion dictate that the Mahons must take the step all at once if they are going to take it at all, since expansion in stages is too costly. If the expansion is not undertaken, Leslie Mahon believes that a larger profit margin can be restored by concentrating on cost control. Since 1971, sales have been increasing at about a 29 percent rate compounded annually. The Mahons do not expect sales to continue to grow at this rate, but they do anticipate that a 20 percent annual sales increase can be attained over the next several years if the $3 million expansion is undertaken. Without this expansion the Mahons agree that sales growth after 1977 will only be about 15 percent. 2 Table 1 Barbie Company OPERATING DATA.1967-1977 (IN THOUSANDS) 1967 Sales Leas variable costs: Cost of sales Selling sad Administrative Expenses Total variable costs Contribution to overhead and profits Less fixed operating costs: Rent Depreciation Taxes, property Total fixed Operating costs Earnings before interest and taxes Less Interest Earnings Before Taxes* Less income taxes** Profit After Taxes Leas dividends Addition to retained Earnings Net profits after taxes as a percentage of sales * = 5 % of sales. 1968 1969 1970 1971 1,000 1,250 1500 1950 770 50 962 63 1,155 75 820 1,025 180 1972 1973 1974 1975 . 1976 1977 2,000 3,600 4,800 $4,200 7,500 10,500 1,502 98 2350 _ 1,810 118 1,540 100 2.772 180 3,696 240 3,233 210 5,775 375 8,084 525 1,230 1,600 1,928 1,640 2,952 3,936 3,443 6,150 225 270 350 422 360 648 864 757 1,350 8,609 . 1,891 59 8 67 113 80 13 93 132 81 10 91 179 116 _19 135 215 131 16 147 275 152 18 170 190 55 179 32 266 382 96 216 61 373 491 148 301 90 539 218 241 388 116 745 605 437. 443 I80 1,060 831 5 108 38 70 40 30 5 127 48 79 40 39 13 166 67 99 40 59 11 204 84 120 40 80 16 259 111 148 40 108 16 174 70 104 40 64 24 358 158 200 40 160 21 470 212 258 40 218 22 196 80 116 40 76 31 574 262 312 40 272 40 791 366 425 40 385 7.0% 6.3% 6.6% 6.2% 6.3% 5.2% 5.5% 5.4% 2.8% 4.2% 4.0% Required 1, Calculate the break-even point in dollars for 1978, assuming that present production methods are continued. Also calculate estimated 1978 sales and express the break-even point as a percentage of 1978 sales. Under the expansion program the estimated 1978 sales are $12.6 million, and Barbie breaks even at a sales level of $8.8 million, or 70 percent of estimated sales. 2. Assuming that the expansion program is undertaken, what is the estimated EBIT (earnings before interest and taxes) for 1978? (Note: If the expansion program is not instituted, estimated 1978 earnings before interest and taxes would be $611,250). .At what level of sales would profits be equal under the two production methods? (Hint: Use the equation BE=Sales-(FC +VC+PROFIT ) where profit = zero. On one side of the equal sign substitute the formula for non-expansion; the other side of the equal sign substitute the same equation bu he formula for \"with expansion\". So you will have BE without expansion = BE with expansion. Next, you can eliminate Sales since sales will be the same mount. You can then express VC as a percent of sales and solve for sales). 3. Assuming that the expansion program is instituted, what would happen to before-tax earnings if 1978 sales level by about the same rate that sales fell in 1975? If the expansion program was not instituted, EBIT would be $170,250 if sales fell by this same percentage. 4. Assuming that depreciation is the only noncash charge, what is the cash break-even sales level for the non-expansion alternative? The cash break-even sales level for expansion is $3.2 million