Master Budget Spreadsheet Instructions: use the listed available Master Budget Spreadsheet excel data to list required formulas to complete the Forecasted Income Statement and Forecasted Balance Sheet. Actual & Budgeted Unit Sales April 2,000 May 1,000 June 1,600 July 1,400 August 1,500 September 1,700 Balance Sheet, May 31 19X5 Cash $8,000 Accounts receivable 107,800 Merchandise Inventory 52,800 Fixed assets (net) 150,000 ----------- Total assets $318,600 ====== Accounts payable (merchandise) $74,800 Owner's equity 223,800 ----------- Total liabilities & equity $298,600 ======= Average selling price $100 Average purchase cost per unit $60 Desired ending inventory (%of next month's unit sales) 70% Collections from customers: Collected in month of sales 30% Collected in month after sale 60% Collected two months after sale 10% Projected cash payments: Variable expenses 30% of sales Fixed expenses (per month) $10,000 Depreciation per month $2,000 ---------------------------------------------- ----------------- Sales Budget June July August Units 1,600 1,400 1,500 Dollars $160,000 140,000 150,000 Unit Purchase Budget June July August Desired ending inventory 980 1,050 1,190 Current month's unit sales 1,600 1,400 1,500 -------- -------- -------- Total units needed 2,580 2,450 2,690 Beginning inventory 700 980 1,050 -------- -------- -------- Purchases (units) 1,880 1,470 1,640 ======== ======== ======== Purchase (dollars) $112,800 $88,200 $98,400 ======== ======== ======== Cash Budget June July August Cash balance beginning $8,000 $8,000 $8,000 Cash receipts: Collections from customers: From April sales 20,000 From May Sales 60,000 10,000 From June sales 48,000 96,000 16,000 From July sales 42,000 84,000 From August sales 45,000 -------- --------- --------- Total cash available $136,000 $156,000 $153,000 Cash disbursements: Merchandise $74,800 $112,800 $88,200 Variable expenses 48,000 42,000 45,000 Fixed expenses 10,000 10,000 10,000 Interest paid 0 72 325 -------- -------- -------- Total disbursements $132,800 $164,872 $143,525 -------- -------- -------- Cash balance before financing $3,200 ($8,872) $9,475 Less: Desired ending balance 8,000 8,000 8,000 -------- -------- -------- Excess (deficit) of cash over needs ($4,800) ($16,872) $1,475 -------- -------- -------- Financing Borrowing $4,800 $16,872 $0 Repayment 0 0 ($1,475) -------- --------- --------- Total effects of financing $4,800 $16,872 ($1,475) -------- --------- -------- Cash balance, ending $8,000 $8,000 $8,000 ======== ======== ======== Forecasted Income Statement For Quarter Ended August 31, 19X5 --------------------------------------------------- Sales Formula? Cost of goods sold Formula? ----------------- Gross profit Formula? ----------------- Expenses: Variable expenses Formula? Fixed expenses Formula? Depreciation expenses Formula? Interest expenses Formula? ----------------- Total expenses Formula? ----------------- Net income Formula? ========= Forecasted Balance Sheet August 31, 19X5 --------------------------------------------------- Assets: Cash Formula? Accounts receivable Formula? Merchandise inventory Formula? Fixed assets (net) Formula? ----------------- Total assets Formula? ========= Liabilities & equity: Accounts payable Formula? Loans payable Formula? Owners equity Formula? ----------------- Total liabilities & equity Formula?
Master Budget Spreadsheet Instructions: use the listed available Master Budget Spreadsheet excel data to list required formulas to complete the Forecasted Income Statement and Forecasted Balance Sheet. Actual & Budgeted Unit Sales April 2,000 May 1,000 June 1,600 July 1,400 August 1,500 September 1,700 Balance Sheet, May 31 19X5 Cash $8,000 Accounts receivable 107,800 Merchandise Inventory 52,800 Fixed assets (net) 150,000 ----------- Total assets $318,600 ====== Accounts payable (merchandise) $74,800 Owner's equity 223,800 ----------- Total liabilities & equity $298,600 ======= Average selling price $100 Average purchase cost per unit $60 Desired ending inventory (%of next month's unit sales) 70% Collections from customers: Collected in month of sales 30% Collected in month after sale 60% Collected two months after sale 10% Projected cash payments: Variable expenses 30% of sales Fixed expenses (per month) $10,000 Depreciation per month $2,000 ---------------------------------------------- ----------------- Sales Budget June July August Units 1,600 1,400 1,500 Dollars $160,000 140,000 150,000 Unit Purchase Budget June July August Desired ending inventory 980 1,050 1,190 Current month's unit sales 1,600 1,400 1,500 -------- -------- -------- Total units needed 2,580 2,450 2,690 Beginning inventory 700 980 1,050 -------- -------- -------- Purchases (units) 1,880 1,470 1,640 ======== ======== ======== Purchase (dollars) $112,800 $88,200 $98,400 ======== ======== ======== Cash Budget June July August Cash balance beginning $8,000 $8,000 $8,000 Cash receipts: Collections from customers: From April sales 20,000 From May Sales 60,000 10,000 From June sales 48,000 96,000 16,000 From July sales 42,000 84,000 From August sales 45,000 -------- --------- --------- Total cash available $136,000 $156,000 $153,000 Cash disbursements: Merchandise $74,800 $112,800 $88,200 Variable expenses 48,000 42,000 45,000 Fixed expenses 10,000 10,000 10,000 Interest paid 0 72 325 -------- -------- -------- Total disbursements $132,800 $164,872 $143,525 -------- -------- -------- Cash balance before financing $3,200 ($8,872) $9,475 Less: Desired ending balance 8,000 8,000 8,000 -------- -------- -------- Excess (deficit) of cash over needs ($4,800) ($16,872) $1,475 -------- -------- -------- Financing Borrowing $4,800 $16,872 $0 Repayment 0 0 ($1,475) -------- --------- --------- Total effects of financing $4,800 $16,872 ($1,475) -------- --------- -------- Cash balance, ending $8,000 $8,000 $8,000 ======== ======== ======== Forecasted Income Statement For Quarter Ended August 31, 19X5 --------------------------------------------------- Sales Formula? Cost of goods sold Formula? ----------------- Gross profit Formula? ----------------- Expenses: Variable expenses Formula? Fixed expenses Formula? Depreciation expenses Formula? Interest expenses Formula? ----------------- Total expenses Formula? ----------------- Net income Formula? ========= Forecasted Balance Sheet August 31, 19X5 --------------------------------------------------- Assets: Cash Formula? Accounts receivable Formula? Merchandise inventory Formula? Fixed assets (net) Formula? ----------------- Total assets Formula? ========= Liabilities & equity: Accounts payable Formula? Loans payable Formula? Owners equity Formula? ----------------- Total liabilities & equity Formula?
Chapter1: Financial Statements And Business Decisions
Section: Chapter Questions
Problem 1Q
Related questions
Concept explainers
Master Budget
A master budget can be defined as an estimation of the revenue earned or expenses incurred over a specified period of time in the future and it is generally prepared on a periodic basis which can be either monthly, quarterly, half-yearly, or annually. It helps a business, an organization, or even an individual to manage the money effectively. A budget also helps in monitoring the performance of the people in the organization and helps in better decision-making.
Sales Budget and Selling
A budget is a financial plan designed by an undertaking for a definite period in future which acts as a major contributor towards enhancing the financial success of the business undertaking. The budget generally takes into account both current and future income and expenses.
Question
| Actual & Budgeted Unit Sales | |||
| April | 2,000 | ||
| May | 1,000 | ||
| June | 1,600 | ||
| July | 1,400 | ||
| August | 1,500 | ||
| September | 1,700 | ||
|
Balance Sheet, May 31 19X5 |
|||
| Cash | $8,000 | ||
| 107,800 | |||
| Merchandise Inventory | 52,800 | ||
| Fixed assets (net) | 150,000 | ||
| ----------- | |||
| Total assets | $318,600 | ||
| ====== | |||
| Accounts payable (merchandise) | $74,800 | ||
| Owner's equity | 223,800 | ||
| ----------- | |||
| Total liabilities & equity | $298,600 | ||
| ======= | |||
| Average selling price | $100 | ||
| Average purchase cost per unit | $60 | ||
| Desired ending inventory (%of next month's unit sales) | 70% | ||
| Collections from customers: | |||
| Collected in month of sales | 30% | ||
| Collected in month after sale | 60% | ||
| Collected two months after sale | 10% | ||
| Projected cash payments: | |||
| Variable expenses | 30% of sales | ||
| Fixed expenses (per month) | $10,000 | ||
| $2,000 | |||
| ---------------------------------------------- | ----------------- | ||
| Sales Budget | |||
| June | July | August | |
| Units | 1,600 | 1,400 | 1,500 |
| Dollars | $160,000 | 140,000 | 150,000 |
| Unit Purchase Budget | |||
| June | July | August | |
| Desired ending inventory | 980 | 1,050 | 1,190 |
| Current month's unit sales | 1,600 | 1,400 | 1,500 |
| -------- | -------- | -------- | |
| Total units needed | 2,580 | 2,450 | 2,690 |
| Beginning inventory | 700 | 980 | 1,050 |
| -------- | -------- | -------- | |
| Purchases (units) | 1,880 | 1,470 | 1,640 |
| ======== | ======== | ======== | |
| Purchase (dollars) | $112,800 | $88,200 | $98,400 |
| ======== | ======== | ======== | |
| June | July | August | |
| Cash balance beginning | $8,000 | $8,000 | $8,000 |
| Cash receipts: | |||
| Collections from customers: | |||
| From April sales | 20,000 | ||
| From May Sales | 60,000 | 10,000 | |
| From June sales | 48,000 | 96,000 | 16,000 |
| From July sales | 42,000 | 84,000 | |
| From August sales | 45,000 | ||
| -------- | --------- | --------- | |
| Total cash available | $136,000 | $156,000 | $153,000 |
| Cash disbursements: | |||
| Merchandise | $74,800 | $112,800 | $88,200 |
| Variable expenses | 48,000 | 42,000 | 45,000 |
| Fixed expenses | 10,000 | 10,000 | 10,000 |
| Interest paid | 0 | 72 | 325 |
| -------- | -------- | -------- | |
| Total disbursements | $132,800 | $164,872 | $143,525 |
| -------- | -------- | -------- | |
| Cash balance before financing | $3,200 | ($8,872) | $9,475 |
| Less: Desired ending balance | 8,000 | 8,000 | 8,000 |
| -------- | -------- | -------- | |
| Excess (deficit) of cash over needs | ($4,800) | ($16,872) | $1,475 |
| -------- | -------- | -------- | |
| Financing | |||
| Borrowing | $4,800 | $16,872 | $0 |
| Repayment | 0 | 0 | ($1,475) |
| -------- | --------- | --------- | |
| Total effects of financing | $4,800 | $16,872 | ($1,475) |
| -------- | --------- | -------- | |
| Cash balance, ending | $8,000 | $8,000 | $8,000 |
| ======== | ======== | ======== | |
| Forecasted Income Statement For Quarter Ended August 31, 19X5 | |||
| --------------------------------------------------- | |||
| Sales | Formula? | ||
| Cost of goods sold | Formula? | ||
| ----------------- | |||
| Gross profit | Formula? | ||
| ----------------- | |||
| Expenses: | |||
| Variable expenses | Formula? | ||
| Fixed expenses | Formula? | ||
| Depreciation expenses | Formula? | ||
| Interest expenses | Formula? | ||
| ----------------- | |||
| Total expenses | Formula? | ||
| ----------------- | |||
| Net income | Formula? | ||
| ========= | |||
| Forecasted Balance Sheet August 31, 19X5 | |||
| --------------------------------------------------- | |||
| Assets: | |||
| Cash | Formula? | ||
| Accounts receivable | Formula? | ||
| Merchandise inventory | Formula? | ||
| Fixed assets (net) | Formula? | ||
| ----------------- | |||
| Total assets | Formula? | ||
| ========= | |||
| Liabilities & equity: | |||
| Accounts payable | Formula? | ||
| Loans payable | Formula? | ||
| Owners equity | Formula? | ||
| ----------------- | |||
| Total liabilities & equity | Formula? |
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, accounting and related others by exploring similar questions and additional content below.Recommended textbooks for you
Accounting
Accounting
ISBN:
9781337272094
Author:
WARREN, Carl S., Reeve, James M., Duchac, Jonathan E.
Publisher:
Cengage Learning,
Accounting Information Systems
Accounting
ISBN:
9781337619202
Author:
Hall, James A.
Publisher:
Cengage Learning,
Accounting
Accounting
ISBN:
9781337272094
Author:
WARREN, Carl S., Reeve, James M., Duchac, Jonathan E.
Publisher:
Cengage Learning,
Accounting Information Systems
Accounting
ISBN:
9781337619202
Author:
Hall, James A.
Publisher:
Cengage Learning,
Horngren's Cost Accounting: A Managerial Emphasis…
Accounting
ISBN:
9780134475585
Author:
Srikant M. Datar, Madhav V. Rajan
Publisher:
PEARSON
Intermediate Accounting
Accounting
ISBN:
9781259722660
Author:
J. David Spiceland, Mark W. Nelson, Wayne M Thomas
Publisher:
McGraw-Hill Education
Financial and Managerial Accounting
Accounting
ISBN:
9781259726705
Author:
John J Wild, Ken W. Shaw, Barbara Chiappetta Fundamental Accounting Principles
Publisher:
McGraw-Hill Education