ACCT 2302 Principles of Accounting II - Managerial PROJECT - MASTER BUDGET Objective: To understand and apply the basic concepts of profit planning and gain experience in MS Excel. Required: You need to prepare a comprehensive 12-month budget, including supporting schedules and a report for the period January 1, 2023 to December 31, 2023 for BRK Corp. This project includes a complete budget template that makes up the Master Budget. Notes and Tips 1. All parts must be completed before I grade the project, but you can ask questions via email. I will also review your work if you email it to mc. 2. The completed Master Budget must be uploaded to Blackboard. I will not accept email submissions for grading. The budget must be turned in as an Excel workbook. It is fine if you open the template and use Google Sheets or some other spreadsheet software to complete it, as long as you convert it back to Excel before you upload it to Blackboard. If you do use some other software, make sure all your cells are still formulas. 3. If you need to upload a corrected copy or make changes after uploading, Blackboard will allow you to upload it multiple times. I will only look at the last upload unless you tell me otherwise. 4. The template I have prepared must be used as-is. DO NOT ADD/DELETE COLUMNS OR ROWS!! DO NOT CHANGE ANY CELL FORMATTING! 5. This project demonstrates your proper use of Excel (spreadsheets). You may only input a "hard number" into a pink cell. All yellow cells must be formulas (no numbers should be included within formula. Every variable in the formula should use appropriate cell referencing instead of a number.). 6. You can toggle the spreadsheet display between "normal" and "show formulas" with Ctri + - (the burton left of the number 1 on a standard keyboard) 7. I recommend constructing the formulas for one month and then copying the formulas over to the remaining months using copy/paste "fx only". Try to keep cell formats as they currently are format; i.c. S, %, borders, are preset. Rounding numbers for costs or units is not necessary. 8. The budget template and a copy of this instruction sheet are located on the course materials page under Project. Make sure you download the file as Excel and then open the file through Excel (so it doesn't open in your web browser. If you want to use Excel but do not have it, you can use the school computers which all do, or you can also it is free for students (Microsoft Office) or use Office.com. 9. I provide check figures for you to check your accuracy at different points. 10. The total column at the right of each table should be annual totals if that is appropriate. Some cells don't make sense to have a total, such as the price per unit row. Another example is Cash disbursement total column: the first line beginning cash wouldn't be total of all the beginning months, it would be the Jan 1" amount. Use logic to determine what amount should be reported in these total columns. NOTE: some budgets have 2022 months in them, DO NOT include those months in the Totals for 2023.ACCT 2302 Principles of Accounting II - Managerial PROJECT - MASTER BUDGET INFORMATION FOR RRK CORP. BRK Corp. is a company that sells a highly specialized product, an: "Deepsea sensor array scanning system for use in autonomous and manned deep-submergence vehicles." A tech engineering company manufactures the product exclusively for BRK Corp.; Therefore, BRK Corp. has no manufacturing related costs. PRODUCT COSTS In Nov of 2022, each module costs BRK Corp. $4,900. Per an existing contract, the cost of each unit is scheduled to increase by 6% on May 1, 2023. Rare carth minerals are used in production of the item. Analysis has shown that there will be a shortage of these rare earth minerals due to a spike in their demand. The cost is anticipated to increase by an additional 9% on Sept 1, 2023. PRODUCT SALES BRK Corp. sells cach Module for $18,500 per unit. Projected Sales units: Year Month Amt 2022 No 16 De 13 2023 Jar Fel 10 Ma 10 Apr 25 May 27 Jun 23 Jul 20 Aug 13 Sep 9 Oct 10 Nov 22 De 24 2024 Jan 11 To offset increasing costs of modules, the company plans to raise the sales price to $21,000 per unit beginning Sept 1, 2023. The sales forecast (i.c., estimated sales in units) takes this price increase into account. CREDIT SALES Monthly sales are 12% cash sales, 88% credit sales. 25% of credit sales are collected in the month of sale, 70% are collected the following month, and 4% are collected the 2" month after sale. The remaining receivables are deemed uncollectible at the end of the 2" month after sale. Bad debts are written off in the month the debt is deemed uncollectible PRODUCT INVENTORY The firm's policy regarding inventory is to maintain their stock (ie. have in ending inventory) at 12% of the forecasted sales in units for the next month. BRK Corp. uses the first-in. first-out (FIFO) method in accounting for inventories. Page 2 of 4ACCT 2302 Principles of Accounting II - Managerial PROJECT - MASTER BUDGET 43% of the inventory purchases are paid in the month of purchase with the remaining 57% paid the following month. EQUIPMENT A Note was issued for $1,200,000 for the purchase of facilities and equipment in December 2022. A note payment of $210,000 is due in January 2023, then payments of $1 10,000 per month February through October 2023. There are no Note Payables at the end of 2023. OPERATING EXPENSES Monthly Cash expenses are paid when incurred Salary and Wage Exp $ 17,800 Sales Commissions Exp 5 % of sales revenue Rent Exp $ 14.800 Gen & Admin Variable Exp 3 % of sales revenue Supplies Exp $ 480 Gen & Admin Fixed Exp $ 800 Gen & Admin Depreciation Exp $ 960 (Cash expenses are paid when incurred) CASH MANAGEMENT The company must maintain a minimum cash balance of $300,000. Borrowing can make up shortfalls. For simplicity, assume that the bank will only lend (and accept repayments) in $1,000 increments. (Ignore interest on the loans, but minimize the amount borrowed and pay off any loans as soon as possible.) DIVIDENDS Dividends of $700,000 are paid in Nov. BALANCE SHEET Balance Sheet balances as of December 31, 2022: Cash $ 275,000 Accounts Receivable $ 171,754 Supplica* $ 850 Merchandise Inventory $ 5,292 Buildings and Equipment* $ 1,650,900 Accumulated Depreciation $ 380,000 Accounts Payable $ 34.968 Notes Payable $ 1,200,000 Capital Stock* $ 250,000 Retained Earnings $ 238,828 "same balance in 2023 Page 3 of 4ACCT 2302 Principles of Accounting II - Managerial PROJECT -MASTER BUDGET CHECK FIGURES Cash Collections Budget Total Cash Collections for January: $ 215,177 Total Cash Collections 2023: $ 3,687,750 Bad Debt Exp for January. 2,605 Bad Debt Exp, Total 2023: 30,536 Purchase Budget Cost of Purchases for Dec 2023: 127,043 Operating Expense Budget Cash disbursements for Op Exp for Dec: $ 74,200 Cash Budget Total Cash available, Summary: $ 3,962,750 Total Cash Disbursements, Summary: 3,645,494 Ending Cash Balance for Dec: 317,256 Budgeted Income Statement Cost of Goods Sold Total: $ 1,062,239 Gross Profit, Total $ 2,837,261 Net Income, Total: $ 2,076,685 Budgeted Balance Sheet Total Assets Dec 2023: $ 1,937,927Chrome File Flit View History Bookmarks Profiles Tab Window Help O G 47 ~ Q 2 0 Fri Aug 5 11:33 PM File Home Insert Draw Page Layout Formulas Data Review View Automate Help Editing v Share Comments Catch up Arial 10 v B Y uv Av . .. Eva Custom -0 Ev ATV OV . . . D11 V X V f D G Name: ONLY Pirk Cells can contain a keyed in number Yellow cells must only contain formula W N ERK Corp. Sales Budget For Year Ending Dec 31, 2023 Nov 22 Dec 22 Jan 23 Feb 23 Mar '23 Apr 23 May 23 Jun 23 Jul 23 Budged unit sales 16,000 13,000 Aug '23 Sep '23 Oct 23 Nov '23 Dec 23 Annual Total Jan 24 Selling price per unit Total Sales Cash Sales % Credit Sales % Cash Sales Credit Sales Total Sales Current month A/R Collections 1 month prior A/R Collections 2 months prior A/R Collections Uncollectible BRK Corp. Cash Collections Budget For Year Ending Dec 31, 2023 Jan 23 Feb 23 Mar '23 Apr 23 May 23 Jun 23 Jul 23 Current month cash Sales Aug 23 Sep 23 Oct 23 Nov '23 Dec 23 Annual Total month AVR Collections 1 month prior A/R Collections 2 months prior AVR Collections Total cash colections Bad Debt Expense Desired ending inventory % BRK Corp. Purchase Budget For Year Ending Dec 31, 2023 Nov 22 Dec 22 Jan 23 Feb 23 Mar '23 Apr 23 Budged unit sales May 23 Jun 23 Jul 23 Aug 23 Sep '23 Oct 23 Nov '23 Dec 23 Add desired ending Total unit needs Less Beginning Inventory Required Unit Purchases Cost per unit Cost of Purchases % Paid in Month c Daint in Alnesh sher Durehana E STUDENT BLANK TEMPLATE + Calculation Mode: Automatic Workbook Statistics Give Feedback to Microsoft - 58% +'M File Home Insert Draw Page Layout Formulas Data Review View Automate Help Editing v Share Comments Catch up Arial 10 v B . .. Eva Custom EV ATV OV . . . D11 X V fx B % Paid in Month of Purchase er Purchase BRK Corp. Schedule of Budgeted Cash Disbursements for Merchandise Purchases For Year Ending Dec 31, 2023 Jan 23 Feb 23 Mar '23 Apr 23 May 23 un 23 Jul 23 Aug 23 Sep 23 Oct 23 Current month A/P Payments Nov 23 Dec 23 Annual Total 1 month prior A/P Payments 65 66 Cash disbursements for merchandise purchases Fixed Operating expenses: Variable Operating Expenses: 28 8 Salary and Wage Exp Rent Exp Sales Commission Exp %% of Revenue Gen & Admin Variable Exp % of Revenue Supplies Exp Gan & Admin Fixed Exp Gen & Admin Depreciation Exp BRK Corp. Operating Expanse Budget For Year Ending Dec 31, 2023 Jan 23 Feb 23 Mar 23 Apr 23 May 23 Jun 23 Jul 23 80 Aug 23 Sep 23 Oct 23 Now '23 Dec 23 Annual Total Salary and Wage Exp 81 Sales Commission Exp 82 Rent Exp 83 Gen & Admin Variable Exp Supplies Exp 85 Gen & Admin Fixed Exp Gen & Admin Depreciation Exp Bad Debt Exp Total Operating Exp s: Depreciation Less: Bad Debt Exp (noncash) Cash Disbursements for Operating Expenses Note payment Equipme Note payment Equipment- Feb-out Dividends - Nov Minimum Monthly Cash Budget 98 BRK Corp. Cash Budget For Year Ending Dec 31, 2023 Jan 23 Mar '23 Apr 23 May 23 Jul 23 Aug '23 Sep '23 Oct 23 Cash balance, beginning Now '23 Add colections from customers fal cash available Less disbursements Cash disbursements for merchandise purchases Cash Disbursements for Operating Expenses 109 Note Payments - Equipment 110 Dividends 111 E STUDENT BLANK TEMPLATE + Calculation Mode: Automatic Workbook Statistics Give Feedback to Microsoft - 58% +Chrome File Edit View History Bookmarks Profiles Tab Window Help O G 45 2 Q 2 0 Fri Aug 5 11:34 PM File Home Insert Draw Page Layout Formulas Data Review View Automate Help Editing v Share Comments Catch up Aria 10 B BY My Av ... Ev ab Custom . . . D11 X Vfx # Selling price per unit Total Sales Cash Sales % Credit Sales % Cash Sales Credit Sales Total Sales Current month AV/R Collections 1 month prior AV/R Collections 2 months prior AV/R Collections Uncolectible BRK Corp. Cash Collections Budget For Year Ending Dec 31, 2023 Feb 23 Mar '23 Apr 23 May 23 Jun 23 Jul 23 Aug 23 Sep 23 Oct 23 Current month cash Sales Nov '23 Dec 23 Annual Total Current month /V/R Collections 1 month prior A/R Collections months prior AVR Collections Total cash colections Bad Debt Expense Desired ending inventory % BRK Corp. Purchase Budget For Year Ending Dec 31, 2023 Now 22 Budged unit sales Apr 23 Aug 23 Add desired ending inventory Total unit needs Less Beginning Inventory Required Unit Purchases Cost per unit Cost of Purchases %% Paid in Month of Purchase Paid in Month after Purchase BRK Corp. Schedule of Budgeted Cash Disbursements for Merchandise Purchases For Year Ending Dec 31, 2023 Jan 23 Feb 23 Mar '23 Current month /P Payments Apr 23 May 23 Jun 23 Jul 23 Aug '23 Sep 23 Oct 23 Nov 23 Dec 23 Annual Total 1 month prior A/P Payments Each dichursamants for me STUDENT BLANK TEMPLATE + Calculation Mode: Automatic Workbook Statistics Give Feedback to Microsoft - 58% +Chrome File Edit View History Bookmarks Profiles Tab Window Help O Q 8 0 Fri Aug 5 11:34 PM File Home Insert Draw Page Layout Formulas Data Review View Automate Help Editing v Share Comments Catch up Aria 10 v B . . . Ev ab Custom . . . D11 X V f # D Cash disburse E G 65 ants for merchandise H L M N 0 P Q R 66 Fixed Operating expenses: Variable Operating Expenses: 28 8 Salary and Wage Exp Rent Exp Sales Commission Exp % of Revenue Supplies Exp Gon & Admin Variable Exp % of Revenue Gen & Admin Fixed Exp Gen & Admin Depreciation Exp BRK Corp. Operating Expanse Budget For Year Ending Dec 31, 2023 Jan 23 Feb 23 Mar '23 80 Apr 23 May 23 Salary and Wage Exp Jun 23 Aug 23 Sep 23 Oct 23 Nov 23 Dec 23 Annual Total Sales Commission Exp 82 Rent Exp 83 Gen & Admin Variable Exp Supplies Exp Gen & Admin Fixed Exp Gen & Admin Depreciation Exp Bad Debt Exp Total Operating Exp Less: Depreciation Exp (noncash) Less: Bad Debt Exp (noncash) Cash Disbursements for Operating Expense Note payment Equipment . January Note payment Equ Dividends - Nov Minimum Monthly Cash Budget 98 BRK Corp. Cash Budget For Year Ending Dec 31, 2023 Jan 23 Feb '23 Mar '23 Apr 23 May 23 Jun 23 Jul 23 Aug 23 Cash balance, beginning Sep '23 Oct 23 Now '23 Dec '23 Add colections from customers Total cash available Less disbursements Cash disbursements for merchandise purchases Cash Disbursements for Operating Expenses Note Payments - Equipment 110 Dividends 111 Total cash disbursements Excess of receipts over disbursements Financing: Borrowing-note Repayments-note Cash balance, ending 117 118 119 BRK Corp. Inventory Data 120 COGS Calculation using FIFO Jon using FIFO Cost Assumption E STUDENT BLANK TEMPLATE + Calculation Mode: Automatic Workbook Statistics Give Feedback to Microsoft - 58% +Chrome File Edit View History Bookmarks Profiles Tab Window Help O Q 8 0 Fri Aug 5 11:36 PM File Home Insert Draw Page Layout Formulas Data Review View Automate Help Editing Share Comments Catch up Arial 10 v B Y uv Av . . . Eva Custom EV ATV OV . . . D11 Y X Vfx # B D E G H K M N Q 136 137 BRK Corp. 138 Budgeted Income Statement 139 For Year Ending Dec 31, 2023 140 141 Jan '23 Feb 23 Mar "23 Apr '23 May '23 Jun '23 Jul '23 Aug 23 Sep '23 Oct '23 Nov '23 Dec '23 Annual Total 142 Sales 143 Cost of goods sold: 144 Gross Profit 145 Total Operating Exp 146 Net Operating Income 147 148 BRK Corp. 149 Budgeted Balance Sheet 150 as of Dec 31, 2023 151 152 2023 2022 153 Assets 154 Current Assets: 155 Cash 156 Accounts receivable Supplies 158 Merchandise Inventory 159 Plant and Equipment: 160 Buildings and Equipment 161 Accumulated Depreciation 62 Total assets 163 164 Liabilities and Equity 165 Accounts Payable 166 Notes Payable 167 168 Capital stock 169 Retained earnings 170 Total abilities and equity 171 172 173 174 175 176 127 178 179 180 181 192 183 184 185 186 E STUDENT BLANK TEMPLATE + Calculation Mode: Automatic Workbook Statistics Give Feedback to Microsoft - 65% +