The Sustainable Systems Inc. is a long-established automotive parts supplier that reinvented its business to become a high-tech subsystem production business that produces technology to enable automob

The Sustainable Systems Inc. is a long-established automotive parts supplier that reinvented its business to become a high-tech subsystem production business that produces technology to enable automobiles to select among alternate energy sources at any time during the car’s operations.  The design of Sustainable Systems products is unique and represent a breakthrough in the industry.  The units Sustainable Systems produces claim to provide for greater dependability, quality and longevity.  The company is completing its third year of operations and is preparing to create a master budget for next year, 2022.  The budget will detail each quarter’s activities and the activity for the year in total. 

Through connections at the Rotary Club, the CEO was able obtain a template for use in creating a master budget and has provided it to you along with financial information from the corporate controller.  Your job is to create the initial documents for the master budget:

·       sales budget

·       production budget

·       direct materials purchases budget

·       direct labor budget

·       overhead budget

·       selling and administrative expense budget

·       cash budget include a schedule of cash collections and payments

At that point you will present your results at the budget committee meeting before continuing to complete all of the documents needed.  In particular, the CEO would like you to have determined whether the company is projected to have enough cash to purchase additional plant and equipment and to expand its operations to begin paying dividends each quarter.  The CEO’s top priority is to purchase plant and equipment with a cost of $5,450,000 in the fourth quarter of 2022.   Secondly, the CEO has been taking a salary from the company, but the other 9 shareholders haven’t received any return on their initial investment in the company yet and would like to achieve a regular 10% rate of return on their initial stock investment, although they realize that it may take a few more years to achieve that level.

REQUIRED for Professional Application #2 Part 1:  CHECKPOINT

A.  Preparation

Download the Budget Spreadsheet from D2L and enter your WIN on the INPUT worksheet, one number per column.  This step sets up your unique assignment. 

You will also enter your recommendation for each quarter’s capital expenditures and dividends on the INPUT worksheet.  Note that you are expected to consider various levels of capital expenditures and dividends before deciding on a recommendation, so enter preliminary amounts and adjust the figures when you are further along in the project.  However, at the time you submit your project, the level of capital expenditures and dividends on the INPUT worksheet should match your recommendations.

B.   Understanding the Company

Carefully browse the PROJECT INFORMATION worksheet for the data you need to create this year’s budgets for Sustainable Systems Inc.  I suggest that you print out this worksheet with the column letters and row numbers to aid your creating and reviewing the budgets.

Budget assumptions to use in preparing the budget are included on the PROJECT INFORMATION worksheet and are listed here:

·       The company policy is to keep a raw material inventory at 25% of the requirements for the next period’s production.

·       The company policy is to produce enough units of finished goods at the end of the month to ease its production in the next quarter based on its overall experience.  The specific numbers are included on the Project Information worksheet, so this will NOT be computed within the budget.

·       The company policy is to allocate variable overhead to products based on direct labor hours.

·       For absorption reporting purposes, fixed overhead is allocated equally to each unit expected to be produced.

·       The company has a policy of writing off outstanding accounts receivable after 90 days.  This is implemented in the budget by assuming that the projected uncollectible receivables are written off each quarter as a decrease in accounts receivable and a decrease in income as bad debt expense.

·       The company expects its historic trends in collections from customers, payments to raw material suppliers, and relationship of variable selling & administrative costs to continue into 2022.  Those percentages are included on the PROJECT INFORMATION worksheet.

·       All other cash distributions, except to raw material suppliers, are paid in the quarter of purchase or employee work.

·       The company policy is to maintain a minimum cash balance of $250,000 each quarter.

·       The treasury function of the company uses sophisticated investing techniques to earn the most it can on available cash.  This is implemented in the budget by assuming that the company earns interest on the entire balance in cash at the end of each quarter for the entire quarter

·       The information about the values of assets, liabilities, and owners’ equity at the beginning of 2022 is on the 2021 balance sheet provided on the PROJECT INFORMATION worksheet.  Your budget must take this information into consideration in creating the 2022 budget.

·       You do not have any information about the costs in 2021, other than those explicitly included on the PROJECT INFORMATION worksheet (and you won’t need them).

C.   Budgets

Create the following budgets / calculations on the appropriate worksheets:

1.     sales budget

2.     production budget

3.     direct materials purchases budget

4.     direct labor budget

5.     overhead budget

