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 #1Data Section:Actual and Budgeted Unit Sales:April 1,500May 1,000June 1,600July 1,400August 1,500September 1,200Balance Sheet, May 31, 19X5Cash $8,000Accounts Receivable 107,800Merchandise Inventory 52,800Fixed Assets (net) 130,000Total assets $298,600Accounts Payable (merchandise) $74,800Owner's equity 223,800Total liabilities & equity $298,600Average selling price $98Average purchase cost per unit $55Desired 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 salesFixed expenses (per month) $10,000Depreciation per month $1,000Check figure (Total liabilities & equity):$324,357 Data set #2Inputs:Data Section:Actual and Budgeted Unit Sales:April 1,500May 1,000June 1,600July 1,400August 1,500September 1,700Balance Sheet, May 31, 19X5Cash $8,000Accounts Receivable 107,800Merchandise Inventory 52,800Fixed Assets (net) 130,000Total assets $298,600Accounts Payable (merchandise) $74,800Owner's equity 223,800Total liabilities & equity $298,600Average selling price $100Average purchase cost per unit $55Desired 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 salesFixed expenses (per month) $10,000Depreciation per month $1,000Check figure (Total liabilities & equity):$369,873 Data set #3Inputs:Data Section:Actual and Budgeted Unit Sales:April 2,000May 1,000June 1,600July 1,400August 1,500September 1,700Balance Sheet, May 31, 19X5Cash $8,000Accounts Receivable 107,800Merchandise Inventory 52,800Fixed Assets (net) 150,000Total assets $318,600Accounts Payable (merchandise) $74,800Owner's equity 223,800Total liabilities & equity $298,600Average selling price $100Average purchase cost per unit $60Desired 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 salesFixed expenses (per month) $10,000Depreciation per month $2,000Check figure (Total liabilities & equity):$331,128 767377Collections from customers:From April salesFrom May salecFrom June sales29,40058,80031,36074787519,60094,08027,4407976807778From July salesFrom August sales31,36082,32029,4008182 1983 8084Total cash available$127,560 ##*#** ******81 Cash dicbursements:85 8286 8387 8488 8589 86'$74,800 FORMUL:$044,10010,000MerchandiseVariable expensesFixed expensesInterest paid41,16010,00090 87Total disbursements$84,800$41,160$54,10091 8832 89 Cash balance before financing93 90 Less: Desired ending balance$42,760 $142,720 $231,7008,0008,0008,000349135 92 Excess (deficit) of cash over needs96 9397 94 Financing98 95$34,760 $134,720 ####**BorrowingRepayment$0$0$099 96100 97101 38 Total effects of financing102 99103 ## Cash balance, ending$0$0$0$42,760 $142,720 $231,700104 ##105 ##106 ##107 ##108 110=====Forecasted Income StatementFor Quarter Ended August 31, 19X5109 110 SalesFORMULA16110 110 Cost of goods soldFORMULA17111110112 110 Gross profit113 110114 110 Expenses:115FORMULA18Variable expensesFixed expenses110FORMULA19116 110FORMULA20110 Depreciation expenseFORMULA21FORMULA22117118## Interest expense119120 ## Total expenses$0121 ##$0122 ## Net income123 ##124 ##125 ##Forecasted Balance SheetAugust 31, 13X5126 ##127 ##128 ## Asets:129 ## Cash130 ## Accounts receivable131 ## Merchandise inventory132 ## Fixed assets (net)133 ##134 ## Total assetsFORMULA23FORMULA24FORMULA25FORMULA26$0135 ##136 ## Liabilities & equity:137 ## Accounts payable138 ## Loans payable139 ## Owner's equityFORMULA27FORMULA28140 ##141 ## Total liabilities & equity$0142 ## 6.AB6 Data Section:1011121314151617181978 Actual and Budgeted Unit Sales:April1,5001,0001,6001,4001,5001,20010May11June12131415JulyAugustSeptember16 Balance Shet, May 31, 19X5$8,000107,80052,8002017Cash2118Accounts receivableMerchandise inventoryFixed assets (net)22 1923 20130,00024 2125 2226 2327 2428 2529 2630 2731 2832 2933 30 Average selling price34 31 Average purchase cost per unit35 32 Desired ending inventory36 33 (* of next month's unit sales)37 34 Collections from customers:38 3539 3640 3741Total assets========Accounts payable (merchandise)Owner's equity$74,800223,800Total liabilities & equity========$98$5560%Collected in month of saleCollected in month after saleCollected two months after sale20%60%20%38 Projected cash payments:42 3943 40 Fixed expenses (per month)44Variable expenses30% of sales$10,000$1,00041 Depreciotion per month45 4246 4347 44 Answer Section:48 4549 4650 47Sales BudgetJuneJulyAugust514852 49UnitsFORMULA53 50DollarsFORMULA5451Unit Purchases Budget555256 535458 5559 56 Desired ending inventory60 57 Current month's unit sales57JuneJulyAugust......FORMULAFORMULA615862 59 Total units needed63 60 Beginning inventory64 6165 62 Purchases (units)66 6367 64 Purchases (dollars)FORMULAFORMULAFORMULAFORMULA$0$068 6569 6670 67716872 69Cash BudgetJuneJulyAugust73 707412 Cash receipts:71 Cash balance, beginning75$8,000 $42,760 $142,7207673 Collections from customers:

