REQUIRED ANALYSIS:

1. Use Excel to model the revenues, costs, and profitability of this company by creating a pro-forma income statement over four years (base-year = 2020). The goal is to design a decision aid that will help evaluate the companys profitability (if any) and the impact of changes in price structure, market assumptions, etc.
2. Conduct a break-even analysis to help your friend understand how close, in terms of units sold, this firm is to making or losing money. You will need to compute a weighted average price for the three markets (school, municipalities, web-sales). You may ignore shipping costs and revenues for this part of the analysis.
3. In analyzing the ACMEs profitability, consider the following:
   1. Your report should use both pictures and numbers to describe the outcome of you analysis. For example:
      1. A stacked bar chart should help convey how each market (school, municipality, and consumer) contributes to overall revenue in each year.
      2. A column chart, showing projected income over four years, should accompany your projected net income figures.
      3. A tabular exhibit should convey revenues from the two product lines by year.
      4. Two tabular exhibit should convey Break-Even quantity and projected unit sales, by year, for the two product lines.
   2. An analysis of profitability should not exist in a vacuum of knowledge about costs. Analyze company cost structure with the intent of determining the major components of company cost, as a foundation for making a recommendation on where to focus cost cutting efforts. Also, there is the question of whether the cost structure is stable over time or is some component of cost likely to grow out of control? To support this analysis, create two PIE graphs which shows projected total direct labor, material, administrative, and overhead charges in 2020 and 2023.
   3. Assumptions of any model should be challenged. For example, what happens if your competitor is able to match your percentage price decrease? What if sales grow at a slower pace than projected (or even decline) HINT: Save your spreadsheet under a different name before you begin the what-if analysis; i.e. you will have two versions of the spreadsheet, one with the initial assumptions and one for the what-if analysis.
   4. Are there assumptions that are suspect? Are there assumptions that concern you? Your report should delineate and discuss any concerns in this regard.

$0.45 75% $0.29 38% 4 Assumptions Unit Sales Model A Model B 2.5" galvanized steel tube Vinyl coated chain 122 110 S hooks 8 Child seat Standard seats Hinge clamps 318 inch stainless-steel clai Stainless lock nuts Chain hinges Manifold casting Manifold screw 18 18 Unit Price 2.5" galvanized steel tubelli Vinyl coated chairlinear foo Shook Hinge clamps Chain hinges Stainless bolts Lock nuts Manifold castings Manifold screws Standard seats Child seat Wages/hour (benefits) Wages/hour (respectively) FTE packerstyr Manufacturing and packagii Cutting oil, work area cleanu Production supervisontyr (pa Production assitance $0.13 $65.50 $0.72 $0.19 $0.06 $9.52 $0.08 $4.50 $7.50 $24.00 $18.00 Model A Municipalities and school district (ACME and compe ACME's market share School and municipality (ACME's competitor) Foreign manufactures and sundry Traditional advertising media Market share Traditional adv initiatives (log base) Web-page (units) sales School (market) Municipalities (market) Swing sets (via the web) Shipping $645 25% $1,800 140 3.26 350 2 2800 3400 $699.00 $55 15 $8,500 $8.50 $55,000 $14.00 Model B Web-page (units) sales Swing set shipping 525 4% $499 Accountant and Receptionist Salary (accountant) $51,000 Salary (receptionist) $25,000 Temporary consultants $20,000 Salary (owner) $90,000 Salaries estimated 17% Insurance $15,000 Insurance unit sold $5.00 Schools $10,000 Municipalities $15,000 Increase the advertisingly $2,000 Social media ads $2,000 Growing social media ads! 5% Total sales (with fixed cor $28,000 Other selling and adminis! 1.10% Product Swing Model A) Swing (Model B) Fabricator Time Packer Time 2.25 1.75 0.75 5% Fabricator (per hr) Paker (per hr) $24 $18 Municipalities (discount) School distric (discount) grow in 2021 Other years ACME (decreses in 2021-2023) Accountant (competitor) Cost of materials fixed component & insurance Labor cost 6% 4% 1.50% 1.20% 2% 3% 2.50% Sales engineer Salary Person generates All sales $53,000 Taxable income Interest payment (debt) Depreciation per year New equipment Hourly Worker assumption per year (week) 25% $15,000 $8,000 10% 47 $0.45 75% $0.29 38% 4 Assumptions Unit Sales Model A Model B 2.5" galvanized steel tube Vinyl coated chain 122 110 S hooks 8 Child seat Standard seats Hinge clamps 318 inch stainless-steel clai Stainless lock nuts Chain hinges Manifold casting Manifold screw 18 18 Unit Price 2.5" galvanized steel tubelli Vinyl coated chairlinear foo Shook Hinge clamps Chain hinges Stainless bolts Lock nuts Manifold castings Manifold screws Standard seats Child seat Wages/hour (benefits) Wages/hour (respectively) FTE packerstyr Manufacturing and packagii Cutting oil, work area cleanu Production supervisontyr (pa Production assitance $0.13 $65.50 $0.72 $0.19 $0.06 $9.52 $0.08 $4.50 $7.50 $24.00 $18.00 Model A Municipalities and school district (ACME and compe ACME's market share School and municipality (ACME's competitor) Foreign manufactures and sundry Traditional advertising media Market share Traditional adv initiatives (log base) Web-page (units) sales School (market) Municipalities (market) Swing sets (via the web) Shipping $645 25% $1,800 140 3.26 350 2 2800 3400 $699.00 $55 15 $8,500 $8.50 $55,000 $14.00 Model B Web-page (units) sales Swing set shipping 525 4% $499 Accountant and Receptionist Salary (accountant) $51,000 Salary (receptionist) $25,000 Temporary consultants $20,000 Salary (owner) $90,000 Salaries estimated 17% Insurance $15,000 Insurance unit sold $5.00 Schools $10,000 Municipalities $15,000 Increase the advertisingly $2,000 Social media ads $2,000 Growing social media ads! 5% Total sales (with fixed cor $28,000 Other selling and adminis! 1.10% Product Swing Model A) Swing (Model B) Fabricator Time Packer Time 2.25 1.75 0.75 5% Fabricator (per hr) Paker (per hr) $24 $18 Municipalities (discount) School distric (discount) grow in 2021 Other years ACME (decreses in 2021-2023) Accountant (competitor) Cost of materials fixed component & insurance Labor cost 6% 4% 1.50% 1.20% 2% 3% 2.50% Sales engineer Salary Person generates All sales $53,000 Taxable income Interest payment (debt) Depreciation per year New equipment Hourly Worker assumption per year (week) 25% $15,000 $8,000 10% 47