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

FINANCIAL ACCOUNTING
10th Edition
ISBN:9781259964947
Author:Libby
Publisher:Libby
Chapter1: Financial Statements And Business Decisions
Section: Chapter Questions
Problem 1Q
icon
Related questions
icon
Concept explainers
Question

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

### Budget and Financial Forecasts

#### Collections from Customers
- **From April sales:** $29,400
- **From May sales:** $58,800
- **From June sales:** $31,360
- **From July sales:** $27,440
- **From August sales:** $23,400

**Total Collections:** $127,560

#### Cash Available
- **Beginning Cash Balance:** 
  - April: $14,800
  - May: FORMULA
  - June: 
- **Total Cash Available:**
  - April: $29,400
  - May: $88,200
  - June: $31,360

#### Cash Disbursements
- **Merchandise:**
  - April: $74,800
  - May: FORMULA
  - June: 
- **Variable Expenses:**
  - April: $0
  - May: $41,160
  - June: $44,100
- **Fixed Expenses:**
  - April: $10,000
  - May: $10,000
  - June: $10,000
- **Interest Paid:**
  - April: $0
  - May: $0
  - June: $0

**Total Disbursements:**
- April: $84,800
- May: $41,160
- June: $54,100

#### Cash Balance Before Financing
- April: $42,760
- May: $142,720
- June: $231,700

#### Minimum Desired Ending Balance
- April: $8,000
- May: $8,000
- June: $8,000

**Excess (Deficit) of Cash Over Needs:**
- April: $34,760
- May: $134,720
- June: $223,700

#### Financing
- **Borrowing:**
  - April: $0
  - May: $0
  - June: $0
- **Repayment:**
  - April: $0
  - May: $0
  - June: $0

**Total Effects of Financing:**
- April: $0
- May: $0
- June: $0

**Ending Cash Balance:**
- April: $42,760
- May: $142,720
Transcribed Image Text:### Budget and Financial Forecasts #### Collections from Customers - **From April sales:** $29,400 - **From May sales:** $58,800 - **From June sales:** $31,360 - **From July sales:** $27,440 - **From August sales:** $23,400 **Total Collections:** $127,560 #### Cash Available - **Beginning Cash Balance:** - April: $14,800 - May: FORMULA - June: - **Total Cash Available:** - April: $29,400 - May: $88,200 - June: $31,360 #### Cash Disbursements - **Merchandise:** - April: $74,800 - May: FORMULA - June: - **Variable Expenses:** - April: $0 - May: $41,160 - June: $44,100 - **Fixed Expenses:** - April: $10,000 - May: $10,000 - June: $10,000 - **Interest Paid:** - April: $0 - May: $0 - June: $0 **Total Disbursements:** - April: $84,800 - May: $41,160 - June: $54,100 #### Cash Balance Before Financing - April: $42,760 - May: $142,720 - June: $231,700 #### Minimum Desired Ending Balance - April: $8,000 - May: $8,000 - June: $8,000 **Excess (Deficit) of Cash Over Needs:** - April: $34,760 - May: $134,720 - June: $223,700 #### Financing - **Borrowing:** - April: $0 - May: $0 - June: $0 - **Repayment:** - April: $0 - May: $0 - June: $0 **Total Effects of Financing:** - April: $0 - May: $0 - June: $0 **Ending Cash Balance:** - April: $42,760 - May: $142,720
### 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:
- **Assets:**
  - Cash: $8,000
  - Accounts Receivable: $107,800
  - Merchandise Inventory: $52,800
  - Fixed Assets (net): $130,000
  - **Total Assets:** $298,600

- **Liabilities and Equity:**
  - Accounts Payable (merchandise): $74,800
  - Owner’s Equity: $223,800
  - **Total Liabilities & Equity:** $298,600

#### Additional Information:
- **Average Selling Price:** $38
- **Average Purchase Cost per Unit:** $55
- **Desired Ending Inventory:** 60% of next month’s unit sales
- **Collections from Customers:**
  - Month of Sale: 20%
  - Month After Sale: 60%
  - Two Months After Sale: 20%
