1. Read the case
2. Use the exhibits in the Excel file to prepare a six-month budget. Use the blanks tab in the Excel file to prepare:
a. Sales budget
b. Production budget (note: ending inventory should be 300 units + 20% of next month’s sales)
c. Purchases budget
d. Cash budget (note the collections pattern for sales is provided and accounts payable is paid the following month Exhibit 7 provides beginning accounts payable balances). The company wants to maintain a $100,000 cash balance, so you may need to borrow to maintain the desired balance.
3. Based on the descriptions of the budgeting process for Travel-Space Trailers, recommend ways in which the budgeting process could be improved.
EXHIBITS
| ORIGINAL CASE FACTS | ||||||||||||||||||||
| Do not make changes to these numbers | ||||||||||||||||||||
| EXHIBIT 1. | EXHIBIT 6. | EXHIBIT 9. | ||||||||||||||||||
| ACTUAL AND PROJECTED SALES IN NUMBER OF TRAILERS | BUDGETED EXPENSES FOR THE FIRST SIX MONTHS 2021 | ACTUAL 2020 AND PROJECTED 2021 SALES | ||||||||||||||||||
| Actual Sales | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | January | February | March | 2020 | 2021 | |||||||||
| 13,765 | 14,880 | 15,991 | 17,809 | 19,634 | 23,322 | Aluminum | $ 816,000 | $ 1,056,000 | $ 888,000 | Actual | Projected | |||||||||
| Other materials | 54,000 | 264,000 | 222,000 | January | 1,983 | 2,500 | ||||||||||||||
| Projected Sales | 2021 | 2022 | 2023 | 2024 | 2025 | Wages | 624,000 | 1,008,000 | 1,104,000 | February | 3,218 | 4,000 | ||||||||
| 28,000 | 33,600 | 40,320 | 48,384 | 58,060 | Heat, light, & power | 130,000 | 195,000 | 220,000 | March | 3,981 | 5,000 | |||||||||
| Equipment rental | 390,000 | 390,000 | 390,000 | April | 3,240 | 3,000 | ||||||||||||||
| Equipment purchases | 300,000 | 300,000 | 300,000 | May | 1,755 | 2,000 | ||||||||||||||
| EXHIBIT 2. | Depreciation | 250,000 | 250,000 | 250,000 | June | 901 | 1,000 | |||||||||||||
| FINISHED GOODS INVENTORY | Selling & admin | 400,000 | 400,000 | 400,000 | July | 763 | 1,000 | |||||||||||||
| Budget: December 31, 2020 | 1,000 | trailers | August | 611 | 1,000 | |||||||||||||||
| April | May | June | September | 1,622 | 2,000 | |||||||||||||||
| Current finished goods | 300 | trailers *plus* | Aluminum | $ 552,000 | $ 336,000 | $ 240,000 | October | 1,678 | 2,000 | |||||||||||
| inventory formula | + | 20% | of the next month's sales | Other materials | 138,000 | 84,000 | 90,000 | November | 1,439 | 2,000 | ||||||||||
| Wages | 672,000 | 432,000 | 240,000 | December | 2,131 | 2,500 | ||||||||||||||
| Heat, light, & power | 135,000 | 110,000 | 110,000 | Total Trailers | 23,322 | 28,000 | ||||||||||||||
| EXHIBIT 3. | Equipment rental | 340,000 | 340,000 | 340,000 | ||||||||||||||||
| SHEET ALUMINUM | Equipment purchases | 300,000 | 300,000 | 300,000 | ||||||||||||||||
| Trailer requirements | 30.0 | square yards | Depreciation | 275,000 | 275,000 | 275,000 | EXHIBIT 10. | |||||||||||||
| Selling & admin | 400,000 | 400,000 | 400,000 | ACTUAL AND BUDGETED SALES | ||||||||||||||||
| Cost per square yard in the Spring | $ 15.00 | November 2020 (actual) | $ 1,439,000 | |||||||||||||||||
| Cost per square yard in December/January | $ 8.00 | December 2020 (actual) | $ 2,131,000 | |||||||||||||||||
| EXHIBIT 7. | January 2021 (budgeted) | $ 2,500,000 | ||||||||||||||||||
| ACCOUNTS PAYABLE FOR ALUMINUM AND OTHER MATERIALS | February 2021 (budgeted) | $ 4,000,000 | ||||||||||||||||||
| EXHIBIT 4. | PURCHASED DURING DECEMBER 2020 | March 2021 (budgeted) | $ 5,000,000 | |||||||||||||||||
| SHEET ALUMINUM BUDGETED COST | A/P: Aluminum | $ 700,000 | April 2021 (budgeted) | $ 3,000,000 | ||||||||||||||||
| $ 8.00 | cost per square yard | A/P: Other materials | $ 150,000 | May 2021 (budgeted) | $ 2,200,000 | |||||||||||||||
| June 2021 (budgeted) | $ 1,100,000 | |||||||||||||||||||
| EXHIBIT 5. | EXHIBIT 8. | EXHIBIT 11. | ||||||||||||||||||
| RAW MATERIALS INVENTORY | BANK LOAN DETAILS / MINIMUM CASH BALANCE REQUIRED | ACCOUNTS RECEIVABLE COLLECTION SCHEDULE | ||||||||||||||||||
| Sheet aluminum on hand | 50.0% | of the following month's | Requested loan amount | $ 800,000 | Of a month's sales collected in: | |||||||||||||||
| at the end of each month | production needs | Loan duration (in days) | 90 | Month of sale | 25% | |||||||||||||||
| Month following sale | 10% | |||||||||||||||||||
| Raw materials inventory budget | 39,000 | square yards | Mininum cash balance required by directors | $ 100,000 | Second month following sale | 60% | ||||||||||||||
| December 31, 2020 | – The remainder is uncollectible – | |||||||||||||||||||
| EXHIBIT 12. | ||||||||||||||||||||
| SUGGESTED CONSTANT PRODUCTION LEVELS | ||||||||||||||||||||
| First suggested level | 3,000 | |||||||||||||||||||