Business Analysis000525874

Document Sample
Business Analysis000525874 Powered By Docstoc
					Coursework Header Sheet
155631-4


Course        STAT1024: Business Analysis (MBA)                   Course School/Level        BU/PG
Coursework    RESIT - Assignment                                  Assessment Weight         50.00%
Tutor         B TBA                                               Submission Deadline    12/08/2009



Coursework is receipted on the understanding that it is the student's own work and that it has not,
in whole or part, been presented elsewhere for assessment. Where material has been used from
other sources it has been properly acknowledged in accordance with the University's Regulations
regarding Cheating and Plagiarism.



000525874                                                 Neeraj Jaura
Tutor's comments




Grade
                            For Office Use Only__________                Final Grade_________
Awarded___________
Moderation required: yes/no Tutor______________________                  Date _______________
Introduction

The company’s various sales teams are targeting multiple consumer
groups in urban and sub urban areas on random basis. With its
remarkable success over its progress, the company is planning to extend
their coverage sales in potential areas to enhance the revenue. Company
rewards over its sales boost the sales teams to put in their best efforts
and enhances the employee morale in attaining the common objective of
growth for the entity. With due help from strategies for statistical and
analytical reading for the company’s future, the judgmental approach
tends to grow in relation to revenue which has to be correlated with
efforts of the sales teams.

Moreover sales business solely depends on the demand and preference of
a customer, matched with proper products but to achieve the high sales
target for the company, collective efforts of dedicated professionals is a
prerequisite. With regards to keep the teams going and make them
competitive, a good incentive structure is offered to retain the services of
professionals without letting them shift their mind in favor of competitors.
Rest according to the given data, there are some irregularities found in
the nature of variation amongst sales/revenue and commission, which can
further be elaborated and technically judged with the help of trend line
and charts precisely.



Part I- Situations

Problems


      What has happened to sales since 1998?
Most of the entities depend on quantity rather than quality to assist their
profit charts to rise and as in the given situation where in phone sales
team requires increased sales to grow their profitability which is precisely
depicted by the revenue chart where in trend line gives an incremental
approach with upcoming forecast about the coming years with regards to
previous years, but if we will look precisely at quarter 25 where in
revenue is at its lowest(£49837) carried over since quarter 17(£53332)
until its again came to its recovery after quarter 33(£54891).
Within these quarters in 10 years, the sales touched its high at quarter
43(£66326) and lowest at quarter 25(£49837) respectively and
maintained a range between £49000 and £67000, which has further the
potential to break the barrier of £70000 soon in the coming years. So
overall the revenue/sales is increasing since the last decade but with a
slow and steady pace with some minor steep curves where in it generated
its (R2 =0.2921) .


      Do sales follow same annual pattern as commissions?




As seen in the chart above where in both revenue and commissions are
plotted with their respective trend lines, we can definitely say that there
isn’t any correlation between the two. On a contrary, they both are
running with different dynamics in which revenue follows an upside trend
comparable to a constant approach in structure of commission
irrespective of revenue fluctuation which gives an idea about various
products to offer and different selling techniques in phone sales business.
If company continues with the same trend without putting in more
effective sales professional, then the future growth might not going to be
in favour of increasing commission despite of increasing revenue/sales
due to irrelevant links which we can see for instance in quarter(15-
17,39) where in decreasing revenue is increasing the commissions. Huge
changes in revenue in quarter 39 is not effecting the commission
structure to increase being revenue (£63547) in relation to earlier cases
where less revenue also result in the same kind of commission structure
as this.

      There was a change in the way commission was calculated.
       Estimate when that change occurred.


             Q
        Q     u                                           moving    Central   Variatio   Variati    %
       ua    ar                                             avg       avg      n in      ons in    com
       rte   te   Revenu    Commis    moving    Central   commis    Commis    Revenu     Commi     mis
        rs   rs     e        sion     avg rev   avg Rev     sion     sion        e        ssion    sion
        1    3    £58,839   £15,587                                                                26%
        2    4    £62,712   £16,554   £60,259             £16,896                                  26%
