Question

Sales and profit forecast based on 3 scenarios. I have read through our material and there is nothing to actually teach us how to do this problem step by step. I am lost. Please help.

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.

	January	February	March	April	May	June	July	August	September	October	November	December
Volume
Service A	100	120	140	160	180	200	220	200	180	160	140	120
Service B	50	55	60	65	70	75	80	85	90	95	100	105
Service C	75	73	71	69	67	65	63	61	59	57	55	53
Price
Service A	$500	$500	$500	$500	$500	$500	$500	$500	$500	$500	$500	$500
Service B	$2,000	$2,000	$2,000	$2,000	$2,000	$2,000	$2,000	$2,000	$2,000	$2,000	$2,000	$2,000
Service C	$750	$750	$750	$750	$750	$750	$750	$750	$750	$750	$750	$750
Margin
Service A	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%
Service B	32%	32%	32%	32%	32%	32%	32%	32%	32%	32%	32%	32%
Service C	20%	20%	20%	20%	20%	20%	20%	20%	20%	20%	20%	20%
Profit	$55,750	$61,150	$66,550	$71,950	$77,350	$82,750	$88,150	$88,550	$88,950	$89,350	$89,750	$90,150

Answer 1

For each of the 3 scenarios, determine the revised Volume, Price and Margin values based on the instructions. It can be verified that the Profit value at bottom, has been calculated for the original table as

Copy the original table in excel, and for each of the scenario, update the Volume, Price and Margin values as per instructions. Set the Profit function to be computed as per the above formula, and that should complete your work. This will ensure that Profit changes as per revised numbers. For the Scenario 1, we obtain following values

Good Year	Jan	Feb	March	April	May	June	July	Aug	Sep	Oct	Nov	Dec
Volume
Service A	120	140	160	180	200	220	240	260	280	300	320	340
Service B	50	60	70	80	90	100	110	120	130	140	150	160
Service C	75	75	75	75	75	75	75	75	75	75	75	75
Price
Service A	$500	$500	$500	$500	$500	$500	$500	$550	$550	$550	$550	$550
Service B	$2,000	$2,000	$2,000	$2,000	$2,000	$2,000	$2,000	$2,200	$2,200	$2,200	$2,200	$2,200
Service C	$750	$750	$750	$750	$750	$750	$750	$825	$825	$825	$825	$825
Margin
Service A	27.5%	27.5%	27.5%	27.5%	27.5%	27.5%	27.5%	27.5%	27.5%	27.5%	27.5%	27.5%
Service B	35.2%	35.2%	35.2%	35.2%	35.2%	35.90%	36.62%	37.35%	38.10%	38.86%	39.64%	40.43%
Service C	22.0%	22.0%	22.0%	22.0%	22.0%	22.0%	22.0%	22.0%	22.0%	22.0%	22.0%	22.0%
Profit	$64,075	$73,865	$83,655	$93,445	$103,235	$114,433	$125,944	$151,553	$164,933	$178,688	$192,828	$207,364

The calculations for Scenario 2 become more elaborate, since the Volume follows the percentage change. This requires us to calculate the %change for each of the months in 2017, and then apply it to the next year, beginning from Jan value taken as the old Dec value. This gives us

Avg. Year	January	February	March	April	May	June	July	Aug	Sep	October	November	December
Volume
Service A (Old)	100	120	140	160	180	200	220	200	180	160	140	120
Service B (Old)	50	55	60	65	70	75	80	85	90	95	100	105
Service C (Old)	75	73	71	69	67	65	63	61	59	57	55	53
%A		0.2	0.1666667	0.1428571	0.125	0.1111111	0.1	-0.0909091	-0.1	-0.1111111	-0.125	-0.1428571
%B		0.1	0.0909091	0.0833333	0.0769231	0.0714286	0.0666667	0.0625	0.0588235	0.0555556	0.0526316	0.05
%C		-0.026666667	-0.0273973	-0.028169	-0.0289855	-0.0298507	-0.0307692	-0.031746	-0.0327869	-0.0338983	-0.0350877	-0.0363636
Service A (New)	120	144	168	192	216	240	264	240	216	192	168	144
Service B (New)	105	115.5	126	136.5	147	157.5	168	178.5	189	199.5	210	220.5
Service C (New)	53	51.59	50.17	48.76	47.35	45.93	44.52	43.11	41.69	40.28	38.87	37.45
Price
Service A	$500	$500	$500	$500	$500	$500	$500	$500	$500	$500	$500	$500
Service B	$2,000	$2,000	$2,000	$2,000	$2,000	$2,000	$2,000	$2,000	$2,000	$2,000	$2,000	$2,000
Service C	$750	$750	$750	$750	$750	$750	$750	$750	$750	$750	$750	$750
Margin
Service A	25.50%	25.50%	25.50%	25.50%	25.50%	25.50%	25.50%	25.50%	25.50%	25.50%	25.50%	25.50%
Service B	31.68%	31.68%	31.68%	31.68%	31.68%	31.68%	31.68%	31.68%	31.68%	31.68%	31.68%	31.68%
Service C	20%	20%	20%	20%	20%	20%	20%	20%	20%	20%	20%	20%
Profit	$89,778	$99,279	$108,780	$118,280	$127,781	$137,282	$146,783	$150,164	$153,544	$156,925	$160,306	$163,687

Finally the Scenario 3 is a pretty straightforward application, and gives following values

Poor Year	January	February	March	April	May	June	July	Aug	Sep	October	November	December
Volume
Service A	100	110	121	133.1	146.41	161.051	177.1561	194.87171	165.640954	140.79481	119.67559	101.72425
Service B	50	50	50	50	50	50	50	50	50	50	50	50
Service C	75	75	75	75	75	75	75	75	75	75	75	75
Price
Service A	$500	$500	$475	$475	$475	$475	$475	$475	$475	$475	$475	$475
Service B	$2,000	$1,700	$1,700	$1,700	$1,700	$1,700	$1,700	$1,700	$1,700	$1,700	$1,700	$1,700
Service C	$750	$750	$712.50	$712.50	$712.50	$712.50	$712.50	$712.50	$712.50	$712.50	$712.50	$712.50
Margin
Service A	25%	25%	25%	25%	25%	25%	25%	25%	23.75%	23.75%	23.75%	23.75%
Service B	32%	32%	32%	32%	32%	32%	32%	32%	30.40%	30.40%	30.40%	30.40%
Service C	20%	20%	20%	20%	20%	20%	20%	20%	19%	19%	19%	19%
Profit	$55,750	$52,200	$52,256	$53,693	$55,274	$57,012	$58,925	$61,029	$54,679	$51,877	$49,494	$47,469

Let me know for any doubt/clarification.