soreadsheet excercise on excel

HOME INSERT PAGE LAYOUT FORMULAS 'DATA REVIEW VIEW PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View Enable Editing A B C Spreadsheet Exercise: Chapter 15 The current balance in accounts receivable for Eboy Corporation is $443,000. This level was achieved with annual (365 days) credit sales of $3,544,000. The firm offers its customers credit terms of net 30. However, in an effort to help its cash flow position and to follow the actions of its rivals, the firm is considering changing its credit terms from net 30 to 2/10 net 30. The objective is to speed up the receivable collections and thereby improve the firm's cash flows. Eboy would like to increase its accounts receivable turnover to 12.0. The firm works with a raw material whose current annual usage is 1,450 units. Each finished product requires one unit of this raw material at a variable cost of $2,600 per unit and sells for $4,200 on terms of net 30. It is estimated that 70% of the firm's customers will take the 2% discount and that, with the discount, sales of the finished product will increase by 50 units per year. The firm's opportunity cost of funds invested in accounts receivable is 12.5% In analyzing the investment in accounts receivable, use the variable cost of the product sold instead of the sale price because the variable cost is a better indicator of the firm's investment. To Do Create a spreadsheet similar to Table 15.3 to analyze whether the firm should initiate the proposed discount. What is your advice? Make sure that you calculate the following: a. Additional profit contribution from sales. b. Average investment in accounts receivable at present (without the early payment discount) c. Average investment in accounts receivable with the proposed discount. Chapter 15 Type here to search es you CLULU CUL ILS Sale Slay II PIUCLEU VIEW. Enable Editing A - fe B C LARINETED a. Additional profit contribution from sales. b. Average investment in accounts receivable at present (without the early payment discount) c. Average investment in accounts receivable with the proposed discount. d. Reduction in investment in accounts receivable. e. Cost savings from reduced investment in accounts receivable. f. Cost of the cash discount. g. Net profit (loss) from initiation of proposed cash discount. Solution a. Additional profit contribution from sales. mo 50 Increase in units due to discount Selling price @net 30 Variable cost per unit Additional profit contribution from sales 4,200 2,600 b. Average investment in accounts receivable at present (without the early payment discount) Variable cost per unit Raw material annual nace number of unit) Chapter 15 2,600 1.450 DR READY PROTECTED VIEW Be careful files from the Internet can contain viruses. Unless you need to edit. it's safer to stay in Protected View Enable Editing AB Variable cost per unit Raw material annual usage number of units) Accounts receivable Sales Days in a year Collection period AR turnover Average investment presently (without discount) S $ 2,600 1,450 443,000 3,544,000 365 c. Average investment in accounts receivable with the proposed discount. 2,600 Variable cost per unit Raw material annual usage Expected AR turnover due to discount Average investment presently (with cash discounts) 12.00 d. Reduction in investment in accounts receivable. Reduction in accounts receivable investment e, Cost savings from reduced investment in accounts receivable. Chapter 15 READY Tvne here to search Milios Zutter Spreadsheet_Chapter_15_Sta VIEW HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW CTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protec Reduction in accounts receivable investment e. Cost savings from reduced investment in accounts receivable. 12.5% Opportunity cost of funds Cost savings from reduced investment in AR f. Cost of the cash discount. 2.00% 70% Cash discount term Percentage of customers to take discount Raw material annual usage (new) Selling price per unit Cost of cash discount 4,200 g. Net profit (loss) from initiation of proposed cash discount. Additional profit contribution from sales Cost savings from reduced investment in AR Less: Cost of cash discount Net profit (loss) from proposed cash discount Chapter 15 Type here to search o te Enable Editing HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit. It's safer to stay in Protected View - 1 * fi AB Net profit (loss) from proposed cash discount The above calculation shows a net Thus, Eboy Corporation from the cash discount. initiate the proposed cash discount. Points Requirments 1 In cell G23, by using cell references to the given data, calculate the additional profit contribution from sales. 2 In cell G32, by using cell references to the given data, calculate the collection period. 3 In cell G33, by using cell references to the given data, calculate the accounts receivable turnover. 4 In cell G34, by using cell references to the given data, calculate the average investment presently (without discount), Remember to use the variable cost of the product sold rather than sales. 5 In cell G39, by using cell references to the given data, calculate the raw material annual usage. 6 In cell G41, by using cell references to the given data, calculate the average investment presently (with cash discounts) 7 In cell G45, by using cell references to the given data, calculate the reduction in accounts receivable investment 8 In cell G50, by using cell references to the given data, calculate the cost savings from the reduced investment in accounts receivable. 9 In cell G58. by using cell references to the given data, calculate the cost of the cash discount Chapter 15 ) Type here to search L ote 9 W X RETRU UNTUUT FORMULAS DATA REVIEW VIEW PROTECTED VIEW Be careful files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected view Enable Editing 5 In cell G39, by using cell references to the given data, calculate the raw material annual usage.. 6 In cell G41, by using cell references to the given data, calculate the average investment presently (with cash discounts) 7 In cell G45, by using cell references to the given data, calculate the reduction in accounts receivable investment 8 In cell G50, by using cell references to the given data, calculate the cost savings from the reduced investment in accounts receivable. 9 In cell G58, by using cell references to the given data, calculate the cost of the cash discount. 10 In cell G65, by using cell references to the given data, calculate the net profit (loss) from the proposed cash discount 11 In cell F67, type either profit or loss depending on whether the company will gain or lose with the new discount policy 12 In cell E68, type either should or should not depending on whether the company should or should not implement the new discount policy. 13 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed Chapter 15 READY Type here to search o Re 9w] HOME INSERT PAGE LAYOUT FORMULAS 'DATA REVIEW VIEW PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View Enable Editing A B C Spreadsheet Exercise: Chapter 15 The current balance in accounts receivable for Eboy Corporation is $443,000. This level was achieved with annual (365 days) credit sales of $3,544,000. The firm offers its customers credit terms of net 30. However, in an effort to help its cash flow position and to follow the actions of its rivals, the firm is considering changing its credit terms from net 30 to 2/10 net 30. The objective is to speed up the receivable collections and thereby improve the firm's cash flows. Eboy would like to increase its accounts receivable turnover to 12.0. The firm works with a raw material whose current annual usage is 1,450 units. Each finished product requires one unit of this raw material at a variable cost of $2,600 per unit and sells for $4,200 on terms of net 30. It is estimated that 70% of the firm's customers will take the 2% discount and that, with the discount, sales of the finished product will increase by 50 units per year. The firm's opportunity cost of funds invested in accounts receivable is 12.5% In analyzing the investment in accounts receivable, use the variable cost of the product sold instead of the sale price because the variable cost is a better indicator of the firm's investment. To Do Create a spreadsheet similar to Table 15.3 to analyze whether the firm should initiate the proposed discount. What is your advice? Make sure that you calculate the following: a. Additional profit contribution from sales. b. Average investment in accounts receivable at present (without the early payment discount) c. Average investment in accounts receivable with the proposed discount. Chapter 15 Type here to search es you CLULU CUL ILS Sale Slay II PIUCLEU VIEW. Enable Editing A - fe B C LARINETED a. Additional profit contribution from sales. b. Average investment in accounts receivable at present (without the early payment discount) c. Average investment in accounts receivable with the proposed discount. d. Reduction in investment in accounts receivable. e. Cost savings from reduced investment in accounts receivable. f. Cost of the cash discount. g. Net profit (loss) from initiation of proposed cash discount. Solution a. Additional profit contribution from sales. mo 50 Increase in units due to discount Selling price @net 30 Variable cost per unit Additional profit contribution from sales 4,200 2,600 b. Average investment in accounts receivable at present (without the early payment discount) Variable cost per unit Raw material annual nace number of unit) Chapter 15 2,600 1.450 DR READY PROTECTED VIEW Be careful files from the Internet can contain viruses. Unless you need to edit. it's safer to stay in Protected View Enable Editing AB Variable cost per unit Raw material annual usage number of units) Accounts receivable Sales Days in a year Collection period AR turnover Average investment presently (without discount) S $ 2,600 1,450 443,000 3,544,000 365 c. Average investment in accounts receivable with the proposed discount. 2,600 Variable cost per unit Raw material annual usage Expected AR turnover due to discount Average investment presently (with cash discounts) 12.00 d. Reduction in investment in accounts receivable. Reduction in accounts receivable investment e, Cost savings from reduced investment in accounts receivable. Chapter 15 READY Tvne here to search Milios Zutter Spreadsheet_Chapter_15_Sta VIEW HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW CTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protec Reduction in accounts receivable investment e. Cost savings from reduced investment in accounts receivable. 12.5% Opportunity cost of funds Cost savings from reduced investment in AR f. Cost of the cash discount. 2.00% 70% Cash discount term Percentage of customers to take discount Raw material annual usage (new) Selling price per unit Cost of cash discount 4,200 g. Net profit (loss) from initiation of proposed cash discount. Additional profit contribution from sales Cost savings from reduced investment in AR Less: Cost of cash discount Net profit (loss) from proposed cash discount Chapter 15 Type here to search o te Enable Editing HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit. It's safer to stay in Protected View - 1 * fi AB Net profit (loss) from proposed cash discount The above calculation shows a net Thus, Eboy Corporation from the cash discount. initiate the proposed cash discount. Points Requirments 1 In cell G23, by using cell references to the given data, calculate the additional profit contribution from sales. 2 In cell G32, by using cell references to the given data, calculate the collection period. 3 In cell G33, by using cell references to the given data, calculate the accounts receivable turnover. 4 In cell G34, by using cell references to the given data, calculate the average investment presently (without discount), Remember to use the variable cost of the product sold rather than sales. 5 In cell G39, by using cell references to the given data, calculate the raw material annual usage. 6 In cell G41, by using cell references to the given data, calculate the average investment presently (with cash discounts) 7 In cell G45, by using cell references to the given data, calculate the reduction in accounts receivable investment 8 In cell G50, by using cell references to the given data, calculate the cost savings from the reduced investment in accounts receivable. 9 In cell G58. by using cell references to the given data, calculate the cost of the cash discount Chapter 15 ) Type here to search L ote 9 W X RETRU UNTUUT FORMULAS DATA REVIEW VIEW PROTECTED VIEW Be careful files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected view Enable Editing 5 In cell G39, by using cell references to the given data, calculate the raw material annual usage.. 6 In cell G41, by using cell references to the given data, calculate the average investment presently (with cash discounts) 7 In cell G45, by using cell references to the given data, calculate the reduction in accounts receivable investment 8 In cell G50, by using cell references to the given data, calculate the cost savings from the reduced investment in accounts receivable. 9 In cell G58, by using cell references to the given data, calculate the cost of the cash discount. 10 In cell G65, by using cell references to the given data, calculate the net profit (loss) from the proposed cash discount 11 In cell F67, type either profit or loss depending on whether the company will gain or lose with the new discount policy 12 In cell E68, type either should or should not depending on whether the company should or should not implement the new discount policy. 13 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed Chapter 15 READY Type here to search o Re 9w]