Below you will see three sets of inputs. After inputting all of your formulas, you should be able to use any of these sets of data and have the answers automatically update within excel. Please choose one of the data sets below and input all of the necessary formulas to find the answers. Once you are done, choose a different data set, enter it into your spreadsheet, and check the updated answers to ensure that everything is flowing through the formulas appropriately. A check answer for each one has been provided. Data set #1 Data Section: Actual and 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 unite) sales 60% Collections from customers: Collected in month of sale 20% Collected in month after sale 60% Collected two months after sales 20% Projected cash payments: Variable expenses 30% of sales Fixed expenses (per month) $10,000 Depreciation per month $1,000 Check figure (Total liabilities & equity): $324,357 Data set #2 Inputs: Data Section: Actual and Budgeted Unit Sales: April 1,500 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) 130,000 Total assets $298,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 $55 Desired ending inventory (% of next month's unite) sales 60% Collections from customers: Collected in month of sale 30% Collected in month after sale 60% Collected two months after sales 10% Projected cash payments: Variable expenses 25% of sales Fixed expenses (per month) $10,000 Depreciation per month $1,000 Check figure (Total liabilities & equity): $369,873 Data set #3 Inputs: Data Section: Actual and 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 unite) sales 70% Collections from customers: Collected in month of sale 30% Collected in month after sale 60% Collected two months after sales 10% Projected cash payments: Variable expenses 30% of sales Fixed expenses (per month) $10,000 Depreciation per month $2,000 Check figure (Total liabilities & equity): $331,128
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.
Below you will see three sets of inputs. After inputting all of your formulas, you should be able to use any of these sets of data and have the answers automatically update within excel.
Please choose one of the data sets below and input all of the necessary formulas to find the answers. Once you are done, choose a different data set, enter it into your spreadsheet, and check the updated answers to ensure that everything is flowing through the formulas appropriately. A check answer for each one has been provided.
Data set #1
Data Section:
Actual and Budgeted Unit Sales:
April 1,500
May 1,000
June 1,600
July 1,400
August 1,500
September 1,200
Cash $8,000
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 unite) sales 60%
Collections from customers:
Collected in month of sale 20%
Collected in month after sale 60%
Collected two months after sales 20%
Projected cash payments:
Variable expenses 30% of sales
Fixed expenses (per month) $10,000
Check figure (Total liabilities & equity):
$324,357
Data set #2
Inputs:
Data Section:
Actual and Budgeted Unit Sales:
April 1,500
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) 130,000
Total assets $298,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 $55
Desired ending inventory (% of next month's unite) sales 60%
Collections from customers:
Collected in month of sale 30%
Collected in month after sale 60%
Collected two months after sales 10%
Projected cash payments:
Variable expenses 25% of sales
Fixed expenses (per month) $10,000
Depreciation per month $1,000
Check figure (Total liabilities & equity):
$369,873
Data set #3
Inputs:
Data Section:
Actual and 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 unite) sales 70%
Collections from customers:
Collected in month of sale 30%
Collected in month after sale 60%
Collected two months after sales 10%
Projected cash payments:
Variable expenses 30% of sales
Fixed expenses (per month) $10,000
Depreciation per month $2,000
Check figure (Total liabilities & equity):
$331,128
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 8 images