Question 6 The following worksheet (Figure 3) shows part of a quarterly planning model that allows the user to test a variety of "what-if" scenarios over a flexible number of quarters as specified by the data value in cell B10. The value in B10 can range between 1 and 8, and controls the number of quarters that show values, other than zeros, within the Profitability Report. G H 3 7 0 8 0 0 0 0 0 0 0 0 0 0 0 B D E F 1 Planning.xlsx 2 Quarterly Planning Model 3 4 INPUT 5 Initial volume (units) 100 6 Quarterly sales growth rate 5% 7 Unit Selling price 30 8 Variable Cost % 70% 9 Fixed costs 1100 10 Quarters to project (1-8) 6 11 12 REPORT 13 QTR 1 2 3 4 5 6 14 Sales volume 100 105 110 116 122 128 15 16 Revenue 3,000 3,150 3,308 3,473 3,647 3,829 17 less Variable costs 2,100 2,205 2,315 2,431 2,553 2,680 18 Contribution margin 900 945 992 1,042 1,094 1,149 19 less Fixed costs 1,100 1,100 1,100 1,100 1,100 1,100 20 Profit (200) (155) (108) (58) (6) 49 21 22 Total Profit (478) 23 Average Profit (80) 24 25 DATA TABLE 1 showing quarterly profit while varying sales growth rate 26 27 QTR 2 3 4 5 6 28 (200) (155) (108) (58) (6) 49 29 2.5% (200) (178) (154) (131) (107) (82) 30 5.0% (200) (155) (108) (58) (6) 49 31 7.5% (200) (133) (60) 18 102 192 32 10.0% (200) (110) 98 218 349 33 34 DATA TABLE 2 showing total profit while varying sales growth rate and selling price 35 36 (478) $25 $30 $35 $40 37 2.5% (1,809) (851) 107 1,065 38 5.0% (1,499) (478) 542 1,562 39 7.5% (1,167) (80) 1,006 2,093 40 10.0% (813) 344 1,501 2,659 Figure 3: Spreadsheet used to conduct quarterly planning and 'what-if analysis 1 NOOOOO 00 OOOOO Required: (9+2+ 2 + 2 = 15 marks) a. Construct formulas for the cells given in parts (1) to (v) below, subject to the conditions given in brackets in the Description column, i.e., if the formula needs to be copied across multiple columns. Cell Description Marks i. B14 Sales volume in units, and growing by the percentage in cell B6. (4 marks) (Create this formula so that it can be copied across to the remaining quarters without needing modification, and so that it returns zeros once the quarter number exceeds the value in B10). Note, sales volume values in B14:114 are presented using "decrease decimal" function filter and therefore show zero decimal places. For this task, it is not required that your suggested formula for the cell B14 rounds the sale volume values. ii. B16 Revenue in dollars and growing by the percentage in cell B6 (2 marks) (Create this formula so that it can be copied across to the remaining quarters without needing modification, and so that it returns zeros once the quarter number exceeds the value in B10). iii. B22 Total profit for 8 quarters (1 marks) iv. B23 (1 marks) Average of profit for quarters actually projected, as determined by cell B10. Data Table 2 is a two-way data table that shows total profit while varying sales growth rate and selling price. v. A36 (1 marks) b. C. Based on the data in Data Table 1, what is the minimum sales growth rate the company should be aiming for? Explain your reasoning. (2 marks) Based on the data in Data Table 2, estimate the break-even selling price for the 2.5% sales growth rate (a range of values is acceptable as an answer to this question). Explain your reasoning. (2 marks) The adoption of effective spreadsheet design principles when building financial models can improve the likelihood that the resultant spreadsheet models will be "better" (i.e. error free) and meet the needs of the user(s). Identify and discuss two (2) spreadsheet design principles in the worksheet above. For each example, clearly explain the fundamentals of the design principle it demonstrates and how it contributes to the development of better" spreadsheet models. (2 marks) d. To respond to this question, fill in the template below: Q# Cell Description/Formula B14 B16 B22 a. B23 Marks /4 marks /2 marks /1 marks /1 marks (1 marks /2 marks /2 marks /2 marks A36 b. C. d. Question 6 The following worksheet (Figure 3) shows part of a quarterly planning model that allows the user to test a variety of "what-if" scenarios over a flexible number of quarters as specified by the data value in cell B10. The value in B10 can range between 1 and 8, and controls the number of quarters that show values, other than zeros, within the Profitability Report. G H 3 7 0 8 0 0 0 0 0 0 0 0 0 0 0 B D E F 1 Planning.xlsx 2 Quarterly Planning Model 3 4 INPUT 5 Initial volume (units) 100 6 Quarterly sales growth rate 5% 7 Unit Selling price 30 8 Variable Cost % 70% 9 Fixed costs 1100 10 Quarters to project (1-8) 6 11 12 REPORT 13 QTR 1 2 3 4 5 6 14 Sales volume 100 105 110 116 122 128 15 16 Revenue 3,000 3,150 3,308 3,473 3,647 3,829 17 less Variable costs 2,100 2,205 2,315 2,431 2,553 2,680 18 Contribution margin 900 945 992 1,042 1,094 1,149 19 less Fixed costs 1,100 1,100 1,100 1,100 1,100 1,100 20 Profit (200) (155) (108) (58) (6) 49 21 22 Total Profit (478) 23 Average Profit (80) 24 25 DATA TABLE 1 showing quarterly profit while varying sales growth rate 26 27 QTR 2 3 4 5 6 28 (200) (155) (108) (58) (6) 49 29 2.5% (200) (178) (154) (131) (107) (82) 30 5.0% (200) (155) (108) (58) (6) 49 31 7.5% (200) (133) (60) 18 102 192 32 10.0% (200) (110) 98 218 349 33 34 DATA TABLE 2 showing total profit while varying sales growth rate and selling price 35 36 (478) $25 $30 $35 $40 37 2.5% (1,809) (851) 107 1,065 38 5.0% (1,499) (478) 542 1,562 39 7.5% (1,167) (80) 1,006 2,093 40 10.0% (813) 344 1,501 2,659 Figure 3: Spreadsheet used to conduct quarterly planning and 'what-if analysis 1 NOOOOO 00 OOOOO Required: (9+2+ 2 + 2 = 15 marks) a. Construct formulas for the cells given in parts (1) to (v) below, subject to the conditions given in brackets in the Description column, i.e., if the formula needs to be copied across multiple columns. Cell Description Marks i. B14 Sales volume in units, and growing by the percentage in cell B6. (4 marks) (Create this formula so that it can be copied across to the remaining quarters without needing modification, and so that it returns zeros once the quarter number exceeds the value in B10). Note, sales volume values in B14:114 are presented using "decrease decimal" function filter and therefore show zero decimal places. For this task, it is not required that your suggested formula for the cell B14 rounds the sale volume values. ii. B16 Revenue in dollars and growing by the percentage in cell B6 (2 marks) (Create this formula so that it can be copied across to the remaining quarters without needing modification, and so that it returns zeros once the quarter number exceeds the value in B10). iii. B22 Total profit for 8 quarters (1 marks) iv. B23 (1 marks) Average of profit for quarters actually projected, as determined by cell B10. Data Table 2 is a two-way data table that shows total profit while varying sales growth rate and selling price. v. A36 (1 marks) b. C. Based on the data in Data Table 1, what is the minimum sales growth rate the company should be aiming for? Explain your reasoning. (2 marks) Based on the data in Data Table 2, estimate the break-even selling price for the 2.5% sales growth rate (a range of values is acceptable as an answer to this question). Explain your reasoning. (2 marks) The adoption of effective spreadsheet design principles when building financial models can improve the likelihood that the resultant spreadsheet models will be "better" (i.e. error free) and meet the needs of the user(s). Identify and discuss two (2) spreadsheet design principles in the worksheet above. For each example, clearly explain the fundamentals of the design principle it demonstrates and how it contributes to the development of better" spreadsheet models. (2 marks) d. To respond to this question, fill in the template below: Q# Cell Description/Formula B14 B16 B22 a. B23 Marks /4 marks /2 marks /1 marks /1 marks (1 marks /2 marks /2 marks /2 marks A36 b. C. d