ACC EXCEL

exampleStarbucksMasterBudget.xlsx

Home >Business & Finance homework help >Accounting homework help >ACC EXCEL

Stock Price to Sales Budget

						"=abs(number)"		2nd Quarter Budgeted Sales*
Date	Week Day	Closing Price	Opening Price	Change in Price	Multiplier	Absolute Value		April	May	June	July
2-Mar	Monday	$ 82.38	$ 77.92	4.46	$ 446,000.00	446,000	April Sales	$ 1,207,000	$ 1,218,000	$ 1,274,000	$ 1,233,000
3-Mar	Tuesday	$ 78.60	$ 82.32	-3.72	$ (372,000.00)	372,000
4-Mar	Wednesday	$ 79.67	$ 79.29	0.38	$ 38,000.00	38,000		200	300
5-Mar	Thursday	$ 76.19	$ 77.74	-1.55	$ (155,000.00)	155,000
6-Mar	Friday	$ 75.34	$ 73.38	1.96	$ 196,000.00	196,000
9-Mar	Monday	$ 70.83	$ 70.58	0.25	$ 25,000.00	25,000	May Sales
10-Mar	Tuesday	$ 74.87	$ 72.62	2.25	$ 225,000.00	225,000
11-Mar	Wednesday	$ 68.16	$ 72.72	-4.56	$ (456,000.00)	456,000
12-Mar	Thursday	$ 62.10	$ 62.95	-0.85	$ (85,000.00)	85,000
13-Mar	Friday	$ 69.92	$ 65.65	4.27	$ 427,000.00	427,000
16-Mar	Monday	$ 58.59	$ 60.32	-1.73	$ (173,000.00)	173,000	June Sales
17-Mar	Tuesday	58.98	59.55	-0.57	$ (57,000.00)	57,000
18-Mar	Wednesday	56.33	55.38	0.95	$ 95,000.00	95,000
19-Mar	Thursday	61.41	56.23	5.18	$ 518,000.00	518,000
20-Mar	Friday	58.03	62.34	-4.31	$ (431,000.00)	431,000
						July Sales = Average of April, May, & June

Income Statement

Starbucks Income Statement From Annual Report			July	August	Sept	Oct.
Assume Ending Date is March 31st 2020			$ 1,207,000	$ 1,218,000	$ 1,274,000	$ 1,233,000
(In millions USD)		Monthly Avg.	$ 1,207	$ 1,218	$ 1,274	$ 1,233
Revenues	$ 26,508.60	$ 2,209.05
Cost of Sales	$ 8,526.90			Other information from the statements necessary to make the budget			Calculation
Gross Profit	$ 17,981.70	32.17%		1) Cost of Sales are 32.17% of Sales			32.17%
Store Operating Expenses	$ 10,493.60			2) Variable Expenses (Store Operations + Other Operations) = 40.99%			40.99%
Other Operating Expenses	$ 371.00			3) Fixed Expenses are $163.33 per month			$ 163.33
Depreciation/Amortization	$ 1,377.30	$ 114.78		4) Income tax rate is 19.52% (Income Tax Expense/Earnings before Taxes)			19.52%
General & Admin	$ 1,824.10	$ 1,959.90	$ 163.33	5) Depreciation = $114.78/month			$ 114.78
Restructuring & Impairments	$ 135.80			6) Interest Rate is ~3% per year (Interest Expense/Long Term Debt)			2.96%
Total Operating Expenses	$ 14,201.80
Income from Equity Investees	$ 298.00
Operating Income	$ 4,077.90
Gain resulting form divestiture of opeartions	$ 622.80
Interest Income	$ 96.50
Interest Expense	$ 331.00	2.96%
Earnings before taxes	$ 4,466.20
Income tax Expense	$ 871.60	19.52%
Net Earnings including non-controlling interest	$ 3,594.60
Net Loss from non-controlling interest	$ 4.60
Net Earnings attributable to Starbucks	$ 3,599.20
Other Comprehensive Income	$ (173.00)
Comprehensive Income Attributable to Starbucks	$ 3,426.20

Balance Sheet

Starbucks Balance Sheet from Annual Report
Assume Ending Date is June 30th 2020
(In millions USD)
Current Assets
Cash & Cash Equivalents	$ 2,686.60
Short-Term Investments	$ 70.50
Accounts Receivable	$ 879.20
Inventories	$ 1,529.40
Prepaid Expenses	$ 488.20
Total Current Assets	$ 5,653.90
Long-Term Investments	$ 220.00
Equity Investments	$ 396.00
PPE, net	$ 6,431.70
Deferred Income Taxes	$ 1,765.80
Other Long-Term Assets	$ 479.60
Other Intangible Assets	$ 781.80
Goodwill	$ 3,490.80
Total Assets	$ 19,219.60
Current Liabilities
Accounts Payable	$ 1,189.70
Accrued Liabilities	$ 1,753.70
Accrued Payroll & Benefits	$ 664.60
Income Taxes Payable	$ 1,291.70
Stored Value Card Liability	$ 1,269.00
Total Current Liabilities	$ 6,168.70
Long-Term Debt	$ 11,167.00
Deferred Revenue	$ 6,744.40
Other Long-Term Liabilities	$ 1,370.50
Total Liabilities	$ 25,450.60
Shareholders' Equity/Deficit
Common Stock	$ 1.20
Additional Paid-In Capital	$ 41.10
Retained Earnings	$ (5,771.20)
Accumulated Other Comprehensive Loss	$ (503.30)
Total Shareholders' Deficit	$ (6,232.20)
Non-Controlling Interests	$ 1.20
Total Shareholders' Deficit	$ (6,231.00)
Total Liabilities & Shareholders' Deficit	$ 19,219.60