1999    3    1    £61,249   £17,787   £59,435   £59,847   £17,134   £17,015    £1,402     £773     29%
        4    2    £58,236   £17,654   £58,735   £59,085   £17,019   £17,076    -£849      £578     30%
        5    3    £55,543   £16,539   £58,377   £58,556   £17,019   £17,019   -£3,013    -£480     30%
        6    4    £59,910   £16,097   £58,500   £58,439   £16,880   £16,949    £1,471    -£852     27%
2000    7    1    £59,820   £17,784   £58,174   £58,337   £16,876   £16,878    £1,483     £906     30%
        8    2    £58,727   £17,100   £58,145   £58,160   £16,952   £16,914     £568      £187     29%
        9    3    £54,239   £16,521   £57,764   £57,954   £16,832   £16,892   -£3,715    -£371     30%
       10    4    £59,794   £16,401   £56,957   £57,360   £16,770   £16,801    £2,434    -£400     27%
2001   11    1    £58,294   £17,306   £56,644   £56,801   £16,656   £16,713    £1,494     £593     30%
       12    2    £55,502   £16,853   £56,123   £56,383   £16,631   £16,643    -£881      £210     30%
       13    3    £52,985   £16,064   £56,027   £56,075   £16,598   £16,615   -£3,090    -£551     30%
       14    4    £57,711   £16,300   £55,894   £55,960   £16,625   £16,612    £1,751    -£312     28%
2002   15    1    £57,908   £17,176   £55,980   £55,937   £16,791   £16,708    £1,971     £468     30%
       16    2    £54,970   £16,960   £55,748   £55,864   £16,587   £16,689    -£894      £271     31%
       17    3    £53,332   £16,728   £55,464   £55,606   £16,568   £16,578   -£2,274     £151     31%
                                                                                            -
       18    4    £56,781   £15,485   £55,332   £55,398   £16,586   £16,577    £1,383    £1,092    27%
2003   19    1    £56,773   £17,098   £54,799   £55,065   £16,518   £16,552    £1,708     £546     30%
       20    2    £54,441   £17,033   £54,195   £54,497   £16,527   £16,522     -£56      £511     31%
       21    3    £51,200   £16,456   £53,977   £54,086   £16,251   £16,389   -£2,886     £67      32%
       22    4    £54,364   £15,519   £53,623   £53,800   £16,011   £16,131     £564     -£612     29%
2004   23    1    £55,901   £15,995   £53,282   £53,452   £15,111   £15,561    £2,449     £434     29%
       24    2    £53,026   £16,075   £53,597   £53,439   £14,999   £15,055    -£413     £1,020    30%
                                                                                            -
       25    3    £49,837   £12,855   £53,637   £53,617   £14,437   £14,718   -£3,780    £1,863    26%
       26   4   £55,623   £15,070   £53,966   £53,801   £13,767   £14,102    £1,822    £968   27%
2005   27   1   £56,060   £13,748   £54,615   £54,291   £13,795   £13,781    £1,769    -£33   25%
       28   2   £54,345   £13,394   £55,232   £54,923   £13,656   £13,725    -£578    -£331   25%
       29   3   £52,432   £12,966   £55,781   £55,506   £13,851   £13,754   -£3,074   -£788   25%
       30   4   £58,089   £14,517   £56,442   £56,111   £14,045   £13,948    £1,978    £569   25%
2006   31   1   £58,257   £14,527   £57,057   £56,749   £14,167   £14,106    £1,508    £421   25%
       32   2   £56,989   £14,171   £57,636   £57,346   £14,333   £14,250    -£357     -£79   25%
       33   3   £54,891   £13,454   £58,381   £58,008   £14,418   £14,376   -£3,117   -£922   25%
       34   4   £60,407   £15,180   £58,951   £58,666   £14,591   £14,504    £1,741    £676   25%
2007   35   1   £61,235   £14,867   £59,623   £59,287   £14,865   £14,728    £1,948    £139   24%
       36   2   £59,270   £14,862   £60,237   £59,930   £14,938   £14,901    -£660     -£39   25%
       37   3   £57,581   £14,549   £60,815   £60,526   £15,129   £15,033   -£2,945   -£484   25%
       38   4   £62,863   £15,472   £61,482   £61,149   £15,164   £15,146    £1,715    £326   25%
