Excel assignment

StrawmanFinancialModel-Student2.xlsx

Home >Business & Finance homework help >Accounting homework help >Excel assignment

Strawman Student Template

NOTES & ASSUMPTIONS
The tax rate is 40%		40%
Cash dividend of $2,600k declared & paid
Part of the long-term debt was retired on Dec 31 :. Interest is based on previous year balance
Long Term Interest Rate		5%
Additional short-term debt was taken on Dec 31 :. Interest is based on previous year balance
Short Term Interest Rate		10%
During the year, $700k of common stock was issued in exchange for PP&E
No PP&E was sold during the year
All sales involve selling mechandise on account
Strawman Corporation
Income Statement
For year ended 12/31/…
ALL FIGURES IN $000s	2018	2019	NOTES
Revenue	$ 62,727.0	$ 69,000.0
COGS (Cost of Goods Sold)	42,727.0	47,000.0
Gross profit	20,000.0
SG&A (Selling, General & Administrative)	8,454.5	9,300.0
Operating income	11,545.5
Other revenues & gains
Gain on sale of investments	-0	800.0
EBIT (Earnings before interest and taxes)	11,545.5
Interest expense, net	863.0
Earnings before taxes	10,682.5
Income tax expense	4,272.0
Net income	$ 6,410.5
Strawman Corporation
Balance Sheet
As of 12/31/…
ALL FIGURES IN $000s	2018	2019	NOTES
Cash	$ 11,500.0	$ -0 Michael Dimond: Please leave this cell reference to feed the figure from the SoCF.	from statement of cash flows
Receivables	13,000.0	17,500.0
Inventory	20,500.0	17,500.0
Total current assets	45,000.0
PP&E, gross	17,000.0	19,000.0
Accumulated depreciation	(11,700.0)	(12,000.0)
Long-term investments (held-to-maturity)	14,200.0	13,000.0
Total Assets	64,500.0
Accounts payable	9,000.0	12,000.0
Short-term note	2,000.0	2,500.0
Accrued liabilities	2,500.0	2,000.0
Total current liabilities	13,500.0
Long-term debt	15,000.0	14,000.0
Preferred stock	-0	-0
Common stock	17,000.0	19,000.0
Retained earnings	19,000.0
Total Liabilities + Shareholders' Equity	$ 64,500.0
check for imbalance	-0	-0
Strawman Corporation
Statement of Cash Flows
For year ended 12/31/…
ALL FIGURES IN $000s		2019	NOTES
Cash flows from operations (DIRECT)
Cash collections from customers			revenue - increase in receivables
LESS:
Cash paid for merchandise			COGS + increase in inventory - increase in payables
Cash paid for SG&A			SG&A - depreciation - increase in accruals
Cash paid for interest
Cash paid for income taxes
Net cash provided (used) by operating activities
Cash flows from operations (INDIRECT)
Net income			starting line
Adjustments to reconcile net income to net cash provided by operating activities
Depreciation			non-cash, add back
Gain on sale of investments			non-operating, subtract if gain
Change in inventory			decrease = source
Change in receivables			increase = use
Change in accounts payable			increase = source
Change in accrued liabilities			decrease = use
Net cash provided (used) by operating activities
Cash flows from investing activities
Sale of held-to-maturity investments			sale = source
Purchase of plant assets			purchase = use
Net cash provided (used) by investing activities
Cash flows from financing activities
Issuance of capital stock			issuance = source
Proceeds from (repayment of) long-term debt			repayment = use
Proceeds from (repayment of) short-term debt			proceeds = source
Payment of cash dividends to preferred stock		-0
Payment of cash dividends to common stock		(2,600.0)	use
Net cash provided (used) by financing activities
Net increase in cash
Cash, beginning of year
Cash, end of year			feeds the balance sheet cash figure
Noncash investing and financing activities
Issuance of common stock for PP&E		$ 700.0
Common-size Income Statement		2019	NOTES
Revenue
COGS (Cost of Goods Sold)
Gross profit
SG&A (Selling, General & Administrative)
Operating income
Other revenues & gains
Gain on sale of investments
EBIT (Earnings before interest and taxes)
Interest expense, net
Earnings before taxes
Income tax expense
Net income
Common-size Balance Sheet		2019	NOTES
Cash
Receivables
Inventory
Total current assets
PP&E, gross
Accumulated depreciation
Long-term investments (held-to-maturity)
Total Assets
Accounts payable
Short-term note
Accrued liabilities
Total current liabilities
Long-term debt
Preferred stock
Common stock
Retained earnings
Total Liabilities + Shareholders' Equity
Growth of Income Statement Items		2019	NOTES
Revenue
COGS (Cost of Goods Sold)
Gross profit
SG&A (Selling, General & Administrative)
Operating income
Other revenues & gains
Gain on sale of investments
EBIT (Earnings before interest and taxes)
Interest expense, net
Earnings before taxes
Income tax expense
Net income
Growth of Balance Sheet Items		2019	NOTES
Cash
Receivables
Inventory
Total current assets
PP&E, gross
Accumulated depreciation
Long-term investments (held-to-maturity)
Total Assets
Accounts payable
Short-term note
Accrued liabilities
Total current liabilities
Long-term debt
Preferred stock
Common stock
Retained earnings
Total Liabilities + Shareholders' Equity
Useful Ratios & Other Figures		2019	NOTES
PM (Net Profit Margin = NI/Revenue)
TAT (Total Asset Turnover = Revenue/Total Assets)
EM (Equity Multiplier = Total Assets/Total Equity)
ROE (Return on Equity = NI/Total Equity = PMTATEM)
ROA (Return on Assets = NI/Total Assets = PM*TAT)
d (Dividend Payout Ratio = Dividends/NI … note: result is POSTIVE)
b (Retention Ratio = 1-d)
SGR (Sustainable Growth Rate = b*ROE)
IGR (Internal Growth Rate = b*ROA)
TIE (Times Interest Earned Ratio = EBIT/Interest)
DOL (Degree of Operating Leverage [point estimate] = Gross Profit/OpInc)
Days of sales in cash ( = Cash/[Revenue/365])
Inventory Turnover ( = COGS/Avg Inventory … note: Avg Inventory = [BOY+EOY]/2 )
Receivables Turnover (= Sales/Avg Receivables … note: Avg Receivables = [BOY+EOY]/2 )
Purchases ( = COGS + Δ inventory)
Payables Turnover ( = Purchases/Avg Payables … note: Avg Payables = [BOY+EOY]/2 )
Days in inventory ( = 365/Inventory Turnover)
Days in receivables ( = 365/Receivables Turnover)
Operating cycle ( = Days in Inventory + Days in Receivables)
Days in payables ( = 365/Paybales Turnover)
Cash cycle ( = Operating Cycle - Days in Payables)
NOTE:
Inventory Turnover Ratio = COGS / Average Inventory
Receivables Turnover Ratio = Net receivable sales / Average net receivables
Purchases = Ending Inventory - Beginning Inventory + COGS
Payables Turnover Ratio = Purchases / Average Accounts Payable