Question

Bombs Away Video Games Corporation has forecasted the following monthly sales: January February March April May June $ 101,000 $101,0 July $ 46,000 94,000 August 46,000 26,000 September 56,000 26,000 October 86,000 21,000 November 106,000 36,000 December 124,000 Total annual sales = $768,000 Bombs Away Video Games sells the popular Strafe and Capture video game. It sells for $5 per unit and costs $2 per unit to produce. A level production policy is followed. Each month's production is equal to annual sales (in units) divided by 12. Of each month's sales, 40 percent are for cash and 60 percent are on account. All accounts receivable are collected in the month after the sale is made.

Answer 1

A B C D E F G H I J K L M N O P Q R Jan 5000 28800 33800 0 33800 Feb 33800 26600 60400 Beg cash Net cash flow Cumulative cash bal Monthly loan repayment End cash bal Cum loan bal Mar 60400 -4800 55600 0 55600 Apr 55600 -45600 10000 0 10000 0 May 10000 -47600 -37600 42600 5000 42600 Jun Jul 5000 5000 -44600 -31600 -39600 -26600 44600 31600 5000 5000 87200118800 Aug 5000 -25600 -20600 25600 5000 144400 Sep 5000 -21600 -16600 21600 5000 166000 Oct 5000 -3600 1400 3600 5000 169600 Nov 5000 22400 27400 -22400 5000 147200 Dec 5000 41600 46600 -41600 5000 105600 60400 Cash receipts Cash payments 100400 71600 98200 71600 66800 71600 26000 71600 24000 71600 27000 40000 71600 71600 46000 71600 50000 71600 68000 71600 94000 71600 113200 71600

A = 19 Beg cash Net cash flow Cumulative cash bal Monthly loan repayment End cash bal Cum loan bal Jan Feb Mar Apr May Jun Jul 5000 =D9 =E9 =F9 =G9 =H9 =D14-015 =E14-E15 =F14-F15 =G14-G15 =H14-H15 =114-115 =J14-115 =SUM(D5:06) =SUM(E5:E6) =SUM(F5:F6) =SUM(G5:46) =SUM(H5:H6) =SUM(15:16) =SUM(15:36) !=IF(D7<5000,5000-D7,0) '=IF(E7<5000, 5000-E7,0) =IF(F7<5000,5000-F7,0) =IF(G7<5000,5000-G7,0) =IF(H7<5000, 5000-H7,0) =IF(17<5000, 5000-17,0) =IF(J7<5000, 5000-17,0) =SUM(D7:08) =SUM(E7:E8) =SUM(F7:F8) =SUM(G7:48) =SUM(H7:H8) =SUM(17:18) =SUM(17:18) =D8 =D10+E8 =E10+F8 =F10+G8 =G10+H8 =H10+18 =110+18 b Cash receipts C Cash payments 100400 71600 98200 71600 66800 71600 26000 71600 24000 71600 27000 71600 40000 71600

J19 f x M N O P Q Jul Aug 5 =19 =19 6 =J14-115 =K14-K15 7 =SUM(15:16) =SUM(K5:K6) 8 =IF(17<5000,5000-17,0) =IF(K7<5000,5000-K7,0) 9 =SUM(17:18) =SUM(K7:K8) 10 -110+J8 =J10+K8 11 12 Sep Oct Nov Dec =K9 =L9 =M9 EN9 =L14-L15 EM14-M15 =N14-N15 =014-015 =SUM(L5:L6) =SUM(M5:M6) =SUM(N5:N6) =SUM(05:06) =IF(L7<5000,5000-L7,0) =IF(M7<5000,5000-M7,0) =-27400+5000 -46600+5000 =SUM(L7:L8) =SUM(M7:M8) =SUM(N7:N8) =SUM(07:08) =K10+L8 =L10+M8 =M10+N8 -N10+08 13 14 40000 15 71600 16 46000 71600 50000 71600 68000 71600 94000 71600 113200 71600 18 19 20