Athena Bicycle Sales: Break-Even and Profit Analysis

11/26/2023 c0faf44f1ef82e39d99c6afb9fe6dfba811b62e0.xlsx Page 1 Documentation Athena Cycles Author Date Purpose To perform a break-even analysis of Athena I bicycle sales and to determine the product mix that optimizes company profits among four Athena bicycle models

Scenario Summary Current Values: Status Quo Changing Cells: Units_Sold 3,000 3,000 Sales_Price_per_Unit $1,400 $1,400 Salaries_and_Benefits $ 616,500 $ 616,500 Shipping_and_Distribution 180,000 180,000 Stocking_and_Storage 150,000 150,000 Miscellaneous 210,000 210,000 Result Cells: Total_Revenue 4,200,000 4,200,000 Total_Expenses 4,006,500 4,006,500 $B$27 $ 193,500 $ 193,500 Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray.

Expanded Operations Reduced Operations Reduced Pricing 3,500 2,250 3,800 $1,400 $1,400 $1,300 $ 755,000 $ 575,000 $ 616,500 235,000 150,000 190,000 200,000 125,000 160,000 225,000 180,000 230,000 4,900,000 3,150,000 4,940,000 4,740,000 3,167,500 4,806,500 $ 160,000 $ (17,500) $ 133,500

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Scenario PivotTable Row Labels Total_Revenue Total_Expenses Net_Income Expanded Operations $4,900,000 $4,740,000 $160,000 Reduced Operations $3,150,000 $3,167,500 ($17,500) Reduced Pricing $4,940,000 $4,806,500 $133,500 Status Quo $4,200,000 $4,006,500 $193,500 $0 $1,000,000 $2,000,000 $3,000,000 $4,000,000 $5,000,000 $6,000,000 ($50,000) $0 $50,000 $100,000 $150,000 $200,000 $250,000 Total_Revenue Total_Expenses Net_Income

c0faf44f1ef82e39d99c6afb9fe6dfba811b62e0.xlsx Income 11/26/2023 Athena Cycles Income Analysis for the Athena I Bicycle Break-Even Analysis Net Income Analysis Revenue Units Sold Revenue Expenses Net Income Sales Price Units Sold 3,000 $ 3,000 $ 4,200,000 $ 4,006,500 $ 193,500 Unit Sold $1,000 $1,200 $1,400 $1,600 $1,800 Sales Price per Unit $1,400 1,000 1,400,000 2,106,500 (706,500) 1,000 (1,106,500) (906,500) (706,500) (506,500) (306,500) Total Revenue $ 4,200,000 1,500 2,100,000 2,581,500 (481,500) 1,500 (1,081,500) (781,500) (481,500) (181,500) 118,500 2,000 2,800,000 3,056,500 (256,500) 2,000 (1,056,500) (656,500) (256,500) 143,500 543,500 Variable Expenses 2,500 3,500,000 3,531,500 (31,500) 2,500 (1,031,500) (531,500) (31,500) 468,500 968,500 Units Produced 3,000 3,000 4,200,000 4,006,500 193,500 3,000 (1,006,500) (406,500) 193,500 793,500 1,393,500 Material Cost per Unit $740 3,500 4,900,000 4,481,500 418,500 3,500 (981,500) (281,500) 418,500 1,118,500 1,818,500 Total Material Cost 2,220,000 4,000 5,600,000 4,956,500 643,500 4,000 (956,500) (156,500) 643,500 1,443,500 2,243,500 Manufacturing Cost per Unit $210 4,500 6,300,000 5,431,500 868,500 4,500 (931,500) (31,500) 868,500 1,768,500 2,668,500 Total Manufacturing Cost 630,000 5,000 7,000,000 5,906,500 1,093,500 5,000 (906,500) 93,500 1,093,500 2,093,500 3,093,500 Total Variable Expenses $ 2,850,000 Fixed Expenses Salaries and Benefits $ 616,500 Shipping and Distribution 180,000 Stocking and Storage 150,000 Miscellaneous 210,000 Total Fixed Expenses $ 1,156,500 Summary Total Revenue 4,200,000 Total Expenses 4,006,500 Net Income $ 193,500 $500 $1000 $1500 $2000 $2500 $3000 $3500 $4000 $4500 $5000 $5500 $- $1000000 $2000000 $3000000 $4000000 $5000000 $6000000 $7000000 $8000000 Revenue Expenses 500 1,000 1,500 2,000 2,500 3,000 3,500 4,000 4,500 5,000 5,500 (1,500,000) (1,000,000) (500,000) 0 500,000 1,000,000 1,500,000 2,000,000 2,500,000 3,000,000 3,500,000 $1,000 $1,200 $1,400 $1,600 $1,800

