Case Study #1 - Travel-Space Trailers: A Budgeting ExperienceIntroduction:This case study will provide students an opportunity take on the role of a consultant to advise the owner of a company...

Instructions
    Travel-space Trailers Case Study - Instructions & Rubric
    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 current 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    February    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            February        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 number
                                            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
        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, &...