** Please see the attached file for the complete problem description **
In cell G6 write a formula giving Ann's total sales using the SUM function.
Drag the formula in G6 to cells G7:G10. Did this give the right values for Bob's, Charles's, David's, and Ellen's total sales?
Without using the SUM function, enter a formula in cell B11 giving the total sales of boxes of Christmas Cards.
Drag the formula in cell B11 to cells C11:F11. Did this give the right values for total sales of boxes of Birthday, All Occasion, Thank You, and Mixed Cards?
In cell B12, enter a formula for the total profit made by these five persons selling Christmas cards. Do not enter any numbers, only cell references.
Drag the formula in cell B12 to cells C12:F12. Did this give you the right values for the total profit on boxes of Birthday, All Occasion, Thank You and Mixed Cards?
Using the SUMPRODUCT function, enter a formula in cell H6 giving Ann's total profit from selling all types of cards.
Drag the formula in H6 to cells H7:H10. Do not enter any numbers, only cell references. Did this give the right values for Bob's, Charles's, David's, and Ellen's total profit from selling all types of cards? If not, it is because you did not use the absolute addresses for the profits per box. If that is the case, go back to cell H6. In the formula bar, highlight each unit profit and use the F4 key to make the reference absolute. After making all the references to unit profits absolute, now drag the formula in H6 to cells H7:H10. Now , did this give the right values for Bob's, Charles's, David's, and Ellen's total profit from selling all types of cards?
In cell B13, use the MAX function in order to determine the maximum Christmas card sales.
Drag the formula n cell B13 to cells C13:F13. Did this give you the maximum values of Birthday, All Occasion, Thank You, and Mixed Card sales?
In cell I5 enter "Most Christmas Sales." In cell I6 enter an IF statement that will compare Ann's Christmas card sales to the maximum value in cell B13. If it is equal to this value, Ann's name (the entry in cell A6) should be printed in I6; otherwise nothing should be printed - this is accomplished by entering "".
Make sure the reference to cell B13 is absolute in the formula in the IF statement formula in cell I6. Drag cell I6 formula down to I7:I10. Did it print the person with the most Christmas card sales?
Instead of the profit per box values in row 4, assume that Ann's profit is calculated as follows: 0.12(Christmas Cards Sales)2 + 0.02 (Birthday Card Sales)3 + 3.50 (All Occasion Card Sales) + 0.05 (Thank You Card Sales) * (Mixed Card Sales). In cell C15, determine Ann's total profit.
In cell C16, use an IF statement that compares Ann's profit using the new method proposed in problem 33 (given in cell C15) with that of the old method proposed in problem 27 (given in cell H6). If the new profit is higher, 'NEW' should be printed in cell C16 otherwise; 'OLD' should be printed in the cell.
Suppose we wish to calculate the sum of all Mixed card sales of those persons who sold less than 20 boxes of Christmas Cards. In cell C19, use the SUMIF command to determine this quantity.
Please see the attached file for the solution. If you need to view the formula, click on the relevant cell ...
This solution provides a complete computation of the given accounting problems in Excel format.