Budget Information

		Sales Forecast				*My sales forecast is considerably less than the average monthly revenue of the past year
		July	August	Septmeber	October
		$ 1,207.00	$ 1,218.00	$ 1,274.00	$ 1,233.00
Credit Sales	80%
Cash Sales	20%
Desired Ending Inventory	$ 764.70
Previous Year Average Monthly Revenue	$ 2,209.05
Other information from the statements necessary to make the budget		Calculation
1) Cost of Sales are 32.17% of Sales		32.17%
2) Variable Expenses (Store Operations + Other Operations) = 40.99%		40.99%
3) Fixed Expenses are $163.33 per month		$ 163.33
4) Income tax rate is 19.52% (Income Tax Expense/Earnings before Taxes)		19.52%
5) Depreciation = $114.78/month		$ 114.78
6) Interest Rate is ~3% per year (Interest Expense/Long Term Debt)		2.96%

Schedule A-Sales Budget

Sales Budget
	July	August	September	October
Cash sales, 20%	$241.40	$243.60	$254.80	$ 246.60
Credit sales, 80%	$965.60	$974.40	$1,019.20	$ 986.40
	Sales Forecast
	July	August	September
	$ 1,207.00	$ 1,218.00	$ 1,274.00

Schedule B-Cash Collections

Cash Collections
	July	August	September
Cash Sales	$241.40	$243.60	$254.80
On accounts receivable from:
April Sales				*Notice no collections from April or May, all Accounts Receivable from the Balance Sheet are collected in July
May Sales
June Receivables (Balance Sheet)	$ 879.20
July Sales		$482.80	$386.24
August Sales			$487.20
Total Collections	$1,120.60	$726.40	$1,128.24
	Account Receivable on Quarterly Budgeted Balance Sheet
	September	$1,019.20	(September Credit Sales)
	August	$ 487.20	(August Credit Sales * 50%)
	July	$ 96.56	(July Credit Sales * 10%)
		$1,602.96

Schedule C-Inventory Purchases

Purchases Budget
	June	July	August	September
Desired Ending Inventory		$ 764.70	$ 764.70	$ 764.70
Cost of Goods Sold		$ 388.25	$ 391.79	$ 409.80
Total Needed		$ 1,152.95	$ 1,156.49	$ 1,174.50
Beginning Inventory		$ 1,529.40	$ 1,141.15	$ 764.70
Purchases	$ 1,189.70	$ - 0	$ 15.34	$ 409.80	Account Payable amount on the Budgeted Balance Sheet
	Balance Sheet Accounts Payable
Cost of Goods Sold	32.17%
		Sales Forecast
		July	August	September
		$ 1,207.00	$ 1,218.00	$ 1,274.00

Schedule D-Cash for Inventory

Cash Paid for Inventory Purchases
	July	August	September
Last Month's Purchases	$ 1,189.70	$ - 0	$ 15.34
Each Month's Purchases from Schedule C are paid for the following month

Schedule E-Expenditures Budget

Cash Expenditures Budget
	July	August	Sepetmber	Total
Variable Expenses (40.99%)	$ 494.69	$ 499.20	$ 522.15	$ 1,516.04
Fixed Expenses	$ 163.33	$ 163.33	$ 163.33	$ 489.98
Cash Expenditures	$ 658.02	$ 662.52	$ 685.48	$ 2,006.02
	Sales Forecast
	July	August	Sepetmber
	$ 1,207.00	$ 1,218.00	$ 1,274.00
2) Variable Expenses =	40.99%

Schedule F-Operating Expenses

Operating Expenses Budget
	July	August	Sepetmber	Total
Cash Expenditures	$ 658.02	$ 662.52	$ 685.48	$ 2,006.02
Depreciation	$ 114.78	$ 114.78	$ 114.78	$ 344.33	Depreciation from the Income Statement (Yearly Total): Divided by 12
Total Operating Expenses	$ 772.79	$ 777.30	$ 800.25	$ 2,350.34

Cash Budget

	July	August	Sepetmber
