Buad $195$

Chapter $4$ Master Budget Assignment

Winter $2024$

Page $2$ of $3$

Required:

Use Microsoft Excel to complete this assignment. Each student is to create his$/$her own file from scratch, and complete the assignment individually. Use formulas wherever possible. The spreadsheet should be formatted to show amounts to the nearest dollar $($no cents$).$

The items in the budget should appear in the following order:

The balance sheet for December $31,2023($as given above$).$

A cash receipts schedule for January, February and March.

NOTE: You must first calculate the estimated sales units, then calculate the sales dollars$).$

Hint #$1$ Use the given percentages, with the Round function $($explained below$),$ to avoid a rounding error. Hint #$2$ Include cash received from issuing new shares, if applicable.

Check figures: Cash receipts for January should be $$58,520$ and March should be $$67,892.$

A purchases schedule, in units, for January, February and March.

Check figure: January purchases should be $277$ units.

A cash payments schedule for January, February and March.

Check figure: January's total cash payments should be $$72,215.$

A cash budget for January, February and March, including the balance of the short$-$term loan balance at the end of each month $($presented below the cash budget$).$

Check figures: At the end of January the cash balance should be $$20,000$ and the short$-$term loan balance should be $$$16,898$ at the end of March.

The pro$-$forma income statements for January, February and March with a fourth column for totals. Subtotals for EBIT and EBT should be included. List all expenses separately $($do not combine$)$ and show long$-$term and short$-$term interest separately.

Hint: Cost of goods sold is not the same thing as purchases. Cost of goods sold is based on sales.

Check figures: January's earnings after taxes should be $$202,$ and the total earnings after tax for all three months $($Jan to Mar$)$ should be $$2,865.$

A pro$-$forma retained earnings schedule for the quarter ended March $31($not for each month$).$

Check figure: Ending retained earnings should be $$3,470.$

A pro$-$forma balance sheet at March $31.$ You do not have to complete balance sheets for January or February. Hints: Consider what will cause balances to change from the December $31,2023$ balance sheet. Prepaid insurance will be the opening amount less the amount expensed on the income statement. Tax payable will be the opening balance plus total tax expense less total tax installments.

Check figure: Total assets should be $$112,344.$

Additional Instructions for Submitting the Master Budget Assignment.

This assignment is due Sunday, March ${24}^{th}$ at $11$:$59$ p$.$m$.$ via the Moodle assignment drop$-$box.

Be sure to format your Excel file so it prints out looking professional and presentable to your boss $($or the bank or a potential investor, etc.$)$

Each person is to create his$/$her own Excel file from scratch and do this assignment individually. I will be checking the Excel file properties for the 'create date', 'author' and 'last modified date' to ensure your file is original.

You can help each other with formulas, checking numbers, etc., but no copying. I have methods to check on this, and I will use them! Several students in the past have been assigned a zero on this assignment and have faced disciplinary action with the Dean for not doing their own work.