Company XYZ$,$ a television retailer that sells two flat screens and has never budgeted due to its nature as a start$-$up$.$Company XYZ is now reaching the point where operations are predictable enough that a budget would

be helpful for planning purposes.

Required:

Using Microsoft Excel prepare Year $2$ budget schedules & pro

forma financials by quarter and by year, unless otherwise noted, based on the assumptions provided in this narrative.

$($#$1)$ base case scenario,

scenario should contain the following schedules:

I. Sales budget

II$.$ Schedule of expected cash collections for sales

III. Merchandise Purchases budget $($Using COGS as a proxy for Sales$)($$$)$

IV$.$ Schedule of expected cash disbursements for merchandise purchases

V$.$ Operating budget through Operating Income $($EBIT$)$

VI$.$ Cash budget $($by quarter$)$

VII. Pro Forma Income Statement $($by quarter and for the year$)$

VIII. Pro Forma Balance Sheet $($as of $12/31)$

IX$.$ Pro Forma Statement of Cash Flows $($indirect method; for the year$)$

X$.$ Break$-$even analysis using weighted average unit contribution margin $(***$this sThe following planning assumptions should be used in your BASE CASE BUDGET SCENARIO.

Product Listing$/$Sales Forecast Assumptions

Product Selling

Price

Product

Cost

Volumes

Year $2$

Q$1$

Year $2$

Q$2$

Year $2$

Q$3$

Year $2$

Q$4$

Year $3$

Q$1$

Product A $$11\mathrm{.}00$ $$4\mathrm{.}257,5007,0005,5006,5005,500$

Product B $$14\mathrm{.}00$ $$6\mathrm{.}755,0005,0006,5007,5006,000$

Assumptions for Cash Collections from Customers

$$ Customers pay $40\%$ in cash, $60\%$ credit; All credit sales are collected in following quarter

$$ Uncollectible accounts are negligible and thus ignored

Planned Inventory Levels$/$Inventory Costs Assumptions

$$ At the end of each quarter, Small Company wants to have on hand an inventory of items valued at

$15\%$ of the expected sales for the following quarter

Assumptions for Cash Disbursements for Purchases

$$ Purchases are $70\%$ in cash, $30\%$ credit; All credit purchases are paid for in the following quarter

$$ The company does not currently receive favorable terms from its suppliers; therefore, no

discounts are taken

Operating Budget $($through EBIT$)$ Assumptions

Revenue See sales forecast assumptions above

Cost of Goods Sold See planned inventory levels$/$inventory costs assumptions above

Wages $$25,000$ each quarter; paid as incurred

Rent $$6,250$ each quarter; paid as incurred

Depreciation Company uses straight$-$line depreciation; all depreciable assets $($e$.$g$.,$

equipment$)$ have $10-$year useful lives with no salvage values;

Annual depreciation is prorated to quarters equally

Shipping Expenses Shipping expenses are $$0\mathrm{.}75$ per unit sold; paid as incurred each

quarter.

Sales Commissions Sales commissions are $5\%$ of gross sales; paid as incurred each

quarter.

Other Administrative

Expenses

Other administrative expenses are $$12,500$ per quarter; paid as

incurred each quarter

Other Cash Flow Assumptions

$$ Maintain a minimum cash balance of $$10,000$ at end of each quarter

$$ Use short$-$term loans to meet cash needs and to meet minimum cash balance; invest in short term

marketable securities with excess cash so as not to exceed minimum cash balance

$$ Borrow no more cash than necessary; repay as promptly as possible

$$ Borrow$/$Repay loans or Invest$/$Sell securities in increments of $$1,000$

$$ Borrowing$/$Repayments occur at the beginning of each quarter in question; Investing$/$Selling

securities occurs at the beginning of each quarter in question

$$ Accrue simple interest at the end of each quarter on outstanding loan balances; interest is paid in

the following quarter; $8\%$ annual rate $($or $2\%$ each quarter$)$

$$ Accrue simple interest at the end of each quarter on securities held; interest is received in the

following quarter; $4\%$ annual rate $($or $1\%$ each quarter$)$

$$ Accrue taxes at $34\%$ on Earnings Before Taxes $($EBT$)$; Accrued taxes are remitted to governing

bodies in the following quarter; for quarters with negative EBT, assume no taxes

Prior Year $(12/31/$Year $1)$ Balance Sheet

Assets Liabilities $+$ Equity

Cash $$10,000$ Accounts Payable $$35,000$

Marketable Securities $$12,000$ Short$-$term Notes Payable $$0$

Interest Receivable $$0$ Interest Payable $$0$

Accounts Receivable $$25,000$ Income Taxes Payable $$4,000$

Inventory $(6,000$ units

Product A; $4,000$ units

Product B$)$

$$52,500$ Common Stock $$100,000$

Equipment, Gross $$125,000$ Retained Earnings $$85,500$

Accumulated Depreciation $($$$0)$

Balance Sheet Assumptions

$$ Assume that there is no additional equity contributed during Year $2$; as such, the company will

only increase the equity account via earned capital $($i$.$e$.,$ retained earnings$)$

Break$-$even Assumptions

$$ Calculate a sales mix based on units

$$ Assume wages, rent, other administrative expenses, and depreciation are fixed costs; assume cost

of goods sold, sales commissions, and shipping are variable cost