Beginning Cash Balance (From Balance Sheet)	$ 2,686.60	$ 537.32	$ 537.32
Minimum Cash Balance Desired (20% of Cash Balance)	$ 537.32	$ 537.32	$ 537.32
Available Cash Balance	$ 2,149.28	$ - 0	$ - 0
Cash from customers (Schedule B)	$ 1,120.60	$ 726.40	$ 1,128.24
Payment for Inventory (Schedule D)	$ 1,189.70	$ - 0	$ 15.34
Accrued Liabilities (Balance Sheet)	$ 876.85	$ 438.43	$ 438.43
Accrued Payroll & Benefits (Balance Sheet)	$ 664.60	$ - 0	$ - 0	You decide how much of the current liability you will pay
Income Taxes Payable (Balance Sheet)	$ 1,291.70	$ - 0	$ - 0
Payments for Cash Expenditures (Schedule E)	$ 658.02	$ 662.52	$ 685.48
Payments for interest	$ - 0
Net Cash Receipts & Disbursements	$ (3,560.27)	$ (374.55)	$ (11.00)
Excess (Deficiency) of cash before financing	$ (1,410.99)	$ (374.55)	$ (11.00)
Financing
Borrowing at beginning of period	$ 1,410.99	$ 374.55	$ 11.00
Repayment at end of period
Ending Cash Balance (Beg Bal + Net Cash Receipts + Borrowed)	$ 537.32	$ 537.32	$ 537.32	Beginning Cash Balance on Balance Sheet
OR (Beg Bal + Net Cash Receipts - Repayment)

Capital Budget

	July	August	Sepetmber	Long Term Portion	Short Term Portion	Interest Payable & Interest Expense
Beginning Balance (Long Term Debt + New Loan)	$ 12,577.99	$ 12,983.60	$ 13,037.67	$ 11,178.00	$ 1,796.54	$ 95.34
Annual Interest Expense @ ~3%	$ 31.07	$ 32.07	$ 32.20
Ending Balance Before Repayment (June 30th)	$ 12,609.05	$ 13,015.67	$ 13,069.88
Principal Repayment
(From statement of receipts and disbursements)
Interest Payment
Ending Balance	$ 12,609.05	$ 13,015.67	$ 13,069.88
Interest Rate	2.96%

Budgeted 2ndQ Income Statement

Starbucks Second Quarter Budgeted Income Statement
Ending Date September 30th 2020
(In millions USD)
Sales (Given)	$ 3,699.00
Cost of Goods Sold (32.17% of Sales)	$ 1,189.84
Gross Profit	$ 2,509.16
Operating Expenses
Variable Expenses (Schedule E)	$ 1,516.04
Fixed Expenses (Schedule E)	$ 489.98
Depreciation (Schedule F)	$ 344.33
Total Operating Expenses	$ 2,350.34
Operating Income	$ 158.82
Interest Expense (Cash Budget)	$ 95.34
Earnings Before Taxes	$ 63.47
Income tax Expense (Tax Rate)	$ 12.39
Net Income	$ 51.09

Budgeted 2ndQ Balance Sheet

Starbucks Second Quarter Budgeted Balance Sheet
Ending Date September 30th 2020
(In millions USD)
Current Assets
Cash & Cash Equivalents (Cash Budget)	$ 537.32
Short-Term Investments (Given)	$ 70.50
Accounts Receivable (Schedule B)	$1,602.96
Inventories (Schedule C)	$ 764.70
Prepaid Expenses (Given)	$ 488.20
Total Current Assets	$ 3,463.68
Long-Term Assets
Long-Term Investments (Given)	$ 220.00
Equity Investments (Given)	$ 396.00
PPE (Given)	$ 6,431.70
Accumulated Depreciation (Schedule F)	$ (344.33)
Deferred Income Taxes (Given)	$ 1,765.80
Other Long-Term Assets (Given)	$ 479.60
Other Intangible Assets (Given)	$ 781.80
Goodwill (Given)	$ 3,490.80
Total Long Term Assets	$ 13,221.38
Total Assets	$ 16,685.06
Current Liabilities
Accounts Payable (Schedule C)	$ 409.80
Interest Payable (Capital Budget)	$ 95.34
Income Tax Payable (Income Statement)	$ 12.39
Short Term Note Payable (Capital Budget)	$ 1,796.54
Stored Value Card Liability (Given)	$ 1,269.00
Total Current Liabilities	$ 3,583.07
Long-Term Debt (Capital Budget)	$ 11,167.00
Deferred Revenue (Given)	$ 6,744.40
Other Long-Term Liabilities (Given)	$ 1,370.50
Total Liabilities	$ 22,864.97
Shareholders' Equity/Deficit
Common Stock (Given)	$ 1.20
Additional Paid-In Capital (Given)	$ 41.10
Retained Earnings (Given + Income Statement)	$ (5,720.11)
Accumulated Other Comprehensive Loss (Given)	$ (503.30)
Total Shareholders' Deficit	$ (6,181.11)
Non-Controlling Interests (Given)	$ 1.20
Total Shareholders' Deficit	$ (6,179.91)
Total Liabilities & Shareholders' Deficit	$ 16,685.06