MiniCase6_10692

.xlsx

School

University of North Carolina, Greensboro *

*We aren’t endorsed by this school

Course

110

Subject

Finance

Date

Apr 3, 2024

Type

xlsx

Pages

Uploaded by SuperMagpieMaster502

Mini Case 6 Steps to Perform: Step Instructions 1 2 3 4 5 You are working as an analyst with the sales department of Alpha Trading Corporation. You are assisting the sales manager in planning, performance and budgeting. In this exercise, you will construct cell formula using relative, mixed and absolute referencing. You will also apply Cell Styles and conditional formatting to prepare the data for analysis. You should write smart formulas (wherever feasible), i.e., write once and drag/copy to other cells. If you write multiple formulas where a single formula was possible, then points will be deducted. On the P1 worksheet, perform the following The commission rate for the salesperson is given in Table 1 (A3:B8) and the sales generated by the salesperson are given in Table 2 (A10:E16). Commission amount = Commission rate * sales amount Write a formula in B21 to calculate "Commission amount" based on the Commission Rate in Table 1 and sales amounts in Table 2. Apply the "Commission Rate" (B5) and the "sales amount" (B13) to determine the Commission amount in cell B21 and copy across and down through to cell E24. The sales manager would like to understand the Commission variation as a percentage of total across the quarters. The formula to compute % of total is % of total = (quarterly commission of the salesperson)/ (total commission earned by that salesperson) (a) Write a formula in B31 to calculate "Quarterly Distribution of Commission amount" based on the Commission amount in Table 3. Apply the "Commission amount" (B21) and the "Total" (F21) to determine the Quarterly Distribution of Commission amount in cell B31. Copy the B31 formula across and down through to cell E34. You should write a formula such that when the formula is copied, it computes the percent of total correctly. (b) Apply conditional formatting to the quarterly distribution data in the range B31:E34 in Table 4. Apply the following highlight cell rules.

6 i. If the cell value is greater than 25%, fill the cell with Green Fill with Dark Green Text. 7 ii. If the cell value is less than 24%, fill the cell with Light Red fill with Dark Red Text. 8 iii. If the cell value is between 24% and 25%, apply Custom Format and fill it with Yellow color. 9 10 11 12 13 14 Instead of allocating bonuses from a Bonus pool, the sales manager decided to allocate a bonus based on Commission Earned and a bonus rate for each of the salesperson. The Bonus rate is given in Table 5 (B38:E40). Bonus amount = Bonus Rate * Commission amount (a) Write a formula in B44 to compute the "Bonus amount" based on the Commission amount calculated in Table 3 and the bonus rate in Table 5. Apply the "Bonus rate" (B40) and the "Commission amount" (B21) to determine the Bonus amount in cell B44 and copy across and down through to cell E47. You should write a formula such that when the formula is copied, it computes the bonus amount correctly. (b) Apply Conditional Formatting to the range C44:C47 (Q2), Add green data bar. Edit the rule and show bar only. (c) Apply Conditional Formatting to the range D44:D47 (Q3), Add red data bar. Edit the rule and change the minimum value to 900 and the maximum value to 1800. (d) The column Q4 – Q1, computes the difference in the bonus amount of Quarter 4 and Quarter 1. Apply Conditional Formatting to the range F44:F47 (Q4 - Q1), Add data bar, gradient fill, Blue data bar (Can be Blue or Light blue). Edit the rule and change the Axis Settings to cell midpoint. (Axis settings can be changed by clicking the Negative Value and Axis Settings button). The sales manager would like to understand the impact of the commission paid when the commission rate and commission amount changes. They have created Table 7 (A53:F63) and they want you to write the formula to compute the commission amount. Quarterly sales are given in cell range A55:A63. Commission Rates are given in cell range B54:F54.

15 16 Apply Conditional Formatting to the range B55:F63, Add Color Scale - Apply Red - Yellow - Green color scale 17 18 Monthly net sales are given in Table 8, compute cumulative sales using Formula with appropriate cell references. 19 20 21 i. Apply the Bottom 3 items rule and fill the cell with Light Red fill with Dark Red Text. 22 ii. Apply the above average rule and fill the cell with Green Fill with Dark Green Text. 23 (a) Write a formula in cell B55 to compute the "Quarterly Commission" based on the Quarterly sales and Commission Rates. Apply the "Quarterly sales" (A55) and the "Commission Rate" (B54) to determine the "Quarterly Commission" in cell B55 and copy over and down through to cell F63. You should write a formula such that when the formula is copied, it computes the commission amount correctly. Do not hardcode values in the formula; refer cells. (b) Using a color scale, the sales manager would like to understand the impact on commission amount due to changes in commission rate and quarterly sales. (a) Write a formula in cell D86 using the SUM Function to calculate "Year to Date Cumulative Net sales" based on the Monthly Net sales amounts in Table 9 (C86:C97). Apply the "Monthly Net sales" (B86) to determine the "Year To Date Cumulative Net sales" in cell D86 and copy over and down through to cell D97. You should write a formula such that when the formula is copied, it computes the "Year To Date Cumulative Net sales" amount correctly. Hint: Please go through the Learning Exercise 8.6 Video to see how this can be done. (b) Apply conditional formatting to the Monthly Net sales in the range C86:C97 in Table 8. Apply the following Top/Bottom cell rules. Gross monthly sales and monthly sales return are given in Table 10 below; compute cumulative sales by creating a Formula using appropriate cell references.

24 25 (b) Apply Conditional Formatting to the range C103:C114 (Gross Monthly Sales) , Add 3 Arrows (colored) icon sets. 26 27 28 (b) Write a formula in the cell range C126 to compute the Price, Copy it across to G126. The price is constant for all months. 29 (c) Write a formula in the cell range C127 to compute the revenue, Copy it across to G127. 30 31 32 (i) Apply Heading1 to cell A122 (sales & Commission Projections). 33 (ii) Apply input cell style to B118, B119, B120 and B125. 34 (iii) Apply calculation cell style to cell range C125:G127 , B126 and B127. 35 (iv) Apply output cell style to range B128:G128. 36 (v) Apply Heading2 to range B124:G124 (months). 37 (vi) Apply Note style to range A130. 38 Save the file and upload it Write a formula in cell E103 to calculate "Year to Date Cumulative Net sales" based on the "Gross Monthly sales" and "sales Return" in Table 9 (C103:D114). Apply the "Gross Net Sales" (C103) and "Sales Return" (D103) to determine the "Year To Date Cumulative Net Sales" in cell E103 and copy over and down through to cell E114. You should write a formula such that when the formula is copied, it computes the "Year To Date Cumulative Net Sales" amount correctly. Hint : Please go through the Learning Exercise 8.6 Video to see how this can be done. The sales manager wants to forecast the sales and commissions for one of the product. They have created a template as shown below. You are asked to complete the model and Style it as per the instructions. (a) Write a formula in the cell range C125 to compute the sales. Copy it across to G125 to compute the sales for all months. Sales in Feb = sales in Jan * (1+ sales growth) (d) Write a formula in the cell range C128 to compute the commission, Copy it across to G128. Commission = Commission rate * Revenue (e) Apply Cell Styles to the cells as indicated in below steps. Note : Minor deviations from Sample answer can happen due to style customization. You will be awarded points based on the application of Style and not on look and feel of the text.

Total Points