Layout References Mailings Review View Help Search I X Aa Po EEEE t IDA aBbCcDd AaBb AaBbCcDo AaBbCeDd AaBbCcD Emphasis T Heading 1 Heading 2 Normal Strong e Find Replac Select Styles Editing Paragraph 2 1 7 BUDGET PROJECT- Part 1 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 representa 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 nas 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 Focus be H Stylos Editing 5 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. Layout References Mailings Review View Help Search A A A 33 211 daBbcd AaBb: ABCD Abend Aabe De Emphuis Heading 1 T Heading 2 T Normal Strong O Find Replace Select D 5 Paragraph 15 Styles Editing 1 2 . . . The company expects its historic trends in collections from customers, payments to raw material suppliers, and relationship of vanable 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 carns interest on the entire balance in cash at the end of each quartet 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 workshoot 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 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) Drocul 8 E Ottawa Orientation Company Budget Project Spring 2020 INPUT SECTION SALES (4th 4th Quarter 2020 1st Quarter and Quarter 2021 48.900 lard Quarter Quarter 2021 2021 34,500 31,000 41,000 2021 43,000 s 530 per production unit (Finished Goods) Budgeted sales in units 0 1 Budgeted Selling Price 2 3 4 RECEIVABLES 5 Receivables Collection Schedule 6 7 8 9 0 1 INVENTORY COSTS 2 Direct Lobos 3 83,00% quarter of sale 15,00% quarter following salo 2.00% uncollectible 100.00% Entire projected uncollectible receivables are written off each quarter Policy 1.5 hours 18.00 per direct labor hour S CUSTOMIZATION PROJECT INFORMATION Sales Budget Production Budget OM & DL Budge + Scroll to Throto G H S B D 1 INVENTORY COSTS 2 Direct Labor 4.5 hours 3 18.00 per direct labor hour 4 5 Raw Materials 3 pounds per finished goods unit 6 85.00 per pound 27 28 Variable Overhead $ 11:50 per Direct Labor Hour 29 30 Fixed Overhead 31 Depreciations 202,000 per quarter 32 Other Fixed Overheads 950,000 per quarter 33 Fixed Overhead Application Rate CALCULATE FROM PRODUCTION BUDGET 35 36 INVENTORY ACCOUNTS 37 Finished Goods inventory 38 Ah Quarter Ist Quarter 2nd Quarter 3rd Quarter 39 2020 2021 2021 2021 40 Ending units 0 15,000 19,000 20.000 41 42 Raw Materials Inventory 43 Beginning units on 1/1/2021 41,368 pounds 34 planned Ending units 12/21/21121 26.000 und CUSTOMIZATION PROJECT INFORMATION Sales Budget Production Budget DM &LDL Budge oth Quarter 2021 16,000 09 fo CUSTOMIZATIONIQ40 D G 11,363 pounds 26,000 pounds 25.00% of next quarters' needs for production is in ending Raw Materials Twentory 231,720 231,720 8 42 Raw Materials inventory 43 Beginning units on 1/1/2021 44 Planned Ending units on 12/31/2021 45 Policy 46 47 Work In Process Inventory 48 Beginning value 1/1/2021 S 49 Planned Ending value on 12/31/2021 S 50 51 Payment for Raw Materials 52 Payment Policy 53 54 55 OPERATING EXPENSES 56 Wages and Salaries Payment 57 Payment Policy 58 59 60 Variable Selling & Administrative Exp. 67 62 Fixed Selling & Administrative Exp 65 Depreciations 64 Other Selling & Administrative ExpS 85,00% quarter of purchase 15.00% quarter following purchase 2 times monthly on the 15th and 30th Fully paid each month 3.25% of selling price $5,000 per quarter 180,000 per quarter CUSTOMIZATION PROJECT INFORMATION Sales Budget Production Budget DM & DL Budge ad Scroll Lock A D E G H 6 CAPITAL EXPENDITURES 7 1st Duarter 2nd Quarter 3rd Quarter 4th Quarter 8 2121 2021 2021 2021 9 Equipment Purchases S0.00 $0.00 50.00 55.450,000.00 0 1 TREASURY ACTIVITIES 2 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 3 2021 2021 2021 2021 24 Dividends to be declared $0.00 SO DO SO DO $0.00 25 76 Minimum Cash Balance S 250,000 Required at end of every quarter 77 Interest Rate for larvesting (short-term) 4.00% Annually 79 Interest Policy All of the ending cash balance for the quarter earns interest for the entire quarter 30 All of the ending cash balance for the quarter remains in Cavitat the beginning of the next quarter B1 82 Interest Rate for borrowing 12.00 Annually 83 Interest Policy 10,000 increments are used in borrowing and repayment, maintaining the minimum cash balance 84 Borrowing occurs at the beginning of the quarter 35 Repayments but at the end of the year 86 87 FLEXIBLE BUDGET 88 Actual Units Produced USE VALUE FROM PRODUCTION BUDGET 89 CUSTOMIZATION PROJECT INFORMATION Sales Budget Production Budget DM & DL Budgi Stroll och 97 B . D 7 FLEXIBLE BUDGET 58 Actual Units Produced USE VALUE FROM PRODUCTION BUDGET 9 0 Flexible Budget - Analyze at Additional Production Levels 21 Production level 1 O units 22 Production level 2 O units 93 34 PRIOR YEAR RESULTS 95 Ottawa Orientation Company 96 Balance Sheet 12/31/2020 98 ASSETS 99 Cash $ 6,150,000 100 Inventory S 3,748,000 101 Accounts Receivable (net) $ 15,050,000 102 Plant and Equipment $ 29,400,000 103 S 44,348,000 104 105 LIABILITIES AND EQUITY 106 Accounts Payable S 8,100,000 107 Common Stock $ 26,234,500 108 Retained Earnings S 10,013,500 109 S 44,348,000 110 CUSTOMIZATION PROJECT INFORMATION Sales Budget Production Layout References Mailings Review View Help Search I X Aa Po EEEE t IDA aBbCcDd AaBb AaBbCcDo AaBbCeDd AaBbCcD Emphasis T Heading 1 Heading 2 Normal Strong e Find Replac Select Styles Editing Paragraph 2 1 7 BUDGET PROJECT- Part 1 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 representa 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 nas 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 Focus be H Stylos Editing 5 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. Layout References Mailings Review View Help Search A A A 33 211 daBbcd AaBb: ABCD Abend Aabe De Emphuis Heading 1 T Heading 2 T Normal Strong O Find Replace Select D 5 Paragraph 15 Styles Editing 1 2 . . . The company expects its historic trends in collections from customers, payments to raw material suppliers, and relationship of vanable 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 carns interest on the entire balance in cash at the end of each quartet 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 workshoot 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 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) Drocul 8 E Ottawa Orientation Company Budget Project Spring 2020 INPUT SECTION SALES (4th 4th Quarter 2020 1st Quarter and Quarter 2021 48.900 lard Quarter Quarter 2021 2021 34,500 31,000 41,000 2021 43,000 s 530 per production unit (Finished Goods) Budgeted sales in units 0 1 Budgeted Selling Price 2 3 4 RECEIVABLES 5 Receivables Collection Schedule 6 7 8 9 0 1 INVENTORY COSTS 2 Direct Lobos 3 83,00% quarter of sale 15,00% quarter following salo 2.00% uncollectible 100.00% Entire projected uncollectible receivables are written off each quarter Policy 1.5 hours 18.00 per direct labor hour S CUSTOMIZATION PROJECT INFORMATION Sales Budget Production Budget OM & DL Budge + Scroll to Throto G H S B D 1 INVENTORY COSTS 2 Direct Labor 4.5 hours 3 18.00 per direct labor hour 4 5 Raw Materials 3 pounds per finished goods unit 6 85.00 per pound 27 28 Variable Overhead $ 11:50 per Direct Labor Hour 29 30 Fixed Overhead 31 Depreciations 202,000 per quarter 32 Other Fixed Overheads 950,000 per quarter 33 Fixed Overhead Application Rate CALCULATE FROM PRODUCTION BUDGET 35 36 INVENTORY ACCOUNTS 37 Finished Goods inventory 38 Ah Quarter Ist Quarter 2nd Quarter 3rd Quarter 39 2020 2021 2021 2021 40 Ending units 0 15,000 19,000 20.000 41 42 Raw Materials Inventory 43 Beginning units on 1/1/2021 41,368 pounds 34 planned Ending units 12/21/21121 26.000 und CUSTOMIZATION PROJECT INFORMATION Sales Budget Production Budget DM &LDL Budge oth Quarter 2021 16,000 09 fo CUSTOMIZATIONIQ40 D G 11,363 pounds 26,000 pounds 25.00% of next quarters' needs for production is in ending Raw Materials Twentory 231,720 231,720 8 42 Raw Materials inventory 43 Beginning units on 1/1/2021 44 Planned Ending units on 12/31/2021 45 Policy 46 47 Work In Process Inventory 48 Beginning value 1/1/2021 S 49 Planned Ending value on 12/31/2021 S 50 51 Payment for Raw Materials 52 Payment Policy 53 54 55 OPERATING EXPENSES 56 Wages and Salaries Payment 57 Payment Policy 58 59 60 Variable Selling & Administrative Exp. 67 62 Fixed Selling & Administrative Exp 65 Depreciations 64 Other Selling & Administrative ExpS 85,00% quarter of purchase 15.00% quarter following purchase 2 times monthly on the 15th and 30th Fully paid each month 3.25% of selling price $5,000 per quarter 180,000 per quarter CUSTOMIZATION PROJECT INFORMATION Sales Budget Production Budget DM & DL Budge ad Scroll Lock A D E G H 6 CAPITAL EXPENDITURES 7 1st Duarter 2nd Quarter 3rd Quarter 4th Quarter 8 2121 2021 2021 2021 9 Equipment Purchases S0.00 $0.00 50.00 55.450,000.00 0 1 TREASURY ACTIVITIES 2 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 3 2021 2021 2021 2021 24 Dividends to be declared $0.00 SO DO SO DO $0.00 25 76 Minimum Cash Balance S 250,000 Required at end of every quarter 77 Interest Rate for larvesting (short-term) 4.00% Annually 79 Interest Policy All of the ending cash balance for the quarter earns interest for the entire quarter 30 All of the ending cash balance for the quarter remains in Cavitat the beginning of the next quarter B1 82 Interest Rate for borrowing 12.00 Annually 83 Interest Policy 10,000 increments are used in borrowing and repayment, maintaining the minimum cash balance 84 Borrowing occurs at the beginning of the quarter 35 Repayments but at the end of the year 86 87 FLEXIBLE BUDGET 88 Actual Units Produced USE VALUE FROM PRODUCTION BUDGET 89 CUSTOMIZATION PROJECT INFORMATION Sales Budget Production Budget DM & DL Budgi Stroll och 97 B . D 7 FLEXIBLE BUDGET 58 Actual Units Produced USE VALUE FROM PRODUCTION BUDGET 9 0 Flexible Budget - Analyze at Additional Production Levels 21 Production level 1 O units 22 Production level 2 O units 93 34 PRIOR YEAR RESULTS 95 Ottawa Orientation Company 96 Balance Sheet 12/31/2020 98 ASSETS 99 Cash $ 6,150,000 100 Inventory S 3,748,000 101 Accounts Receivable (net) $ 15,050,000 102 Plant and Equipment $ 29,400,000 103 S 44,348,000 104 105 LIABILITIES AND EQUITY 106 Accounts Payable S 8,100,000 107 Common Stock $ 26,234,500 108 Retained Earnings S 10,013,500 109 S 44,348,000 110 CUSTOMIZATION PROJECT INFORMATION Sales Budget Production