Hi I have an excel problem, sheet 1 is the questions to be answered and sheet 2 are the questions. I already started part a but got stuck on part b, c and d. please let me know if you can help me understand this question.
Schedule a: Sales plan Weighted average sales price Total Units Credit sales, 100% 1st Quarter 2nd Quarter Quarter th Quarter ear End 3rd 4 Y 234.0855 235.8071 238.935 239.3757 18600 21600 23400 25500 89100 4305900 5093496 5591196 6104190 21094782 NOTE: Actual A/R should be collected in 1st and 2nd Quarters. Schedule b: Cash collections 1st Quarter 2nd Quarter Quarter th Quarter ear End 3rd 4 Y From current quarter's sales 1722360 1782724 1397799 0 From sales 1 quarter before 1438322 1438322 From sales 2 quarters before 1027373 1027373 Total collections 1438322 1027373 0 0 2465694 Schedule c: Purchases plan in units Desired units in ending inventory Plus units sold Total needed Less beginning inventory Required purchases 1st Quarter 2nd Quarter Quarter th Quarter ear End 3rd 4 Y 0 0 0 0 0 0 0 0 0 0 0 0 0 Schedule d: Purchases plan (dollars) Weighted average unit cost 1st Quarter 2nd Quarter Quarter th Quarter ear End 3rd 4 Y Desired ending inventory Plus cost of goods sold Total requirements Less beginning inventory Total purchases 0 0 0 0 0 0 0 0 0 0 0 0 0 Note: Actual A/P should be paid in 1st Quarter only. Schedule e: Cash pmts for purchases 1st Quarter 2nd Quarter Quarter th Quarter ear End 3rd 4 Y From accounts payable (12/31) 0 1st Quarter 0 2nd Quarter 0 3rd Quarter 0 4th Quarter 0 Total payments 0 0 0 0 0 UNIT SALES Actual Month 3rd Quarter th Quarter 4 TOTAL 16310 17790 PID-B 16310 16011 PID-P 0 1779 TOTAL 16310 17790 Sales Mix % PID-B PID-P 0.9 0.1 Sales Mix Sensitivity -----------> 0.03 0.04 0.05 0.87 0.83 0.78 0.13 0.17 0.22 215 215 380 Sales Price Sensitivity -----------> 0 -0.01 -0.02 -0.02 -0.03 -0.03 212.85 208.593 204.4211 198.2885 192.3399 376.2 368.676 361.3025 350.4634 339.9495 160 150 220 Unit Cost Sensitivity -----------> 150 150 150 220 220 220 1 0 Unit selling price PID-B PID-P Unit cost PID-B PID-P Unit Volume Sensitivity -----------> 0 Forecast 1st Quarter 2nd Quarter Quarter th Quarter st Quarter 3rd 4 1 18600 21600 23400 25500 27900 16182 17928 18252 18615 19251 2418 3672 5148 6885 8649 18600 21600 23400 25500 27900 Wtd Average Sales Price 231.5 Wtd Average Unit Cost 157 Gross Profit % PID-B 0.302326 PID-P 0.421053 Wtd Average.321814 0 Operating expenses Sales commissions (% of sales price) Royalties (% of sales price) Shipping & handling (per unit) Wages and salaries(quarterly) Rent (quarterly) Utilities (quarterly) Insurance expired (quarterly) Depreciation (quarterly) Other S&A (quarterly) Advertising Campaign (quarterly) Fixed Assets Purchases (quarterly) Dividends Declared(quarterly) LOC Interest rate (annual) Other Assumptions Effective income tax rate Ending cash balance per quarter Ending inventory as percentage of next quarter's sales Percentage of purchases paid in: Current quarter Following quarter Percentage of sales collected in: Current quarter Following quarter Second following quarter 234.0855 235.8071 159.1 161.9 0.05 0.73 0.27 150 220 0 0.04 0.69 0.31 0 150 220 238.935 239.3757 238.0988 165.4 168.9 171.7 0.295278 0.280896 0.266221 0.243526 0.22013 0.415205 0.40327 0.391092 0.37226 0.352845 0.320334 0.313422 0.307762 0.294415 0.278871 1st Quarter 2nd Quarter Quarter th Quarter st Quarter 3rd 4 1 0.05 0.05 0.05 0.05 0.05 0.04 0.04 0.04 0.04 0.05 7 7 7 9 9 388000 400000 420000 425000 440000 42000 42000 46000 46000 46000 8700 9000 9200 9450 9700 7500 7500 7500 7500 7500 38000 40000 48000 52000 54000 110000 120000 120000 130000 145000 0 0 0 0 0 0 105000 20000 40000 0 20000 20000 20000 20000 20000 0.08 0.08 0.08 0.08 0.08 0.3 40000 SENSITIVITY SUMMARY RESULTS NOI = 0 Net Income = 0 Line of Credit = 0 0.65 0.5 0.5 0.4 0.35 0.25 Part 2: (B3a) Inventory Turnover = 0 Part 2: (B3b) Days Sales in acct. rec. = ENTER FORMULA UNIT SALES Actual Month 3rd Quarter th Quarter 4 TOTAL 16310 17790 PID-B 16310 16011 PID-P 0 1779 TOTAL 16310 17790 Sales Mix % PID-B PID-P 0.9 0.1 Sales Mix Sensitivity -----------> 0.03 0.04 0.05 0.87 0.83 0.78 0.13 0.17 0.22 215 215 380 Sales Price Sensitivity -----------> 0 -0.01 -0.02 -0.02 -0.03 -0.03 212.85 208.593 204.4211 198.2885 192.3399 376.2 368.676 361.3025 350.4634 339.9495 160 150 220 Unit Cost Sensitivity -----------> 150 150 150 220 220 220 1 0 Unit selling price PID-B PID-P Unit cost PID-B PID-P Unit Volume Sensitivity -----------> 0 Forecast 1st Quarter 2nd Quarter Quarter th Quarter st Quarter 3rd 4 1 18600 21600 23400 25500 27900 16182 17928 18252 18615 19251 2418 3672 5148 6885 8649 18600 21600 23400 25500 27900 Wtd Average Sales Price 231.5 Wtd Average Unit Cost 157 Gross Profit % PID-B 0.302326 PID-P 0.421053 Wtd Average.321814 0 Operating expenses Sales commissions (% of sales price) Royalties (% of sales price) Shipping & handling (per unit) Wages and salaries(quarterly) Rent (quarterly) Utilities (quarterly) Insurance expired (quarterly) Depreciation (quarterly) Other S&A (quarterly) Advertising Campaign (quarterly) Fixed Assets Purchases (quarterly) Dividends Declared(quarterly) LOC Interest rate (annual) Other Assumptions Effective income tax rate Ending cash balance per quarter Ending inventory as percentage of next quarter's sales Percentage of purchases paid in: Current quarter Following quarter Percentage of sales collected in: Current quarter Following quarter Second following quarter 234.0855 235.8071 159.1 161.9 0.05 0.73 0.27 150 220 0 0.04 0.69 0.31 0 150 220 238.935 239.3757 238.0988 165.4 168.9 171.7 0.295278 0.280896 0.266221 0.243526 0.22013 0.415205 0.40327 0.391092 0.37226 0.352845 0.320334 0.313422 0.307762 0.294415 0.278871 1st Quarter 2nd Quarter Quarter th Quarter st Quarter 3rd 4 1 0.05 0.05 0.05 0.05 0.05 0.04 0.04 0.04 0.04 0.05 7 7 7 9 9 388000 400000 420000 425000 440000 42000 42000 46000 46000 46000 8700 9000 9200 9450 9700 7500 7500 7500 7500 7500 38000 40000 48000 52000 54000 110000 120000 120000 130000 145000 0 0 0 0 0 0 105000 20000 40000 0 20000 20000 20000 20000 20000 0.08 0.08 0.08 0.08 0.08 0.3 40000 SENSITIVITY SUMMARY RESULTS NOI = 0 Net Income = 0 Line of Credit = 0 0.65 0.5 0.5 0.4 0.35 0.25 Part 2: (B3a) Inventory Turnover = 0 Part 2: (B3b) Days Sales in acct. rec. = ENTER FORMULA
