The Hum & Buzz (HB), a kiosk retailer that sells two types of honey, has never budgeted due to its nature as a start-up. HB is now reaching the point where operations are predictable enough that a budget would be helpful for planning purposes. Required Submission Using spreadsheet software (Microsoft Excel or Google Sheets), prepare Year 2 budget schedules & pro forma financials by quarter and by year, unless otherwise noted, based on the assumptions provided in this narrative. Each individual will submit one Excel workbook. There should be only three worksheets in the workbook. Each worksheet is a stand-alone budget scenario: (#1) base case scenario, (#2) offer cash discounts scenario, and (#3) new prices scenario. Each budget 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 should be done on an annual basis only and for Scenario #1 only***) SCENARIO #1: BASE CASE The following planning assumptions should be used in your BASE CASE BUDGET SCENARIO. Product Listing/Sales Forecast Assumptions Product Selling Price Volumes Year 2 Q1 Year 2 Q2 Year 2 Q3 Year 2 Q4 Year 3 Q1 1 oz Sage $2.80 1,000 1,540 1,360 900 500 3 oz Clover $8.00 7,150 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, HB wants to have on hand an inventory of items valued at $20,000 (510 units of 1 oz Sage and 3,393 units of 3 oz Clover) plus 80% of the expected cost of goods sold for the following quarter COGS averages 70% of Gross 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 See sales forecast assumptions above Cost of Goods Sold See planned inventory levels/inventory costs assumptions above Wages $2,500 each quarter; paid as incurred Rent $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 prorated to quarters equally Insurance The company prepays its annual insurance premium ($800) on Jan 1; insurance is prorated to quarters equally Commissions Commissions are 15% of gross sales; company pays all commissions on a one quarter-lag Miscellaneous Expenses Miscellaneous expenditures are 5% of gross sales; 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; 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 new, $3000 depreciable asset on Jan 1, Year 2 with cash. (See EBIT budget assumptions for depreciation expense requirements on this asset.) Prior Year (12/31/Year 1) Balance Sheet Assets Liabilities + Equity Cash $10,000 Accounts Payable $16,800 Marketable Securities $0 Interest Payable $0 Interest Receivable $0 Commissions Payable $6,000 Accounts Receivable $16,000 Short-term Notes Payable $0 Inventory $48,000 Income Taxes Payable $0 Prepaid Insurance $0 Owners Equity (Contributed + Earned Capital) $75,400 Equipment, Gross $37,000 Accumulated Depreciation ($12,800) 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, commissions, and miscellaneous expenses are variable costs SCENARIO #2: OFFER DISCOUNTS The following planning assumptions should be used in your OFFER DISCOUNTS SCENARIO. Scenario Background: HB 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, Year 2. The company wants to understand the impact of this proposal. Using the spreadsheet 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: DECREASED PRICES The following planning assumptions should be used in your NEW PRICES SCENARIO. Scenario Background: Go back to your original budget in Scenario #1. HB 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. Then, state definitively your recommendation as it relates to this new scenario. Should the company lower its prices and advertise? Revised Product Listing/Sales Forecast Assumptions Product Selling Price Volumes Year 2 Q1 Year 2 Q2 Year 2 Q3 Year 2 Q4 Year 3 Q1 1 oz Sage $2.65 1,100 1,700 1,450 945 572 3 oz Clover $7.20 7,655 10,400 7,754 7,008 5,345 Advertising Assumptions The company expects to incur the following advertising expenses in Year 2: Quarter 1 - $3,000 Quarter 2 - $5,000 Quarter 3 - $3,000 Quarter 4 - $3,000 All advertising expenses are paid as incurred.