2008   39   1   £63,547   £15,631   £62,023   £61,753   £15,293   £15,228    £1,795    £403   25%
       40   2   £61,936   £15,003   £62,676   £62,349   £15,590   £15,442    -£413    -£439   24%
       41   3   £59,747   £15,065   £63,370   £63,023   £15,738   £15,664   -£3,276   -£599   25%
       42   4   £65,472   £16,662             £63,476   £15,983   £15,860    £1,996    £802   25%
2009   43   1   £66,326   £16,220             £64,073             £15,682    £2,253    £423   24%
       44   2   £64,215   £15,986             £64,669             £15,815    -£453     £172   25%
       45   3   £62,148   £15,417             £65,265             £15,947   -£3,117   -£531   25%
       46   4   £67,547   £16,087             £65,861             £16,080    £1,685     £7    24%
2010   47   1   £68,295   £16,604             £66,457             £16,213    £1,838    £391   24%
       48   2   £66,640   £16,480             £67,054             £16,345    -£414     £135   25%
       49   3   £64,522   £15,943             £67,650             £16,478   -£3,127   -£535   25%
       50   4   £69,953   £16,695             £68,246             £16,611    £1,707    £85    24%
Figure 1 with Spurious Figure.



                                               Commission                                       R² = 0.1491
   £20,000


   £15,000


   £10,000                                                                            Commission
                                                                                      Linear (Commission)
    £5,000


         £0
              1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35 37 39 41 43 45 47 49


Figure 2 with assumed balanced figure.
                       Central avg Commission
  18000
                                                                         R² = 0.2007
  16000
  14000
  12000
  10000
                                                               Central avg Commission
   8000
   6000                                                        Linear (Central avg Commission)
   4000
   2000
      0
          1 3 5 7 9 1113151719212325272931333537394143454749


As the above excel file represents where in a sudden change was noticed
around quarter 35 where commission declined drastically against revenue
(£61235) and the spurious figure of commission was assumed to be
£14517 instead of the given £44517 in figure 2, which has given it a
positive sustainable slope in trend line otherwise if left the same, it would
have given a steep high trend that seems to be pretty impossible looking
to the trend of all years in commission/revenue chart (Figure 1).

To make it more precise, around quarter 27-29 central average revenue
clearly shows it to be in lowest downtrend (£13656) even with the
average revenue sales (£54345), which reflects the change in trend
clearly for calculating commissions.


      What was the effect of change on the size of commission?
To get a clear image over the effect in change on the size of commission,
we have taken both central average commission and central average
revenue together which depicts an upward trend for the revenue that
favours the company in generating huge profits but when talking about
the morale of the sales teams/professionals which are putting their
hardest efforts over in generating revenue/sales, it is depressing for them
not to get any part of profits even by putting the sales up in the form of
commissions, which can decrease the loyalty and interest of the
company’s employees leading to shifting to any other competitor or lesser
efforts.



Part II- Continuing your analysis
        Identify, formulate, and measure any relationships which exist and
         any time series patterns arising. Look at percentage commission
         and correlations. It is possible that some data items are spurious.
         Identify which they (probably) are and reconstruct better values
         using your analysis of the situation.




Figure 3 with changes




Figure 4. Without changes
As seen in the figure 3 where we have amended the spurious figure to get
the real data for time series pattern compared to untouched percentage
change chart (figure 4) in commission with pre existing spurious data that
doesn’t seems to be feasible. As a whole, both the charts above show a
downtrend despite of increasing revenue for the company, if we exclude
considering the spurious data which enhances the percentage commission
drastically to 77%.

Overall this situation is of loss in long run for the company because of
inconsistency in effort-reward relation with perspective to employees and
their loyalty towards company. Only quarter 21 enjoyed good percentage
commission of 32% in relation to the revenue of £51200 apart from which
all quarters are declined to the downturn.

     Graph the     trends   and   variations   for   sales   and   commission
      separately.
