Bombs Away Video Games Corporation has forecasted the following monthly sales:

January $ 105,000
July $ 50,000
February 98,000
August 50,000
March 30,000
September 60,000
April 30,000
October 90,000
May 25,000
November 110,000
June 40,000
December 128,000

Total annual sales = $816,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, 20 percent are for cash and 80 percent are on account. All accounts receivable are collected in the month after the sale is made.

a. Construct a monthly production and inventory schedule in units. Beginning inventory in January is 30,000 units.

b. Prepare a monthly schedule of cash receipts. Sales in December before the planning year are $100,000.

c. Prepare a cash payments schedule for January through December. The production costs of $2 per unit are paid for in the month in which they occur. Other cash payments, besides those for production costs, are $50,000 per month.

d. Prepare a monthly cash budget for January through December using the cash receipts schedule from part b and the cash payments schedule from part c. The beginning cash balance is $5,000, which is also the minimum desired. (Negative amounts should be indicated by a minus sign.)

I have attached the excel file which shows the entire calculation of all the parts of your question. Majority of the numbers are linked so that its easy for you to understand. Key points before you look into file.

1) Number of units have derived by dividing revenue over sales price per unit which is $5

2) Production units are calculated by dividing total sales unit by 12 (as mentioned in the question)

3)Keep in mind that 80% of revenue will be received next month of the sales therefore also include in January sales receipt the 80% revenue of previous month

Explanation:

please find the answer below

Explanation:

(a)Annual sales = $816, 000

Selling price = $5

Hence, number of units to be sold in a year = $816, 000 / $5

= 163, 200

Monthly production = 163, 200 / 12

= 13, 600 units

Month Production and inventory schedule:

Month Sales ($) Sales (units) Beginning inventory Production  Ending inv

Jan   105, 000 21, 000  30, 00013, 60022, 600

Feb   98, 000   19, 600  22, 60013, 60016, 600

Mar   30, 000  6, 000  16, 60013, 60024, 200

Apr   30, 000  6, 000  24, 20013, 60031, 800

May   25, 000  5, 000  31, 80013, 60040, 400

Jun   40, 000  8, 000  40, 40013, 60046, 000

Jul   50, 000  10, 000  46, 00013, 60049, 600

Aug   50, 000  10, 000  49, 60013, 60053, 200

Sep   60, 000  12, 000  53, 20013, 60054, 800

Oct    90, 000  18, 000  54, 80013, 60050, 400

Nov   110, 000  22, 000  50, 40013, 60042, 000

Dec   128, 000  25, 600  42, 000 13, 60030, 000

(b)Schedule of cash receipts:

Mon

Sales

‘000JanFebMarAprMayJunJulAugSepOctNovDec

Dec10080,000            

Jan10521,00084,000          

Feb98  19,60078,400          

Mar30  6,00024,000        

Apr30    6,00024,000        

May25    5,00020,000      

Jun40      8,00032,000      

Jul50      10,00040, 000    

Aug50        10, 00040, 000    

Sep60        12, 00048,000  

Oct90          18,00072,000  

Nov110          22,00088,000

Dec128            25,600

Total101,

000103,  

60084,  

40030, 00029,  

00028, 00042, 00050,  

00052, 00066,  

00094,  

000113,  

600

(c)Cash payment schedule

JanFebMarAprMayJunJulAugSepOctNovDec

Product-

ion  

costs27, 20027, 20027, 20027, 20027, 20027, 20027, 20027, 20027, 20027, 20027, 20027, 200

Other  

Costs50, 00050, 00050, 00050, 00050, 00050, 00050, 00050, 00050, 00050, 00050, 00050, 000

Total

costs77, 20077, 20077, 20077, 20077, 20077, 20077, 20077, 20077, 20077, 20077, 20077, 200

(d)Cash Budget

Jan

FebMarAprMayJunJulAugSepOctNov

Dec

Opening

balance5, 00028, 80055, 20062, 00014, 800-32, 400-80, 600-129,

000-165, 000-192, 200-217, 400-200, 600

Cash  

receipts101, 000103, 60084, 00030, 00030, 00029, 00028, 00042, 00050, 00052, 00094, 000113, 600

Cash  

Payments77, 20077, 20077, 20077, 20077, 20077, 20077, 20077, 20077, 20077, 20077, 20077, 200

Closing

Balance28, 80055, 20062, 00014, 800-32, 400-80, 600-129, 800-165, 000-192, 200-217, 400-200,600-164, 200

Minimum

Balance

   5,0005,0005,0005,0005,0005,0005,0005,000

Bank loan    27, 40075, 600124, 800160, 000187,

200214, 400195, 600159, 200