- **Projected Cash Payments:**
  - Variable Expenses: 30% of sales
  - Fixed Expenses (per month): $10,000
  - Depreciation per Month: $1,000

### Answer Section:

#### Sales Budget (for June, July, August):
- **Units:** <formula to be derived based on data>
- **Dollars:** <formula to be derived based on data>

#### Unit Purchase Budget (for June, July, August):
- **Desired Ending Inventory:** <formula to be derived based on data>
- **Current Month's Unit Sales:** <formula to be derived based on data>
- **Total Units Needed:** <formula to be derived based on data>
- **Beginning Inventory:** <formula to be derived based on data>
- **Purchases (units):** <formula to be derived based on data>
- **Purchases (dollars):** <formula to be derived based on data>

#### Cash Budget (for June, July, August):
- **Cash Balance, Beginning:** $8,000 (for June), $42,760 (for July), and $142,720 (for August)
- **Cash
Transcribed Image Text:### 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: - **Assets:** - Cash: $8,000 - Accounts Receivable: $107,800 - Merchandise Inventory: $52,800 - Fixed Assets (net): $130,000 - **Total Assets:** $298,600 - **Liabilities and Equity:** - Accounts Payable (merchandise): $74,800 - Owner’s Equity: $223,800 - **Total Liabilities & Equity:** $298,600 #### Additional Information: - **Average Selling Price:** $38 - **Average Purchase Cost per Unit:** $55 - **Desired Ending Inventory:** 60% of next month’s unit sales - **Collections from Customers:** - Month of Sale: 20% - Month After Sale: 60% - Two Months After Sale: 20% - **Projected Cash Payments:** - Variable Expenses: 30% of sales - Fixed Expenses (per month): $10,000 - Depreciation per Month: $1,000 ### Answer Section: #### Sales Budget (for June, July, August): - **Units:** <formula to be derived based on data> - **Dollars:** <formula to be derived based on data> #### Unit Purchase Budget (for June, July, August): - **Desired Ending Inventory:** <formula to be derived based on data> - **Current Month's Unit Sales:** <formula to be derived based on data> - **Total Units Needed:** <formula to be derived based on data> - **Beginning Inventory:** <formula to be derived based on data> - **Purchases (units):** <formula to be derived based on data> - **Purchases (dollars):** <formula to be derived based on data> #### Cash Budget (for June, July, August): - **Cash Balance, Beginning:** $8,000 (for June), $42,760 (for July), and $142,720 (for August) - **Cash
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 2 steps with 8 images

Blurred answer
Knowledge Booster
Budgeting
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.
Similar questions
Recommended textbooks for you
FINANCIAL ACCOUNTING
FINANCIAL ACCOUNTING
Accounting
ISBN:
9781259964947
Author:
Libby
Publisher:
MCG
Accounting
Accounting
Accounting
ISBN:
9781337272094
Author:
WARREN, Carl S., Reeve, James M., Duchac, Jonathan E.
Publisher:
Cengage Learning,
Accounting Information Systems
Accounting Information Systems
Accounting
ISBN:
9781337619202
Author:
Hall, James A.
Publisher:
Cengage Learning,
Horngren's Cost Accounting: A Managerial Emphasis…
Horngren's Cost Accounting: A Managerial Emphasis…
Accounting
ISBN:
9780134475585
Author:
Srikant M. Datar, Madhav V. Rajan
Publisher:
PEARSON
Intermediate Accounting
Intermediate Accounting
Accounting
ISBN:
9781259722660
Author:
J. David Spiceland, Mark W. Nelson, Wayne M Thomas
Publisher:
McGraw-Hill Education
Financial and Managerial Accounting
Financial and Managerial Accounting
Accounting
ISBN:
9781259726705
Author:
John J Wild, Ken W. Shaw, Barbara Chiappetta Fundamental Accounting Principles
Publisher:
McGraw-Hill Education