Information Technology IT – 9 Modelling | e-Consult
9 Modelling (1 questions)
Login to see all questions.
Click on a question to view the answer
To perform a what-if analysis using the sales volume, I would set up a spreadsheet with the following components:
- Input Cell: A cell (e.g., cell B1) would be designated for the user to input the estimated units sold.
- Cost Calculations: A cell (e.g., cell C1) would calculate the total variable cost based on the number of units sold (e.g.,
=B1*5). - Revenue Calculation: A cell (e.g., cell D1) would calculate the total revenue based on the number of units sold (e.g.,
=B1*15). - Profit Calculation: A cell (e.g., cell E1) would calculate the total profit by subtracting total costs from total revenue (e.g.,
=D1-C1-10000). - What-If Analysis Setup: I would then use the "What-If Analysis" feature in the spreadsheet program. Specifically, I would use the "Scenario Manager" or "Scenario Tool". This allows me to define different scenarios with varying values for the input cell (B1). For example, I could create scenarios for 3,000 units, 4,000 units, and 6,000 units. Each scenario would have a different value entered in cell B1.
- Analysis and Interpretation: After defining the scenarios, the spreadsheet program would automatically calculate the profit for each scenario. I would then analyze the results to understand the relationship between the number of units sold and the potential profit. This would help the business make informed decisions about production and sales targets. The results could be presented in a table.
Example Table:
| Units Sold | Total Revenue | Total Variable Cost | Fixed Costs | Total Costs | Profit |
| 3,000 | 45,000 | 15,000 | 10,000 | 25,000 | 20,000 |
| 4,000 | 60,000 | 20,000 | 10,000 | 30,000 | 30,000 |
| 6,000 | 90,000 | 30,000 | 10,000 | 40,000 | 50,000 |