Case#1 Travel-Space Trailers MacKenzie Maddox-2

.xlsx

School

Alabama A&M University *

*We aren’t endorsed by this school

Course

ACC-450

Subject

Management

Date

Apr 3, 2024

Type

xlsx

Pages

Uploaded by KidTigerPerson17

Travel-space Trailers Case Study - Instructions & Rubric Read through the case thoroughly it is provided in a separate document. Items to submit: 1. Excel workbook with all speadsheets showing all 9 budgets' calculations. (Items 2 and 4) 2. Written report with well reasoned responses to all discussion questions. Number your responses to match e 3. Visualization Charts (Item 3) on separate sheet to accompany the letter in item #6. 4. Professional letter addressed to the owner, Paul, with your recommendations. (Item 6) After preparing the budgets respond to the following items: Case Questions - Perform the required calculations and discuss each question in order. You must draw upon re complete this assignment successfully. Be sure to answer questions completely, but succinctly. Reference the this case to support your response(s) and cite any material you rely on when applicable. Be sure to link all of y workbook. Doing so will help you be efficient with your time and it will be a great help when you prepare the s you complete the first budget, I recommend that you copy that spreadsheet to another sheet in the workbook the second set of budgets and again for the third set of budgets. If you do this, please delete the ones I have la and rename your sheets accordingly. There are a lot of videos on YouTube demonstrating how to link and cop your instructor or the lab tutor if you need assistance. See list of items to submit below. 1. Draw upon research and course materials to discuss the validity and reasonableness of Travel-Space Trail 2. Prepare production, purchasing (aluminum), and cash budgets based on projections for Travel-Space Tra months of 2021 using the Excel spreadsheet provided (60 points). Hints: Use $8 per square yard of aluminum budget estimates. $15 per yard is the cost of aluminum in the current finished goods inventory. Also, rememb required to maintain a cash balance of $100,000. When the account falls short, Travel-Space must draw from repay when they have an excess of cash (above $100,000). These calculations need to be added to the bottom a. Discuss any issues you suspect may exist in Travel-Space Trailers’ cost estimates, including the costs of alu and selling and administration costs. b. Discuss any issues you suspect may exist in Travel-Space Trailers’ financing plans. c. Discuss any issues you notice in Travel-Space Trailers’ procedures for preparing budgets. 3. Using the budgets you prepared for the previous question, produce three or more charts/graphs visualizin be the most important data to consider, such as: a. Trailer production during the first six months of the year. b. Cash collections and disbursements during the first six months of the year. c. Explain how each of these visualizations illustrates one or more of the issues you raised in the previous qu 4. Prepare a second and third set of budgets at the 3,000 and 3,500 level of units using the stable (constant) suggested by Yun. Note: These should be placed on separate spreadsheets in your workbook and labeled. Prep purchasing (aluminum), and cash budgets. Hold monthly production constant for the two constant levels sugg helpful to change the format of the production budget to begin with budgeted production rather than budget each for preparing 6 additional budgets).

5. What changes, if any, would you recommend Travel-Space Trailers make to its budgeting procedures, its s production strategies, and its performance evaluation system to improve financial performance, company cult between members of the executive team? 6. Owner Paul is aware that Travel-Space Trailers is experiencing difficulties, and he has hired you, an indepe investigate. Write a letter to Paul explaining the recommendations you developed in requirement 5. Paul is no lots of raw numbers, so develop and include in your letter data visualizations that will convince him to interven changes that you recommend. Be sure to address whether the 20% increase is reasonable or not.

each question. (Items 1, 2a, 2b, 2c, 3c, 5) esearch and coursework to e analysis you perform related to your calculations in the Excel second and third budgets. After k and edit the numbers to create abeled at the 3,000 and 3,500 level py spreadsheets or you can contact lers’ sales projections. ailers for the first six across the board for all ber that the company is their line of credit and m of each cash budget. uminum, equipment, ng what you consider to uestions. ) production schedule pare the production, gested by Yun. It will be ted sales. (10 points

sales strategies, its ture, and relationships endent consultant, to ot likely to respond to ne and make the

EXHIBIT 1. ACTUAL AND PROJECTED SALES IN NUMBER OF TRAILERS Actual Sales 2015 2016 2017 2018 2019 2020 13,765 14,880 15,991 17,809 19,634 23,322 Projected Sales 2021 2022 2023 2024 2025 28,000 33,600 40,320 48,384 58,060 EXHIBIT 2. FINISHED GOODS INVENTORY Budget: December 31, 2020 1,000 trailers Current finished goods 300 trailers *plus* inventory formula + 20% of the next month's sales EXHIBIT 3. SHEET ALUMINUM Trailer requirements 30 square yards Cost per square yard in the Spring $ 15.00 Cost per square yard in December/January $ 8.00 EXHIBIT 4. SHEET ALUMINUM BUDGETED COST $ 8.00 cost per square yard EXHIBIT 5. RAW MATERIALS INVENTORY Sheet aluminum on hand 50.0% of the following month's at the end of each month production needs Raw materials inventory budget 39,000 square yards December 31, 2020

EXHIBIT 6. BUDGETED EXPENSES FOR THE FIRST SIX MONTHS 2021 January February March Aluminum $ 816,000 $ 1,056,000 $ 888,000 Other materials 54,000 264,000 222,000 Wages 624,000 1,008,000 1,104,000 Heat, light, & power 130,000 195,000 220,000 Equipment rental 390,000 390,000 390,000 Equipment purchases 300,000 300,000 300,000 Depreciation 250,000 250,000 250,000 Selling & admin 400,000 400,000 400,000 April May June Aluminum $ 552,000 $ 336,000 $ 240,000 Other materials 138,000 84,000 90,000 Wages 672,000 432,000 240,000 Heat, light, & power 135,000 110,000 110,000 Equipment rental 340,000 340,000 340,000 Equipment purchases 300,000 300,000 300,000 Depreciation 275,000 275,000 275,000 Selling & admin 400,000 400,000 400,000 EXHIBIT 7. ACCOUNTS PAYABLE FOR ALUMINUM AND OTHER MATERIALS PURCHASED DURING DECEMBER 2020 A/P: Aluminum $ 700,000 A/P: Other materials $ 150,000 EXHIBIT 8. BANK LOAN DETAILS / MINIMUM CASH BALANCE REQUIRED Requested loan amount $ 800,000 Loan duration (in days) 90 Mininum cash balance required by directors $ 100,000

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version