Solution
Khushboo answered on
Feb 08 2024
Instructions
Travel-space Trailers Case Study - Instructions & Ru
ic
Read through the case thoroughly it is provided in a separate document.
Case Questions - Perform the required calculations and discuss each question in order. You must draw upon research and coursework to complete this assignment successfully. Be sure to answer questions completely, but succinctly. Reference the analysis you perform related to this case to support your response(s) and cite any material you rely on when applicable. Be sure to link all of your calculations in the Excel workbook. Doing so will help you be efficient with your time and it will be a great help when you prepare the second and third budgets. After you complete the first budget, I recommend that you copy that spreadsheet to another sheet in the workbook and edit the numbers to create the second set of budgets and again for the third set of budgets. If you do this, please delete the ones I have labeled at the 3,000 and 3,500 level and rename your sheets accordingly. There are a lot of videos on YouTube demonstrating how to link and copy spreadsheets or you can contact your instructor or the lab tutor if you need assistance. See list of items to submit below.
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 each question. (Items 1, 2a, 2b, 2c, 3c, 5)
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)
1.      Draw upon research and course materials to discuss the validity and reasonableness of Travel-Space Trailers’ sales projections.
2.      Prepare production, purchasing (aluminum), and cash budgets based on projections for Travel-Space Trailers for the first six months of 2021 using the Excel spreadsheet provided (60 points). Hints: Use $8 per square yard of aluminum across the board for all budget estimates. $15 per yard is the cost of aluminum in the cu
ent finished goods inventory. Also, remember that the company is required to maintain a cash balance of $100,000. When the account falls short, Travel-Space must draw from their line of credit and repay when they have an excess of cash (above $100,000). These calculations need to be added to the bottom of each cash budget.
After preparing the budgets respond to the following items:
a.      Discuss any issues you suspect may exist in Travel-Space Trailers’ cost estimates, including the costs of aluminum, equipment, 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 visualizing what you consider to 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 questions.
4.      Prepare a second and third set of budgets at the 3,000 and 3,500 level of units using the stable (constant) production schedule suggested by Yun. Note: These should be placed on separate spreadsheets in your workbook and labeled. Prepare the production, purchasing (aluminum), and cash budgets. Hold monthly production constant for the two constant levels suggested by Yun. It will be helpful to change the format of the production budget to begin with budgeted production rather than budgeted sales. (10 points each for preparing 6 additional budgets).
5.      What changes, if any, would you recommend Travel-Space Trailers make to its budgeting procedures, its sales strategies, its production strategies, and its performance evaluation system to improve financial performance, company culture, and relationships between members of the executive team?
6.      Owner Paul is aware that Travel-Space Trailers is experiencing difficulties, and he has hired you, an independent consultant, to investigate. Write a letter to Paul explaining the recommendations you developed in requirement 5. Paul is not likely to respond to lots of raw numbers, so develop and include in your letter data visualizations that will convince him to intervene and make the changes that you recommend. Be sure to address whether the 20% increase is reasonable or not.
Data Files
STUDENT FILES
Copy and paste values from exhibits below into Excel worksheets as appropriate to make calculations and build budgets. Adjustment
0%
EXHIBIT 1. EXHIBIT 6. EXHIBIT 9. Applies a constant
ACTUAL AND PROJECTED SALES IN NUMBER OF TRAILERS BUDGETED EXPENSES FOR THE FIRST SIX MONTHS 2021 ACTUAL 2020 AND PROJECTED 2021 SALES percentage change
Actual Sales 2015 2016 2017 2018 2019 2020 January Fe
uary March 2020 2021 to all units and costs.
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 Enter positive
Projected Sales 2021 2022 2023 2024 2025 Wages 624,000 1,008,000 1,104,000 Fe
uary 3,218 4,000 number for increase
28,000 33,600 40,320 48,384 58,060 Heat, light, & power 130,000 195,000 220,000 March 3,981 5,000 or negative numbe
Equipment rental 390,000 390,000 390,000 April 3,240 3,000 for decrease.
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
Cu
ent 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, &...