Can someone help me create the proper equation for selling price family? im working on the Electric Car Financial Analysis
mily sedans sold in a year: the market momentum effect, the cost of gasoline, and the new charger locations. arket Momentum Effect-A 2 percent increase is expected each year. You can hard-code each year. Gas Price Effect--If gas prices are expected to rise, sports car and sedan selling prices wil ncrease by 3 percent in the year. Otherwise, there will be no effect on selling prices. New Charger Location Effect--If the number of new charger locations exceeds 25, sports a sedan selling prices will increase by 1 percent in the year. If there are 25 or fewer new cha locations, there will be no effect on selling prices, Total Change-The total expected percentage change in sports car and family sedan selling prices is the sum of the market, gas price, and charger location effects Selling Price-Sport-The selling price in a year is a function of the prior year's price and the total percentage change expected. For example, in 2017 the selling price was $75,000. If a 5 percent total change was expected, the 2018 selling price would be 1.05 * $75,000, or $78,78 Selling Price-FamilyNo family sedans were sold in 2017, so there was no 2017 selling price Management thinks 835,000 should be the selling price in 2018, plus or minus the expected change. Thus, if n 5 percent increase was expected in 2015, the selling price would be 105 x $35.000, or $36,750. In succeeding years, the price will be a function of the price in the prior year and the total percentage chante expected Unit.Cost-Short-The unit cost of production in a year is a function of the prior year's cost an 1. Factor, which is a value from the Inputs section. For example, the unit cost in 2017 was 878,000. If a 2 percent decrease was expected in 2018, the unit cost would be 98 x $78.000. Or $76,440, Um Cost-Family-No family sedans were made for sale in 2017 Management estimates a 8.30.000 unit cost in 2015. minus the effect of any cost reduction factor. Thus, if a 2 percent decrease was expected in 2018, the unit enst would be on, or $29.400 In surowing years, the unit cet will be a function of the oust in the prior year and the cost reduction facit espeted in the year me and Cash Flow Statements Home Insert Page Layout Formulas Data Review View Help - Arial 10 ' ' 2 Wrap Text Percentage Cut Copy 3 Format Painter hipboard BIU A E Merge & Center $ % 9 Conditional Format as Ce Formatting Table Style Styles Font 5 Alignment Number B c E A Llectric Car Company 2018 2017 NA NA ONSTANTS AX RATE MINIMUM CASH NEEDED TO START YEAR PROJECTED CAPITAL COSTS RESEARCH AND DEVELOPMENT COSTS FIXED COSTS COST OF OPERATING A CHARGER STATION INTEREST RATE ON DEBT OWED $ $ S NA NA NA NA NA 2019 20% 20% 1,000,000,000 5 1,000,000,000 $ 2,000,000,000 $2,000,000,000 $ 500,000,000 $ 500,000,000 $ 500,000,000 $ 500,000,000 $ 100.000 $ 100,000 $ 2% 2% 20,000 35,000.00 1,000,000.00 $ 990,000.00 2020 20% 1,000,000,000 2,000,000,000 500,000,000 500,000,000 100,000 296 $ $ S 980, 100,00 2017 NA NA NA NA 2011 U 0.01 0.03 30 7 2012 U 0.01 0.03 30 7 2020 U 0.01 0.00 30 7 4 5 INPUTS 16 COST OF GASOLINE (DSU) 17 GUARANTY CLAIM RATE (X 18 UNIT COST REDUCTION FACTOR 19 NUMBER OF NEW CHARGER LOCATIONS 20 UNITS - MARKET MOMENTUM EFFECT% PTS) 21 22 SUMMARY OF KEY RESULTS 23 NET HICOM NETER TANES 24 END OF THE YEAR CASH ON HAND 25 END OF THE YEAR DEBT OWED 20 21 CALCULATIONS 2. CHARGER LOCATIONS 29 CHCE SOLDI POLITIS) 30 MARKET MOMENTUM EFFECT 31 CAS PRICE EFFECT 2 JEW CHAROPRACATIO EFFECT 2018 2017 NA NA NA S 5 2019 470 877 7605 477 739,3665 6.870.877.760 $4,748.617.126 S 2,100 000.000 5 1.800.000.0005 2020 304 300.099 2.452 917 225 1.200.000.000 2010 2019 2020 COD 630 560 690 NA NA NA NA mily sedans sold in a year: the market momentum effect, the cost of gasoline, and the new charger locations. arket Momentum Effect-A 2 percent increase is expected each year. You can hard-code each year. Gas Price Effect--If gas prices are expected to rise, sports car and sedan selling prices wil ncrease by 3 percent in the year. Otherwise, there will be no effect on selling prices. New Charger Location Effect--If the number of new charger locations exceeds 25, sports a sedan selling prices will increase by 1 percent in the year. If there are 25 or fewer new cha locations, there will be no effect on selling prices, Total Change-The total expected percentage change in sports car and family sedan selling prices is the sum of the market, gas price, and charger location effects Selling Price-Sport-The selling price in a year is a function of the prior year's price and the total percentage change expected. For example, in 2017 the selling price was $75,000. If a 5 percent total change was expected, the 2018 selling price would be 1.05 * $75,000, or $78,78 Selling Price-FamilyNo family sedans were sold in 2017, so there was no 2017 selling price Management thinks 835,000 should be the selling price in 2018, plus or minus the expected change. Thus, if n 5 percent increase was expected in 2015, the selling price would be 105 x $35.000, or $36,750. In succeeding years, the price will be a function of the price in the prior year and the total percentage chante expected Unit.Cost-Short-The unit cost of production in a year is a function of the prior year's cost an 1. Factor, which is a value from the Inputs section. For example, the unit cost in 2017 was 878,000. If a 2 percent decrease was expected in 2018, the unit cost would be 98 x $78.000. Or $76,440, Um Cost-Family-No family sedans were made for sale in 2017 Management estimates a 8.30.000 unit cost in 2015. minus the effect of any cost reduction factor. Thus, if a 2 percent decrease was expected in 2018, the unit enst would be on, or $29.400 In surowing years, the unit cet will be a function of the oust in the prior year and the cost reduction facit espeted in the year me and Cash Flow Statements Home Insert Page Layout Formulas Data Review View Help - Arial 10 ' ' 2 Wrap Text Percentage Cut Copy 3 Format Painter hipboard BIU A E Merge & Center $ % 9 Conditional Format as Ce Formatting Table Style Styles Font 5 Alignment Number B c E A Llectric Car Company 2018 2017 NA NA ONSTANTS AX RATE MINIMUM CASH NEEDED TO START YEAR PROJECTED CAPITAL COSTS RESEARCH AND DEVELOPMENT COSTS FIXED COSTS COST OF OPERATING A CHARGER STATION INTEREST RATE ON DEBT OWED $ $ S NA NA NA NA NA 2019 20% 20% 1,000,000,000 5 1,000,000,000 $ 2,000,000,000 $2,000,000,000 $ 500,000,000 $ 500,000,000 $ 500,000,000 $ 500,000,000 $ 100.000 $ 100,000 $ 2% 2% 20,000 35,000.00 1,000,000.00 $ 990,000.00 2020 20% 1,000,000,000 2,000,000,000 500,000,000 500,000,000 100,000 296 $ $ S 980, 100,00 2017 NA NA NA NA 2011 U 0.01 0.03 30 7 2012 U 0.01 0.03 30 7 2020 U 0.01 0.00 30 7 4 5 INPUTS 16 COST OF GASOLINE (DSU) 17 GUARANTY CLAIM RATE (X 18 UNIT COST REDUCTION FACTOR 19 NUMBER OF NEW CHARGER LOCATIONS 20 UNITS - MARKET MOMENTUM EFFECT% PTS) 21 22 SUMMARY OF KEY RESULTS 23 NET HICOM NETER TANES 24 END OF THE YEAR CASH ON HAND 25 END OF THE YEAR DEBT OWED 20 21 CALCULATIONS 2. CHARGER LOCATIONS 29 CHCE SOLDI POLITIS) 30 MARKET MOMENTUM EFFECT 31 CAS PRICE EFFECT 2 JEW CHAROPRACATIO EFFECT 2018 2017 NA NA NA S 5 2019 470 877 7605 477 739,3665 6.870.877.760 $4,748.617.126 S 2,100 000.000 5 1.800.000.0005 2020 304 300.099 2.452 917 225 1.200.000.000 2010 2019 2020 COD 630 560 690 NA NA NA NA
