The Cooking Hut Company (CHC), a kiosk retailer, has never budgeted due to its nature as a start-up. CHC is now reaching the point where operations are predictable enough that a budget would be helpful for planning purposes.
Using spreadsheet software, prepare 2016 budget schedules & pro forma financials by quarter and in total, unless otherwise noted, based on the assumptions provided in this narrative.
You will submit one Excel workbook (or Google spreadsheet). There should be only three worksheets in the workbook. Each worksheet is a stand-alone budget scenario: (#1) base case scenario, (#2) modify cash flow scenario, and (#3) new prices scenario.
Each budget scenario should contain the following:
Sales budget
Schedule of expected cash collections for sales
Merchandise Purchases budget (Using COGS as a proxy for Sales) ($)
Schedule of expected cash disbursements for merchandise purchases
Operating budget through Operating Income (EBIT)
Cash Flow budget
Pro Forma Income Statement (by quarter and for the year)
Pro Forma Balance Sheet (as of 12/31)
Pro Forma Statement of Cash Flows (indirect method; for the year)
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
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, CHC wants to have on hand an inventory of items valued at $20,000 (510 units of Product GT-118C and 18,995 units of Product HX-303A) 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
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 $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 $3000 depreciable asset on Jan 1, 2016 with cash
Prior Year (12/31/2015) Balance Sheet
Balance Sheet Assumptions
Assume that there is no additional equity contributed during 2016; 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, 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:
CHC 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, 2016. 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: 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. CHC 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
Additional Advertising Assumptions
The company expects to spend the following in 2016:
Quarter 1 - $3,000
Quarter 2 - $5,000
Quarter 3 - $3,000
Quarter 4 - $3,000
All advertising expenses are paid as incurred
Notes for the Client.
Dear Client, in order to avoid further misunderstanding, I have decided to explain calculations made in Excel file. Please note, that I have more than 20 years of experience as a CFO and a CMA certification underway, so I know what I am talking about. ))
Merchandise purchase budget
The merchandise purchase budget is our biggest confusion point. The ONLY proper way to calculate this budget is to determine the physical flow of units first. The physical flow of units formula is:
Forecasted number of units sold + expected ending balance – units in the beginning inventory.
After we have calculated the physical number of units FOR EACH PRODUCT we can calculate the dollar amount of the purchase by multiplying the number of units by the unit price (70% of sale price in our case).
In order to determine the beginning balance of units for each quarter, if we know that it should be $20 000 in dollar value, we HAVE to come up with some mix of units of each product. I have used 510 units of GT-118C and then plugged in the necessary number of units of HX-303A. It is NOT a mistake, as long as it comes to $20 000 in total.
$48 000 of the beginning inventory have NOTHING TO DO with 510 units calculation. Please do not confuse two separate calculations. 510 is used to come up with $20 000 beginning balance for Q2, Q3 and Q4. $48 000 is the beginning balance for Q1.
$48 000 also needs to be broken down to number of units for GT-118C and HX-303A for the same reasons I have explained above. I have done it, and used $48 000 in my calculation of the merchandise purchase budget. Check cell C23 – it is there. So there is no mistake here either.
If somebody is saying there is some other way to calculate the merchandise purchase budget – THEY ARE WRONG.
Scenario #3 Calculation
Scenario #3 is not as simple as it seems, so I need to give you a detailed explanation of how I have arrived at the final result.
A little remark before I go on. I have not initially done items VI, VIII and IX for Scenario #3 because it make no sense. The result of the price decrease and additional advertising costs is an operating loss. Any manager would dismiss this project after operating budget and no further calculations are necessary. So there is a logic in my omission. However, since I assume that you need to “tick all the boxes” however redundant they might seem, I have done ALL the reports for Scenario #3.
Let’s get back to Scenario #3 calculations.
In order to properly calculate the results, we need to start with the same amount of $48 000 in the beginning inventory. Since the number is the same, as in Scenario #1, I have made an assumption that the beginning inventory was purchased using the original (not new lower) prices. This is very important, because it affects the COGS for Q1 and Q2. I have provided the detailed calculation of COGS for Q1 and Q2 in cells N35:Q50. Because the inventory was bought with higher prices and sold with new, reduced prices, the Gross Margin in Q1 is 22%. This is not a mistake. In Q2 the Gross Margin is 30% because the number of units from the beginning inventory sold in Q2 is small. I have used FIFO inventory costing method for this scenario, where the older units are sold first as the most common approach.
Everything else seems to be clear from the calculations in the excel file.
Please do not hesitate to contact me any time if you have any further questions. I’ll be happy to help, but please go into details and understand the logic behind the numbers first!