As shown in the above graphs, trend line of variation in revenue has
stability despite being volatile but variation in commission is going slightly
in downturn along with its volatility.

        Make sensible forecasts for the sales for the remaining three
         quarters of 2009 and all of 2010.       What additional information
         would you require in order to assess the validity of your forecasts?

  Year     Quarters Revenue       Commissions
              1           £58,839            £15,587
              2           £62,712            £16,554
  1999        3           £61,249            £17,787
              4           £58,236            £17,654
              5           £55,543            £16,539
              6           £60,010            £16,097
  2000        7           £59,820            £17,784
              8           £58,727            £17,100
              9           £54,339            £16,521
             10           £59,794            £16,401
  2001       11           £58,294            £17,306
             12           £55,502            £16,853
             13           £52,985            £16,064
             14           £57,711            £16,300
  2002       15           £57,908            £17,176
             16           £54,970            £16,960
             17           £53,332            £16,728
             18           £56,781            £15,485
  2003       19           £56,773            £17,098
             20           £54,441            £17,033
              21          £51,200           £16,456
              22          £54,464           £15,519
  2004        23          £55,901           £15,995
              24          £53,026           £16,075
              25          £49,837           £12,855
              26          £55,623           £15,070
  2005        27          £56,060           £13,748
              28          £54,345           £13,394
              29          £52,432           £12,966
              30          £58,089           £44,517
  2006        31          £58,257           £14,527
              32          £56,989           £14,171
              33          £54,891           £13,454
              34          £60,407           £15,180
  2007        35          £61,235           £14,867
              36          £59,270           £14,862
              37          £57,681           £14,549
              38          £62,863           £15,472
  2008        39          £63,547           £15,631
              40          £61,936           £15,003
              41          £59,747           £15,065
              42          £65,572           £16,662
  2009        43          £66,326           £16,220

Data as given in the problem.



Forecasted data (Below).

Year                                        Central   Central           Variation
                                              Avg.      Avg.  Variation      in
         Quarter Quarter Revenue Commission   Rev.    Comm.    in Rev.   Comm.
2009       44      2     £64,215   £15,986  £64,669   £15,815   -£453      £172
           45      3     £62,148   £15,417  £65,265   £15,947 -£3,117     -£531
           46      4     £67,547   £16,087  £65,861   £16,080 £1,685        £7
2010       47      1     £68,295   £16,604  £66,457   £16,213 £1,838       £391
           48      2     £66,640   £16,480  £67,054   £16,345   -£414      £135
           49      3     £64,522   £15,943  £67,650   £16,478 -£3,127     -£535
           50      4     £69,953   £16,695  £68,246   £16,611 £1,707        £85


To be able to forecast the future revenue and commission, we initially
needed to calculate the following in the above posted chart-
   1. Central Average Revenue calculated by (596.18*x+52149).(x=
      series in no. for required quarter – median)
   2. Central Average Commission calculated by (132.7*y+ 13426). (y=
      series in no. for required quarter – median)
   3. Variation in Revenue calculated by taking average of every same
      quarter of revenue to calculate the same for future.
   4. Variation in Commission calculated by taking average of every same
      quarter of commission to calculate the same for future.


Forecasted Revenue= Central average revenue+ Variation in
revenue.

Forecasted Commission= Central average commission+ Variation
in commission.

To know forecasting validity, these time series analysis has been taken
out by the help of coefficient of determination, which is derived by putting
a trend line over central average revenue and central average commission
respectively.



Conclusion

Overall this report provides with the precise graphical details about the
structure of commission and revenue over a specific period of time along
with a future forecast for the coming years. With consistency in the
increment of revenue despite few lows, the company is enjoying a level of
stability in profits and sales but on the other hand wherein sales teams
are putting their best to generate commissions with regards to high sales
that doesn’t seems to be possible due to non correlation between the two,
due to some policies of the company or may be different structure of
incentives. Accurate charts can assist in any specific time period to be
analyzed along with relevant finding to make the validity of future
forecast sounder on the basis of past performance. Spurious figures are
also dealt carefully by assuming the trend in the specific time frame.

Possible Amendments

Employees are asset of the company, specifically the one in service and
sales industry. So the utmost care should be driven to always boost the
workers and professional of the entity. Sales should be directly connected
to incentives which incline the sales professional to give their best, which
can help the company in its growth and productivity.
(Data provided   for   the   assignment   through   Webct-   University   of
Greenwich, UK)

				
DOCUMENT INFO
Shared By:
Tags:
Stats:
views:7
posted:4/29/2011
language:English
pages:13