Question

ITIS1P97 Data Analysis and Business Modelling. Excel Questions

PROBLEM 1: RANIS Enterprise Solutions is the provider of hosted customer relationship management (CRM) solutions for Small to Medium Enterprises (SMEs). The headquarters are in Toronto, ON, and they have clients across the globe. Currently, they are charging a $500 monthly fee for their CRM services that they offer to 300 of their clients. They have recently been contacted by their software vendor and been told that with a $180,000 upgrade on their hardware and CRM software platform, they can substantially improve their direct marketing offerings. The new system will cost them nothing beyond the initial cost, but they will have to increase the number of their maintenance and support staff from 10 to 13 to be able to comply with their service level agreements. The maintenance staff is paid $800 weekly. Using the what-if analysis feature of MS Excel, answer the following questions:

A. If the management thinks the upgrade will cause the demand (# of clients) to grow uniformly (same percentage every month) until it doubles at the end of the third year (144 weeks), what will be the average weekly demand growth (percentage) for their service? Note: assume 1 month is 4 weeks.

B. Assuming this demand growth (from question 1) is achievable, should they invest in this upgrade if they want to break even (reach the status quo profit levels) within 1 year? Why? Hint: Consider the average client size for the 144 weeks while dealing with the proposed change.

C. What level of weekly demand increase (percentage) would justify the investment if RANIS wants to break even (reach the status quo profit levels) after 2 years? D. If the demand will stay the way it is, however, there is an opportunity for RANIS to charge more for this new service, to maintain current profitability, what should the new monthly fee for this service be?

Answer 1

Answer:

A) If the management thinks the upgrade will cause the demand (# of clients) to grow uniformly (same percentage every month) until it doubles at the end of the third year (144 weeks), what will be the average weekly demand growth (percentage) for their service? Note: assume 1 month is 4 weeks.

Ans:

• Number of clients before upgrade - 300
• Number clients will be doubled in next 3 yrs (36 months/144 weeks) = 600
• Average client growth per year after upgrade = 300/3 = 33%
• Average client growth per month after upgrade = 300/36 = 8.33%
• Average client growth per week after upgrade = 300/144 = 2.08%

B) Assuming this demand growth (from question 1) is achievable, should they invest in this upgrade if they want to break even (reach the status quo profit levels) within 1 year? Why? Hint: Consider the average client size for the 144 weeks while dealing with the proposed change.

Ans:

• The uniformly client increment in first year after upgrade will be

= 300+100 = 400

• The month service for first year after upgrade will be

= 400 x $ 500 = $ 200,000

• The investment for upgrade of hardware and CRM software will be = $ 180,000
• That means there is a profit in investing for upgrade.

C) What level of weekly demand increase (percentage) would justify the investment if RANIS wants to break even (reach the status quo profit levels) after 2 years?

Ans: The weekly percentage growth should be 2.08%.

D) If the demand will stay the way it is, however, there is an opportunity for RANIS to charge more for this new service, to maintain current profitability, what should the new monthly fee for this service be?

Ans: The new monthly should be $ 650 per week.