Master Budget Spreadsheet Instructions: use the listed available Master Budget Spreadsheet excel data to list the required formulas to complete the Forecasted Balance Sheet including the missing spreadsheet items that are used in summing the initial $324,357 figure used in this given Forcasted Balance Sheet's Fixed assets (net) formula: $131,717=$324,357-$8,000-$145,040-$39,600. Respective coresponding Fixed assets (net) Balance Sheet formulas derived from the Master Budget Spreadsheet data: $324,357=Spreadsheet data? Cash: $8,000 Cash balance, ending Accounts receviable: 145,040=(147,000*80%)+(137,200*20%) Sales buget August*80%+Sales budget July*20% Merchandise inventory: 39,600=720*55 Desired ending inventory August*Average purchase cost per unit Actual & Budgeted Unit Sales April 1,500 May 1,000
Variance Analysis
In layman's terms, variance analysis is an analysis of a difference between planned and actual behavior. Variance analysis is mainly used by the companies to maintain a control over a business. After analyzing differences, companies find the reasons for the variance so that the necessary steps should be taken to correct that variance.
Standard Costing
The standard cost system is the expected cost per unit product manufactured and it helps in estimating the deviations and controlling them as well as fixing the selling price of the product. For example, it helps to plan the cost for the coming year on the various expenses.
Respective coresponding Fixed assets (net) Balance Sheet formulas derived from the Master Budget Spreadsheet data:
$324,357=Spreadsheet data?
Cash: $8,000 Cash balance, ending
Accounts receviable: 145,040=(147,000*80%)+(137,200*20%) Sales buget August*80%+Sales budget July*20%
Merchandise inventory: 39,600=720*55 Desired ending inventory August*Average purchase cost per unit
| Actual & Budgeted Unit Sales | |||
| April | 1,500 | ||
| May | 1,000 | ||
| June | 1,600 | ||
| July | 1,400 | ||
| August | 1,500 | ||
| September | 1,200 | ||
|
Balance Sheet, May 31 19X5 |
|||
| Cash | $8,000 | ||
| Accounts receivable | 107,800 | ||
| Merchandise Inventory | 52,800 | ||
| Fixed assets (net) | 130,000 | ||
| ----------- | |||
| Total assets | $298,600 | ||
| ====== | |||
| Accounts payable (merchandise) | $74,800 | ||
| Owner's equity | 223,800 | ||
| ----------- | |||
| Total liabilities & equity | $298,600 | ||
| ======= | |||
| Average selling price | $98 | ||
| Average purchase cost per unit | $55 | ||
| Desired ending inventory (%of next month's unit sales) | 60% | ||
| Collections from customers: | |||
| Collected in month of sales | 20% | ||
| Collected in month after sale | 60% | ||
| Collected two months after sale | 20% | ||
| Projected cash payments: | |||
| Variable expenses | 30% of sales | ||
| Fixed expenses (per month) | $10,000 | ||
| $1,000 | |||
| ---------------------------------------------- | ----------------- | ||
| Sales Budget | |||
| June | July | August | |
| Units | 1,600 | 1,400 | 1,500 |
| Dollars | $156,800 | 137,200 | 147,000 |
| Unit Purchase Budget | |||
| June | July | August | |
| Desired ending inventory | 840 | 900 | 720 |
| Current month's unit sales | 1,600 | 1,400 | 1,500 |
| -------- | -------- | -------- | |
| Total units needed | 2,440 | 2,300 | 2,220 |
| Beginning inventory | 600 | 840 | 900 |
| -------- | -------- | -------- | |
| Purchases (units) | 1,840 | 1,460 | 1,320 |
| ======== | ======== | ======== | |
| Purchase (dollars) | $101,200 | $80,300 | $72,600 |
| ======== | ======== | ======== | |
| Cash Budget | |||
| June | July | August | |
| Cash balance beginning | $8,000 | $8,000 | $8,000 |
| Cash receipts: | |||
| Collections from customers: | |||
| From April sales | 29,400 | ||
| From May Sales | 58,800 | 19,600 | |
| From June sales | 31,360 | 94,080 | 31,360 |
| From July sales | 27,440 | 82,320 | |
| From August sales | 29,400 | ||
| -------- | --------- | --------- | |
| Total cash available | $127,560 | $149,120 | $151,080 |
| Cash disbursements: | |||
| Merchandise | $74,800 | $101,200 | $80,300 |
| Variable expenses | 47,040 | 41,160 | 44,100 |
| Fixed expenses | 10,000 | 10,000 | 10,000 |
| Interest paid | 0 | 184 | 356 |
| -------- | -------- | -------- | |
| Total disbursements | $131,840 | $152,544 | $134,756 |
| -------- | -------- | -------- | |
| Cash balance before financing | ($4,280) | ($3,424) | $16,324 |
| Less: Desired ending balance | 8,000 | 8,000 | 8,000 |
| -------- | -------- | -------- | |
| Excess (deficit) of cash over needs | ($12,280) | ($11,424) | $8,324 |
| -------- | -------- | -------- | |
| Financing | |||
| Borrowing | $12,280 | $11,424 | $0 |
| Repayment | 0 | 0 | (8,324) |
| -------- | --------- | --------- | |
| Total effects of financing | $12,280 | $11,424 | ($8,324) |
| -------- | --------- | -------- | |
| Cash balance, ending | $8,000 | $8,000 | $8,000 |
| ======== | ======== | ======== | |
| 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 | Solution? |
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