c0faf44f1ef82e39d99c6afb9fe6dfba811b62e0.xlsx Product Mix 11/26/2023 Athena Cycles Product Mix Analysis Parts Required for Each Unit Product Mix Analysis Athena I Athena II Athena III Athena Racer Part Athena I Athena II Athena III Athena Racer Sales Price per Unit $1,400 $1,800 $2,200 $3,000 Carbon Frame 1 1 0 0 Material Cost per Unit $740 $934 $1,388 $2,004 UltraLite Carbon Frame 0 0 1 1 Manufacturing Cost per Unit $210 $378 $488 $648 Ultraforce Shifters 1 0 0 0 Ultraforce II Shifters 0 1 0 0 Even Product Mix (Units) 3,000 3,000 3,000 3,000 High Response Shifters 0 0 1 1 Optimal Product (Units) 2,809 3,381 2,422 3,388 Front Derailleur X2 1 1 1 0 Change (191) 381 (578) 388 Front Derailleur X3 0 0 0 1 Rear Derailleur X2 1 1 1 0 Revenue (Even Mix) $ 4,200,000 $ 5,400,000 $ 6,600,000 $ 9,000,000 Rear Derailleur X3 0 0 0 1 Revenue (Optimal Mix) 3,932,600 6,085,800 5,328,400 10,164,000 Standard Crank Arm 1 1 1 0 Change (267,400) 685,800 (1,271,600) 1,164,000 Extended Crank Arm 0 0 0 1 LightTouch Pedals 1 1 0 0 Summary Even Mix Optimal Mix Difference % Difference SpeedRacer Pedals 0 0 1 1 Units Produced and Sold 12,000 12,000 0 0.00% FirmControl Brakes 1 1 1 0 Total Revenue $ 25,200,000 $ 25,510,800 310,800 1.23% Sensor Brakes 0 0 0 1 Total Material Cost 15,198,000 15,387,802 189,802 1.25% Standard Wheelset 2 2 2 2 Total Manufacturing Cost 5,172,000 5,245,268 73,268 1.42% Chain Ring Kit 1 1 1 1 Total Fixed Expenses 3,470,000 3,470,000 0 0.00% Casset Kit 1 1 1 1 Net Income $ 1,360,000 $ 1,407,730 47,730 3.51% Drivetrain Kit 1 1 1 1 Maximum Net Income Parts Available 3.51% Part Available Used Remaining 4 Carbon Frame 6,413 6,190 223 1 UltraLite Carbon Frame 6,312 5,810 502 1 Ultraforce Shifters 2,809 2,809 0 1 Ultraforce II Shifters 3,381 3,381 0 1 High Response Shifters 6,304 5,810 494 32767 Front Derailleur X2 9,681 8,612 1,069 0 Front Derailleur X3 3,388 3,388 0 Rear Derailleur X2 9,831 8,612 1,219 Minimum Material Cost Rear Derailleur X3 3,722 3,388 334 15,387,802 Standard Crank Arm 10,204 8,612 1,592 4 Extended Crank Arm 3,500 3,388 112 1 LightTouch Pedals 9,000 6,190 2,810 1 SpeedRacer Pedals 7,800 5,810 1,990 1 FirmControl Brakes 10,311 8,612 1,699 1 Sensor Brakes 3,417 3,388 29 32767 Standard Wheelset 25,108 24,000 1,108 0 Chain Ring Kit 14,688 12,000 2,688 Casset Kit 13,409 12,000 1,409 Drivetrain Kit 13,944 12,000 1,944

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Microsoft Excel 16.0 Answer Report Worksheet: [NP_EX_8-1_NaifBinkassim.xlsx]Product Mix Report Created: 8/1/2021 12:38:16 AM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Solver Options Objective Cell (Max) Cell Name Original Value Final Value $E$24 Net Income % Difference 0.00% 3.51% Variable Cells Cell Name Original Value Final Value $B$11:$E$11 $B$11 Optimal Product (Units) Athena I 3,000 2,809 $C$11 Optimal Product (Units) Athena II 3,000 3,381 $D$11 Optimal Product (Units) Athena III 3,000 2,422 $E$11 Optimal Product (Units) Athena Racer 3,000 3,388 Constraints Cell Name Cell Value Formula $C$19 Units Produced and Sold Optimal Mix 12,000 $C$19=12000 $J$28:$J$46 >= 0 $J$28 Carbon Frame Remaining 223 $J$28>=0 $J$29 UltraLite Carbon Frame Remaining 502 $J$29>=0 $J$30 Ultraforce Shifters Remaining 0 $J$30>=0 $J$31 Ultraforce II Shifters Remaining 0 $J$31>=0 $J$32 High Response Shifters Remaining 494 $J$32>=0 $J$33 Front Derailleur X2 Remaining 1,069 $J$33>=0 $J$34 Front Derailleur X3 Remaining 0 $J$34>=0 $J$35 Rear Derailleur X2 Remaining 1,219 $J$35>=0 $J$36 Rear Derailleur X3 Remaining 334 $J$36>=0 $J$37 Standard Crank Arm Remaining 1,592 $J$37>=0 $J$38 Extended Crank Arm Remaining 112 $J$38>=0 $J$39 LightTouch Pedals Remaining 2,810 $J$39>=0 $J$40 SpeedRacer Pedals Remaining 1,990 $J$40>=0 $J$41 FirmControl Brakes Remaining 1,699 $J$41>=0 $J$42 Sensor Brakes Remaining 29 $J$42>=0 $J$43 Standard Wheelset Remaining 1,108 $J$43>=0 $J$44 Chain Ring Kit Remaining 2,688 $J$44>=0 $J$45 Casset Kit Remaining 1,409 $J$45>=0 $J$46 Drivetrain Kit Remaining 1,944 $J$46>=0

