professional application 2 budget project ottawa orientation company the ottawa orientation company is a high tech subsystem production business that produces technology to enable driverless operation of automobiles and other vehicles the design of o 1
Professional Application #2
BUDGET PROJECT
Ottawa Orientation Company
The Ottawa Orientation Company is a high-tech subsystem production business that produces technology to enable driverless operation of automobiles and other vehicles. The design of Ottawa Orientation products are unique and represent a breakthrough in the industry. The units Ottawa Orientation produce claim to provide for greater dependability and safety. The company is completing its third year of operations and is preparing to create a master budget for next year, 2021. 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 2021. 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
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 Ottawa Orientation Company. 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.. 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 2021 is on the 2019 balance sheet provided on the PROJECT INFORMATION worksheet. Your budget must take this information into consideration in creating the 2021 budget.
- You do not have any information about the costs in 2019, 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:
- sales budget
- production budget
- direct materials purchases budget
- direct labor budget
- overhead budget
- selling and administrative expense budget
- schedule of cash collections
- schedule of cash payments
- cash budget based on operations (see below for Discretionary Use of Cash segment)
NOTE: The additional tabs in the budget spreadsheet provided are for the second half of the project. Leave them blank in your submission for Professional Application #2.
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.
- Verify that the row calculations across the four quarters match the column calculations for the year as a whole in the last column. The light red 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.
- 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 Ottawa Orientation Company, taking into account both the CEO’s plans and your analysis of the forecasted budgets you create. Be sure to enter your recommendations on the CUSTOMIZATION 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 recommendation on the CUSTOMIZATION worksheet.
Complete the Discretionary Uses of Cash section of the Cash Budget based on cell references to the PROJECT INFORMATION 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.
NOTE: Your cash budget cannot have a negative cash balance in any quarter because that means your company is bankrupt and out of business. Adjust the capital expenditures and dividends so that you meet the company policy of minimum cash balance 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.
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
Input (5)
Student’s WIN and recommendation for capital expenditures and dividends appear on the INPUT tab.
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.
