Budgeting Analytics Assignment

This is more of a coding and programming assignment. Using Excel, prepare budgets with active equations, macros, and links that allow the data to flow from the sales budget through the financial statements. The test will be in the ability to change random data throughout the budgets and achieve the correct numbers in the financial statements. Dazzle me!

Preliminary information:

• This budget is for four quarters comprising a complete fiscal year.
• There are two raw materials used in the production process.
• The budget uses variable costing and produces a contribution income statement (i.e., it is appropriately for internal/operational usage).
• There are uncollectible accounts and an allowance for uncollectible accounts is recorded at the time of sale for budgeting purposes (each quarter).
• There is a required minimum cash balance at the end of each quarter. Provisions may/must be made for borrowing and/or repayments based on this requirement.
• Because this is an operational budget, a balance sheet and statement of retained earnings will not be required.

Parameters:

Additional Information:

• All cash payments except purchases are made quarterly as incurred.
• All repayments occur at the end of the quarter (i.e., you must pay interest for the current quarter for the debt owed at the beginning of the quarter).
• Borrowings and repayments may be made in any amount, but a minimum cash balance of $10,000 must be maintained.
• The ending cash balance on the Cash Budget is $630,104.96.

REQUIRED: Below are listed the minimum individual budgets that must be prepared in Excel. The budgets should be linked to allow estimation of outcomes using various inputs.

1. Sales Budget
2. Production Budget
3. Manufacturing Cost Budget
4. Purchases Budget
5. Selling and Administrative Expense Budget
6. Budgeted Contribution Income Statement
7. Cash Budget

In preparing your budgets:

• Prepare your budgets and schedules in good form. This is the header I used for each budget or schedule (with the budget or schedule name changed, of course).

SILLY SNOT MANUFACTURING COMPANY
Sales Budget
For the Budget Year 2023
	Quarter
	First	Second	Third	Fourth	Year

• You may use two decimal places or round to even dollars
• You do not need to prepare either a balance sheet or a statement of retained earnings
• Yes, all of the information you need is contained in the problem above. If you cannot find the information needed for a computation, the final number may be provided in the text.
• You dont need dollar signs $. If you use $, use them in good form. Think of an equation. The $ starts the equation. Another $ denotes either a subtotal or the final answer. For example:
  • Beginning inventory $1,000
  • Add: Purchases 8,000
  • Cost of goods available for sale $9,000
  • Less: Ending inventory 2,000
  • Cost of goods sold $7,000
• You dont need underlines. If you use them, use them in good form. Think of an equation. The underline is an equal sign =. In the example above, $1,000 + $8,000 = $9,000. The underline indicates some mathematical operation is taking place. The double underline means the end, amen, no mas, fine, were done with that.
• Because I will need the detail to score your budgets, these are the first round of budgets and schedules I prepared:
  • Sales Budget
  • Schedule of Expected Cash Collections
  • Production Budget
  • Direct Materials Budget
  • Purchasing Budget
  • Schedule of Expected Cash Payments
  • Direct Labor Budget
  • Manufacturing Overhead Budget
  • Selling and Administrative Expense Budget
  • Schedule of Cost of Goods Sold
  • Cash Budget
  • Budgeted (Pro Forma) Income Statement

You are welcome to prepare all of these, if you need them to think through the process.

• The Cash Budget and Budgeted Income Statement are interdependent. You will need numbers from each to complete the other.

Company Name: SILLY SNOT $20,000 $15,000 Budget Year: December 31, 2023 Estimated balances, January 1, 2023: Cash Accounts receivable, net Raw materials: Material x Material Q Finished goods Accounts payable Borrowed funds Desired balances, December 31, 2023: Raw material: Material x Material Finished goods Desired balances, end-of-quarter: Cash Raw materials as a portion of following quarter's production needs 300 units 250 units 400 units $9,000 $4,000 150 units 80 units 1,000 units $10,000 0.2 0.3 $200 Unit Price $5.00 4.00 8.00 5.00 Total $15.00 8.00 20.00 12.50 $55.50 $50,000 30,000 $80,000 Finished goods as a portion of following quarter's sales Unit selling price MANUFACTURING COSTS: Standard variable cost per unit: Unit Material X Material 2 Direct labor 2.5 hours Variable overhead 2.5 hours Total Fixed overhead per quarter: Cash Noncash Total Selling and administrative expenses: Variable costs per unit sold Fixed costs per quarter: Cash Noncash Total OTHER COSTS: Interest rate per quarter Income tax rate COLLECTIONS AND PAYMENTS: Portion of sales collected: Quarter of sale Subsequent quarter Bad debts Portion of purchases paid: Quarter of sale Subsequent quarter $2.00 $8,000 2,000 $10,000 0.04 0.4 .4 .55 .5 0.75 0.25 Sales Forecast Quarter Units First 5,000 Second 2,000 Third 3,000 Fourth 2,500 Year 12,500