$B$11:$E$11 >= 2000 $B$11 Optimal Product (Units) Athena I 2,809 $B$11>=2000 $C$11 Optimal Product (Units) Athena II 3,381 $C$11>=2000 $D$11 Optimal Product (Units) Athena III 2,422 $D$11>=2000 $E$11 Optimal Product (Units) Athena Racer 3,388 $E$11>=2000 $B$11:$E$11=Integer

Integer Integer Integer Integer Integer Status Slack Binding 0 Not Binding 223 Not Binding 502 Binding 0 Binding 0 Not Binding 494 Not Binding 1,069 Binding 0 Not Binding 1,219 Not Binding 334 Not Binding 1,592 Not Binding 112 Not Binding 2,810 Not Binding 1,990 Not Binding 1,699 Not Binding 29 Not Binding 1,108 Not Binding 2,688 Not Binding 1,409 Not Binding 1,944

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Not Binding 809 Not Binding 1,381 Not Binding 422 Not Binding 1,388

Athena Cycles Terms and Definitions Term Definition Cost-Volume-Profit Analysis Revenue Variable Expenses Fixed Expenses Expenses that must be paid regardless of sales volume Break-Even Point The point where total revenue equals total expenses Product Mix Optimal Product Mix A branch of financial analysis that studies the relationship between expenses, sales volume, and profitability The total income generated from sales of a product or service Expenses that change in proportion to the volume of production The combination of different products offered by the company to the consumer The product mix that maximizes profits subject to constraints on production and consumer choice

NP_EX_8_Athena_

Related Documents