In: Accounting
Using the below information open an excel file and create a Sales Budget, a Cash Receipt Schedule and a Production Budget for Fiwrt for the months of Oct, Nov, and Dec. Make sure to use proper format including dollar signs and headers - this will count in the grade. Upload the excel file as your response.
Fiwrt Corporation manufactures and sells stainless steel coffee mugs. Expected mug sales (in units) are expected to be as follows: September – 32,000, October – 30,000, November – 36,000, December – 34,000, and January 35,000.
Selling Price is expected to be $8 per mug. 40% of sales are on account. All sales on account are collected in the following month. Fiwrt likes to maintain a finished goods inventory equal to 30% of the next month's estimated sales. |
Solution:
Sales Budget - Fiwrt | ||||
Particulars | October | November | December | Total |
Sales units | 30000 | 36000 | 34000 | 100000 |
Selling price per unit | $8.00 | $8.00 | $8.00 | $8.00 |
Projected Sales Revenue | $240,000.00 | $288,000.00 | $272,000.00 | $800,000.00 |
Schedule of expected cash collections | ||||
Particulars | October | November | December | Total |
Cash sales (60%) | $144,000.00 | $172,800.00 | $163,200.00 | $480,000.00 |
Collction of sales on account | $102,400.00 | $96,000.00 | $115,200.00 | $313,600.00 |
Expected cash collections | $246,400.00 | $268,800.00 | $278,400.00 | $793,600.00 |
Production Budget - Fiwrt | ||||
Particulars | October | November | December | Total |
Sales units | 30000 | 36000 | 34000 | 100000 |
Add: Desired ending inventory (30% of next month sales) | 10800 | 10200 | 10500 | 10500 |
Less: Opening Inventory | 9000 | 10800 | 10200 | 9000 |
Budgeted Production units | 31800 | 35400 | 34300 | 101500 |