There appears to be an error in % Profit Margin for all
years in the question.
% Profit Margin = Net Income (Loss) of the Current Year / Gross
Revenue
Due to this error - variables from X1 to X5 only are
calculated and rest X6 to X 10 can not be calculated due to absence
of right information
Due to availability of incorrect info rest of the calculation
and question/s is/are not done. However, out of 10 sub-answers (X1
To X10) required under A, 5 are answered.
The calculation and steps done in Excel Spreadsheet are shown in
picture.
.
ANSWERS SHOWING VARIOUS CALCULATIONS TO ARRIVE AT MISSING DATA X1 TO X10 REFER TO COMMENTS FOR STEPS AND CALCULATIONS IN RED FOR EACH MISSING DATA 30,000,000/0.6%= 5,000,000,000 1 X1 = Revenue /% Market Share = 2 X2 = % Revenue Per Product Survey for P3 This row gives productwise % revenue in total revene of $ 30 Million Refer Product P4 : Revenue for product P4 9,000,000 WHICH IS 30% OF 30% TOTAL REVENUE OF 30 MILLION % Revenue Per Product Survey for P4 Therefore, % Revenue Per Product Survey for P3 = Revenue of P2 10.5 Million/Total Revenue 30 Million = 3 Revenue 1st Year 30,000,000 P3 TOTAL 100% 35% 47 30% 80 X3 = Total Cost of Goods for year 1: This is now derived at by plugging in the X1 and X2 as calculated above Market Size 5,000,000,000 Market Share 0.6096 # Product P11 P2 % Revenue Per Product Survey for P3 - All products = 100% 15% 20% Average Sales Per Unit 90 80 = Revenue of Each Product / No of units sold Annually Number of units sold annually 50000 75000 Revenue Per Product 4,500,000 6,000,000 Gross Margin Per Product 50% 40% Total Cost of Goods in Year 1 = x3 = COST OF GOODS FOR EACH PRODUCT = (1 - Gross Margin Per 2,250,000 3,600,000 Product) X Revenue. Sum of Cost of Goods of Each Product = Total Cost of Goods % Annual Maintenance Annual Tax Rate Long Term Debt 225000 10,500,000 60% 112500 9,000,000 45% 30,000,000 50% 4,200,000 4,950,000 15,000,000 8% 526 REMAINING TABLE IS RECONSTRUCTED TO ARRIVE AT REMAINING VARIABLES 51 2.00% 20.00% 25.00% 5.00% TO YEARS Annual R Increase X10 = X4 = (Current Year Revenue - Previous Year Revenue) / Previous Year Revenue Annual E Increase Gross Revenue Manufacturing Cost =X3 / GROSS REVENUE X Current Year's Revenue Sales And Marketing 2.00961 30,600,000 4.00% 36,720,000 6.00% 45,900,000 5.00% 48.195.000 30,000,000 15,000,000 15,300,000 18,360,000 22,950,000 24,097,500 4,000,000 4,080,000 4,243,200 4,497,792 4,722,682 Salaries 600,200 612,000 636,480 674,669 708,402 = Gross Revenue - [(All Costs Except Salaries + Profit (Loss)] Depreciation Insurance Maintenance Utilities Administrative Fees Space Rental Interest Expenses = X6 = Gross Revenue - (All Costs of Current Year Except Interest + Profit (Loss) 5,000,000 400,000 2,000,000 300,000 180,000 2,000,000 5,000,000 408,000 2,040,000 306,000 183,600 2,040,000 5,000,000 424,320 2,080,000 318,240 190,944 2,121,600 5,000,000 449,779 2,120,000 337,334 202,401 2,248,896 5,000,000 472,268 2,100,000 354,201 212,521 2.361.341 897,400 716,425 526,401 326,877 Taxes on Income = X5 = Gross Revenue - [(All Costs of Current Year Except Tax + Profit (Loss)] 140,941 354,612 402,435 (377,600) (86,025) 2,677,874 6,737,640 Net Income (Loss) =GROSS REVENUE X % PROFIT MARGIN % Profit Margin = NET INCOME (LOSS) / GROSS REVENUE NOTE: IT APPEARS THAT THERE IS AN ERROR IN THE % ARRIVED IN QUESTION THIS NEEDS TO BE CORRECTED ROI -1.24% -0.28% 7.87% 17.20% 18.86% -1.51% -0.34% 10.71% 26.95% 30.59% CORRECT % PROFIT MARGIN = -1.26% -0.28% 7.29% TO BE GIVEN IN 14.68% QUESTION TO ARRIVE AT X6