Like Magic Company

Like Magic Company. See attached file for full problem description.

LIKE MAGIC COMPANY
Four years ago, Tom Turner developed a new type of cream to improve skin appearance. After a few weeks of tests, his wife, Tracy, and her friends declared that the cream worked like magic. Keeping the formula a closely guarded secret, they started Like Magic to manufacture this new type of skin cream.
They gave up their jobs and put all of their financial assets into the business. Tom was responsible for development and manufacturing, while Tracy took charge of marketing and finance. Tom converted the garage into production area and a development center. The family room became the storage, packing and shipping areas. Tracy ran the sales from the kitchen.
Sales started with friends and grew by word of mouth. Tracy convinced a few local retailers to carry Like Magic. As sales increased, products, boxes, papers, etc. took over their home. Every room became work space. Expansion was necessary, so they took out a second mortgage on their home, rented space and began adding staff. Business kept growing and so did the need for more people, inventory, equipment, and space.
As cash started to run short, Tom and Tracy turned to Rodney Rukeyser and his investment group for additional financing. Like Magic received additional capital in exchange for expanded ownership and the agreement to sell shares in the company to the public the following year.
Over the past four years, the company has achieved a considerable measure of success. Sales have increased very nicely and Like Magic is an important segment of a growing market. Shares of their stock are owned by the public and are actively traded. The market price of their stock is widely reported in the financial press.
Like Magic is planning on significant expansion for next year. They are negotiating to purchase a small company producing another skin care cream. They are also in discussion to buy the rights to produce another cream from a European company. Like Magic is also planning to go global, with Canada the likely first target, then Mexico, Europe and Asia. All of this will require purchase of new buildings and equipment, which in turn will necessitate new financing.
YOU have just been hired as the controller of Like Magic. The company officers want you to provide financial information and analysis for the past year and the coming year. They would like to see data, discussion, and recommendations evaluating performance, helping to improve financial control, and supporting decisions on future directions for the company.
An executive committee meeting has just been called to start planning for the coming year. A few quick phone calls reveal that Fawn Fonda, Vice President of Marketing , plans to come to the meeting with a schedule of various selling price and volume combinations. Anthony Iococca, Vice President of Manufacturing, will have some requests for expansion of plant and equipment. Fidel Fernandez, Director of Purchasing, will try to find some cheaper sources of supply for materials and other production costs, or at least what the going market prices will be next year. Finally, Willie Wang, Director of Research and Development, isn't sure that he will have cost estimates on the planned new products.
You decide to develop a spreadsheet model on your PC. With this model you will be able to answer many "what if" questions and help the committee narrow choices of action for next year. Hopefully, this will reduce the length of the meeting and reduce the number of follow-up meetings.
You have the following data:
Selling Price per unit $32.00 Other Production Cost $1.95
Material Cost per unit 6.00 Sales Incentive Cost 2.00
Labor cost 8.00 Fixed Mfg. Cost $4,324
Fixed Marketing Cost 5,116 Administrative Cost 1,805
Units sold* 0 200 400 600 800 1,000 1,200 1,400 1,600
*to be used in a table for graphing
PART A REQUIREMENTS

1. Compute the per unit total variable cost, and the per unit contribution margin. Format to 2 decimal places.
2. Compute total yearly fixed expenses
3. Show each per unit figure as a percent of selling price, as well.
4. Compute the selling price, each variable cost, total variable cost and contribution margin as a percent of sales to one decimal place
5. Show formulae for breakeven sales in units and for breakeven sales in dollars.
6. Complete a Cost-Volume-Profit Relationship Table
7. Create an XY Cost-Volume-Profit Graph
Tracy wants to start planning for next year right now. She asks you to estimate how much the company can earn next year. She wants the first projection to keep the selling price at the average of last year, $32.00. Fawn says that at that price we should be able to sell 1,375,000 units. The staff supplies their best estimates of costs at that volume. These figures appear below.
Tom wants to see projected income when the selling price is $30.00 and again at $35.00. Fawn wants to maintain a price of $32.00 and keep volume high by spending more on incentives. Tony proposes automation changes that would reduce labor costs requiring fewer people with higher skills. His proposal would increase fixed overhead at a volume of 1,375,000 units. Fidel says that with the new equipment he can switch suppliers for savings in indirect production costs. As a result of the discussion, five more sets of projections are developed and shown below.

Past Year Projections

Unit Sales 1100 1375 1540 1210 1540 1375
Selling Price per unit $32 $32 $30 $34 $32 $32
Material Cost per unit 6 6.30 6.30 6.30 6.30 6.30
Labor Cost 8 8.40 8.40 8.40 8.40 4
Other Prod. Cost 1.95 2.05 2.05 2.05 2.05 1.30
Sales Incentives 2.00 2.10 2.10 2.10 3.00 2.10
Fixed Mfg Cost 4,324 4,500 4,500 4,500 4,500 11,000
Marketing Cost 5,116 6,200 6,200 6,200 6,200 6,200
Administrative Cost 1,805 2,100 2,100 2,100 2,100 2,100

REQUIREMENTS PART B

1. Calculate Contribution Margin and the Contribution margin ration for the past year as well as the five projected scenarios.
2. What are the total fixed costs for the past year and the five scenarios?
3. Calculate Breakeven sales in dollars and targeted net income for the past year and the five scenarios.
4. Given that automation would decrease variable labor cost by $6, and increase fixed manufacturing costs by $8,000,000, would operating leverage be higher or lower before or after automation?
5. Compare and comment on the target net income and breakeven sales under each set of projections. Which projection should the company set as its target, and why?