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 cars 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 quarters 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 CEOs 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 havent 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 quarters 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 years 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 periods 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 wont 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 CEOs interests in growing the company and purchasing additional equipment and the CEOs 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 CEOs 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 individuals 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.