Cash budget refers to an estimation of cash inflows and outflows in a business over a specified…

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

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

See similar textbooks

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

76
73
77
Collections from customers:
From April sales
From May salec
From June sales
29,400
58,800
31,360
74
78
75
19,600
94,080
27,440
79
76
80
77
78
From July sales
From August sales
31,360
82,320
29,400
81
82 19
83 80
84
Total cash available
$127,560 ##*#** ******
81 Cash dicbursements:
85 82
86 83
87 84
88 85
89 86
'$74,800 FORMUL:
$0
44,100
10,000
Merchandise
Variable expenses
Fixed expenses
Interest paid
41,160
10,000
90 87
Total disbursements
$84,800
$41,160
$54,100
91 88
32 89 Cash balance before financing
93 90 Less: Desired ending balance
$42,760 $142,720 $231,700
8,000
8,000
8,000
34
91
35 92 Excess (deficit) of cash over needs
96 93
97 94 Financing
98 95
$34,760 $134,720 ####**
Borrowing
Repayment
$0
$0
$0
99 96
100 97
101 38 Total effects of financing
102 99
103 ## Cash balance, ending
$0
$0
$0
$42,760 $142,720 $231,700
104 ##
105 ##
106 ##
107 ##
108 110
=====
Forecasted Income Statement
For Quarter Ended August 31, 19X5
109 110 Sales
FORMULA16
110 110 Cost of goods sold
FORMULA17
111
110
112 110 Gross profit
113 110
114 110 Expenses:
115
FORMULA18
Variable expenses
Fixed expenses
110
FORMULA19
116 110
FORMULA20
110 Depreciation expense
FORMULA21
FORMULA22
117
118
## Interest expense
119
120 ## Total expenses
$0
121 ##
$0
122 ## Net income
123 ##
124 ##
125 ##
Forecasted Balance Sheet
August 31, 13X5
126 ##
127 ##
128 ## Asets:
129 ## Cash
130 ## Accounts receivable
131 ## Merchandise inventory
132 ## Fixed assets (net)
133 ##
134 ## Total assets
FORMULA23
FORMULA24
FORMULA25
FORMULA26
$0
135 ##
136 ## Liabilities & equity:
137 ## Accounts payable
138 ## Loans payable
139 ## Owner's equity
FORMULA27
FORMULA28
140 ##
141 ## Total liabilities & equity
$0
142 ##

6.
A
B
6 Data Section:
10
11
12
13
14
15
16
17
18
19
7
8 Actual and Budgeted Unit Sales:
April
1,500
1,000
1,600
1,400
1,500
1,200
10
May
11
June
12
13
14
15
July
August
September
16 Balance Shet, May 31, 19X5
$8,000
107,800
52,800
20
17
Cash
21
18
Accounts receivable
Merchandise inventory
Fixed assets (net)
22 19
23 20
130,000
24 21
25 22
26 23
27 24
28 25
29 26
30 27
31 28
32 29
33 30 Average selling price
34 31 Average purchase cost per unit
35 32 Desired ending inventory
36 33 (* of next month's unit sales)
37 34 Collections from customers:
38 35
39 36
40 37
41
Total assets
========
Accounts payable (merchandise)
Owner's equity
$74,800
223,800
Total liabilities & equity
========
$98
$55
60%
Collected in month of sale
Collected in month after sale
Collected two months after sale
20%
60%
20%
38 Projected cash payments:
42 39
43 40 Fixed expenses (per month)
44
Variable expenses
30% of sales
$10,000
$1,000
41 Depreciotion per month
45 42
46 43
47 44 Answer Section:
48 45
49 46
50 47
Sales Budget
June
July
August
51
48
52 49
Units
FORMULA
53 50
Dollars
FORMULA
54
51
Unit Purchases Budget
55
52
56 53
54
58 55
59 56 Desired ending inventory
60 57 Current month's unit sales
57
June
July
August
......
FORMULA
FORMULA
61
58
62 59 Total units needed
63 60 Beginning inventory
64 61
65 62 Purchases (units)
66 63
67 64 Purchases (dollars)
FORMULA
FORMULA
FORMULA
FORMULA
$0
$0
68 65
69 66
70 67
71
68
72 69
Cash Budget
June
July
August
73 70
74
12 Cash receipts:
71 Cash balance, beginning
75
$8,000 $42,760 $142,720
76
73 Collections from customers:

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 steps with 8 images

SEE SOLUTION Check out a sample Q&A here

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.