Shelly Cashman Excel 2019 | Modules 4-7: SAM Capstone Project 1a
Victoria Streaming Service
· Bao is confident that revenue will increase by at least 2 percent per quarter next year. He wants to calculate these revenue goals in a second projection.
Project next year’s revenue in the second projection (range H9:K9) based on a growth series using 1.02 as the step value.
The Correct Answer and Explanation is :
Correct Calculation for Revenue Projection
To calculate the second projection of next year’s revenue in the range H9:K9 based on a growth series with a step value of 1.02, you start with the revenue from the previous quarter and multiply it by 1.02 each quarter.
Example Calculation:
Assume the revenue for the fourth quarter of this year (G9) is $100,000.
- Q1 Next Year (H9) = G9 × 1.02 = $100,000 × 1.02 = $102,000
- Q2 Next Year (I9) = H9 × 1.02 = $102,000 × 1.02 = $104,040
- Q3 Next Year (J9) = I9 × 1.02 = $104,040 × 1.02 = $106,120.80
- Q4 Next Year (K9) = J9 × 1.02 = $106,120.80 × 1.02 = $108,243.22
If G9 is not $100,000, replace the value with your actual revenue for G9 and apply the same calculation.
Explanation (300 words)
Bao’s projection of a 2% quarterly revenue increase involves a growth series calculation, which multiplies the revenue from the previous quarter by a growth factor of 1.02 (representing a 2% increase). This approach assumes compounding growth, meaning each quarter builds upon the revenue of the previous one, including its growth.
In Excel, this can be accomplished using formulas. For example:
- In cell H9, enter the formula
=G9*1.02to calculate the revenue for the first quarter of next year. - Drag this formula across cells I9 to K9, so each cell multiplies the prior quarter’s revenue by 1.02.
This method ensures consistency and accuracy, automating the projection process. It is particularly useful for financial forecasting, where quarterly growth compounds over time.
Compounding growth provides a more realistic projection than simply adding a fixed amount each quarter. For example, the revenue for Q1 grows by 2% from the previous quarter’s revenue, and Q2 builds upon Q1’s increased amount, incorporating the 2% growth into each subsequent quarter.
Accurate forecasting like this helps businesses like Victoria Streaming Service set realistic revenue goals, allocate resources, and make informed strategic decisions. By leveraging Excel’s tools, such projections become quick, accurate, and easily adjustable for different growth assumptions.