Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

In this analysis, you will be generating multiple sales forecasts based on different modeling assumptions. In this scenario, you will be working with a business

In this analysis, you will be generating multiple sales forecasts based on different modeling assumptions.

In this scenario, you will be working with a business that offers three types of services to the public. You will be evaluating the effects of different assumptions related to sales level, sales price and service mix.

Begin with the information below for 2017:

image text in transcribed

Requirements:

1) Using the information from 2017, create a sales and profit forecast based on each of the following three scenarios:

Scenario 1 (Good Year)

  • Sales of Service A begin at their December 2017 levels, and increase as they did in 2017, but there is no seasonality decrease beginning in August, the climb continues through the end of the year.
  • Sales of Service B begin at their January 2017 level but increase at twice the pace of 2017.
  • Sales of Service C begin at their January 2017 level and remain constant throughout the year.
  • Beginning in August, prices on all services are raised by 10%
  • Margins on all products are 10% higher than 2017, and beginning in June margins on service B grow at 2% per month

Scenario 2 (Average Year)

  • Product sales levels begin at their December 2017 level, and grow or shrink based on their percentage growth or reduction from 2017.
  • All prices remain the same.
  • Margins on Service A grow by 2%, while margins on Service B shrink by 1%

Scenario 3 (Poor Year)

  • Product sales levels begin at their January 2017 level.
  • Sales of Service A grow at 10% per month until August, then shrink at 15% per month.
  • All other sales levels are flat.
  • Prices for Service A and C are reduced 5% in March, the price for Service B is reduced 15% in February.
  • Margins are cut across the board by 5% in September.

Include a copy of your Microsoft Excel spreadsheet that demonstrates your calculations.

Include a one-page description of the overall impacts of each scenario on the business.

Januar Februa Augus Novemb Decemb SeptembOctob er Y March April May June July er Volume Service A 120 140 160 100 50 75 55 60 Service B Service C 65 180 70 67 200 75 65 220 80 63 200 85 61 180 90 59 160 95 57 140 100 5 5 120 105 53 73 71 69 Price Service A Service B Service $500 $2,000 $750 $500 $2,000 $750 $500 $2,000 $750 $500 $2,000 $750 $500 $2,000 $750 $500 $2,000 $750 $500 $2,000 $750 $500 $2,000 $750 $500 $2,000 $750 $500 $2,000 $750 $500 $2,000 $750 $500 $2,000 $750 | Margin Service A 25% 25% 25% 32% 25% 32% 20% Service B 25% 32% 20% 25% 32% 20% 25% 32% 25% 32% 20% 25% 32% 20% 25% 32% 20% 25% 32% 20% 25% 32% 20% 32% 32% Service C 20% 20% 20% 20% $82,75 Profit $55,75 0 $61,15 0 $66,55 0 $71,95 0 $77,35 0 0 $88,15 0 $88,55 0 $88,950 $89,35 0 $89,750 $90,150

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access with AI-Powered Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Students also viewed these Finance questions