Budgeting Case Analysis

Travel-SpaceTrailersBudgetingCase1.xlsx

Home >Business & Finance homework help >Accounting homework help >Budgeting Case Analysis

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
												Second suggested level		3,500

Budget

PRODUCTION BUDGET								Steps/Hints
		Feb	March	April	May	June	Six Months	1. Use sales from Exhibit 9 (units) and Sales dollars - Exhibit 10
Budgeted sales								2. Use desired inventory from Exhibit 2
Add: desired ending inventory								3. Beginning inventory is given in Exhibit 2, and beginning inventory = prior month ending inventory
Total needs								4. Sheet metal required is Exhibit 3 and cost is provided in Exhibit 4
Less: beginning inventory								5. Ending iinventory for raw materials provided in Exhibit 5
Trailer production								6. Collections pattern is provided in Exhibit 11
								7. Expenses are provided in Exhibit 6 and Exhibit 7 provides payment information on accounts payable (materials purchases)
PURCHASES BUDGET								8. Borrowing and minimum cash balance provided in Exhibit 8. Balance at beginning of year is at minimum
	Jan	Feb	March	April	May	June	Six Months
Trailer production
Sheet metal needs per trailer
Total production needs
Add: desired ending inventory
Total materials needs
Less: beginning inventory
Total sheet metal purchases
Cost per square yard
Total cost
CASH BUDGET
	Jan	Feb	March	April	May	June	Six Months
Cash beginning balance
Add: cash collections
Month of sale
Month following sale
Second month following sale
Total cash collections
Total cash available
Less: cash disbursements
Aluminum	$ - 0
Other materials
Wages
Heat, light, & power
Equipment rental
Equipment purchases
Selling & admin
Total cash disbursements
Excess (deficiency)
Financing:
Borrowings: Original
Borrowings: Additional
Repayments
Interest
To be invested (excess)
Total financing	$ - 0
Cash balance ending	$ - 0