6.     selling and administrative expense budget

7.     schedule of cash collections

8.     schedule of cash payments

9.     cash budget based on operations (see below for Discretionary Use of Cash segment)

NOTE:  The additional tabs in the Budget Template are for the second half of the project.  Leave them blank in your submission for Professional Application #2 Checkpoint.

D.  Review

Review your budgets to be sure that your work is accurate and flexible.  You must code cells with formulas and cell references only.  You should also look for consistency and reasonableness throughout your budgets.

Two ways to check your work are built into the spreadsheet.

1.     Verify that the row calculations across the four quarters match the column calculations for the year as a whole in the last column.  The bright yellow boxes just outside each table help to provide this check.

NOTE:  The EXCEL Template is already coded to put these figures on the Checkfigures worksheet.

2.     The blue questions that appear on many spreadsheets check for common errors.  Use these clues to go over your work and resolve any problems identified.  The checks are not helpful until you have completed all of the cells in a table.

E.  Decision-making

Once you have completed the basic budgets, consider the CEO’s interests in growing the company and purchasing additional equipment and the CEO’s interests in providing returns to shareholders as dividends.

1.  Determine the level of capital expenditures and the timing of capital expenditures that you would recommend to Sustainable Systems Inc., taking into account both the CEO’s plans and your analysis of the forecasted budgets you create.  Be sure to enter this information on the INPUT worksheet.

2.  Determine the level of dividends and the timing of dividends that you would recommend to Inland Instrumentation Company, taking into account both the shareholders’ plans and your analysis of the forecasted budgets you create.  Be sure to enter this information on the INPUT worksheet.

Complete the Discretionary Uses of Cash section of the Cash Budget based on cell references to the INPUT worksheet.  This will affect the ending cash balances for each quarter and for the year, of course, as well as changing the interest earned each quarter.

Remember that each individual’s budget project numbers will vary in this assignment, so your budgets will not match those of other students, and you may make different recommendations for capital expenditures and dividends from others in the class.

F.  Writing

Write a letter to the CEO that describes the budgets that you have created and how the company can use this information to decide on discretionary uses of cash.  Include in your memo your recommendation for capital expenditures and for dividends to be paid in 2022 and support your recommendation using the information in your budgets.  Remember that the CEO understands the Inland Instrumentation Company business well and developed the policies you are using in creating the budget, but the CEO has not used a formal budgeting process previously.  Use a letter format.  Your response should be no more than two pages, double-spaced, 1-inch margins, New Times Roman 12-point font with your name in the header.

F.  Submission

Submit your EXCEL spreadsheet and WORD letter to the D2L DropBox for Professional Application #2.

Summary of Spreadsheet Guidelines

You should only input cell references and EXCEL mathematical operations and functions, as needed, to complete calculations.  Do not retype any hard-coded numbers into cells.  Do not copy and paste any information from one place to another. No actual numbers are allowed in any of the budget tabs of your solution other than true constants.  For example, 4 is the number of quarters in a year, and you may want to use “4” in some formulas when adjusting from quarterly information to annual information.  But 85% is the portion of purchases paid in the month of purchase, an estimate that could be changed at any time.  You would never type or copy and paste the number 85%, but instead you would use a cell reference to the cell on the PROJECT INFORMATION worksheet that includes the percentage. The spreadsheet must be able to recalculate itself if new budget assumptions are made without changing any formulas.

Evaluation of Professional Application #2 Part 1 Checkpoint

NOTE:  This initial grade will be added to the grade on Part 2

            Input (5)

Student’s WIN and recommendation for capital expenditures and dividends appear on the INPUT tab.

            Written Response (10)

The response uses standard English and a professional tone.

The response presents the student’s recommendations for capital expenditures and dividends and provides support for his or her position.

The response includes information found in the spreadsheet the student has created.

            Accuracy of Spreadsheet  (75)

The spreadsheet accurately produces budgets.

           (5)  sales budget

           (8)  production budget

           (7)  direct materials purchases budget

           (5)  direct labor budget

           (5)  overhead budget

           (5)  selling and administrative expense budget

           (10)  schedule of cash collections

           (10)  Schedule of cash payments

           (20) cash budget

            Flexibility of Spreadsheet (10)

Data is entered only once and referenced thereafter.

All calculations are completed using spreadsheet formulas.

The spreadsheet created can be reused if there are changes in any of the elements in this analysis.