budgeting question , with three scenario :(#1) base case scenario, (#2) modify cash flow scenario, and (#3) new prices scenario

each scenario has to list :

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 Flow budget

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 should be done on an annual basis only and for Scenario #1 only***)

(#1) scenario

SCENARIO #1: BASE CASE The following planning assumptions should be used in your BASE CASE BUDGET SCENARIO Product Listing/Sales Forecast Assumptions Product Unit Price Volumes 2019 Q1 2019 2 2019 Q3 201942020 Q1 1,000 1,540 7,150 40" Latex $2.80 1,360 900 500 36" Mylar $8.00 9,461 7,024 6,560 4,825 Assumptions for Cash Collections from Customers Customers pay 60% in cash, 40% 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, IBC wants to have on hand an inventory of items valued at $20,000 (3,570 units of 40" Latex and 2,322 units of 36" Mylar) plus 80% of the expected cost of goods sold for the following quarter COGS averages 70% of Sales on each product Assumptions for Cash Disbursements for Purchases Purchases are 50% in cash, 50% 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 Cost of Goods Sold Wages Rent See sales forecast assumptions above See planned inventory levels/inventory costs assumptions above 52,500 each quarter; paid as incurred $2,000 each quarter; paid as incurred Depreciation Company uses straight-line depreciation; all depreciable assets (e.g. equipment) have 20-year useful lives with no salvage values, Annual depreciation is pro-rated to quarters equally Insurance The company prepays its annual insurance premium (5800) on Jan 1; insurance is pro-rated to quarters equally Commissions are 15% of total sales; company pays all commissions on a one quarter-lag Commission5 Miscellaneous Expenses | Miscellaneous expenditures are 5% of total sales; paid as incurred each quarter Other Cash Flow Assumptions Maintain a minimum cash balance of S10,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 Other Cash Flow Assumptions- continued Borrow/Repay loans or Invest/Sell securities in increments of $1000 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: 16% annual rate (or 4% each quarter) Accrue simple interest at the end of each quarter on securities held; interest is received in the following quarter; 8% annual rate (or 2% each quarter) Accrue taxes at 30% on Earnings Before Taxes (EBT); Accrued taxes are remitted to governing bodies in the following quarter; for quarters with negative EBT, assume no taxes The company purchased a 53000 depreciable asset on Jan 1, 2018 with cash. (See EBIT budget assumptions for depreciation expense requirements on this asset) Prior Year (12/31/2018) Balance Sheet Assets Liabilities Equity $10,000 S0 S0 16,000 $48,000 S0 516,800 S0 56,000 S0 S0 $75,400 Cash Accounts Payable Interest Payable Commissions Payable Short-term Notes Payable Income Taxes Payable Owner's Equity (Contributed + Marketable Securities Interest Receivable Accounts Receivable nventory Prepaid Insurance Earned Capital) Equipment, Gross 37,000 Accumulated Depreciation (S12,800) Balance Sheet Assumptions Assume that there is no additional equity contributed during 2019; as such, the company wil 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, insurance and depreciation are fixed costs; assume cost of goods sold, commissions, and miscellaneous expenses are variable costs SCENARIO #2: MODIFY CASH FLOW The following planning assumptions should be used in your MODIFY CASH FLOW SCENARIO Scenario Background: IBC wants to improve its cash flow, and wants to understand the impact of more favorable credit terms with customers. The company has decided to offer 5% sales discounts to all cash customers and 2% sales discounts to credit customers who pay in the same quarter as purchase. The company believes the incentives will shift the mix of cash/credit purchases. These changes would be effective Jan 1, 2019. The company wants to understand the impact of this proposal. Using the Spreadshe you have developed in Scenario #1 and the new information below, show the impact on the schedules and pro forma statements. Then, state definitively your recommendation as it relates to this new scenario. Should the company offer sales discounts? Revised Assumptions for Cash Collections from Customers Customers pay 70% in cash, 30% credit 50% of credit sales will be collected in the current quarter; the remaining will be collected in the following quarter; only assume credit customers paying in the same quarter as purchase take the 2% discount All Cash customers receive a 5% discount Uncollectible accounts are negligible and thus ignored Additional Operating Budget (through EBIT) Assumptions Note, all forecasted sales discounts should be subtracted from Gross Revenue on the operating budget to arrive at Net Revenue SCENARIO #3: NEW PRICES The following planning assumptions should be used in your NEW PRICES SCENARIO Scenario Background: Go back to your original budget in Scenario #1. IBC believes its products are relatively price elastic, as such, they believe that decreases in prices will increase volumes and should lead to higher revenues and higher profits. To ensure success, they have also decided to advertise on the radio. Rework Scenario #1 with these changes; revised information follows below. Should the company lower its prices and advertise? Revised Product Listing/Sales Forecast Assumptions Product Unit Price Volumes 2019 Q1 2019 2 2019 Q3 201942020 Q1 1,100 7,655 40" Latex $2.65 1,700 1,450 945 572 36" Mylar $7.20 10,400 7,754 7,008 5,345 Additional Advertising Assumptions The company expects to spend the following in 2019 o Quarter 1 $3,000 o Quarter 2-$5,000 o Quarter 3 - $3,000 o Quarter 4-$3,000 All advertising expenses are paid as incurred