January 1 Sunshine begins business. (This means there was no beginning inventory.) The company is operated as a corporation. This means you will need a retained earnings and common stock account.

The company received a total of $20,000 cash from the following sources $8,000 contributed by the sole shareholder (Hello Kitty) on January 1 in return for 8000 shares of stock at $1.00 par value. AND $12,000 from a short-term note payable received on the first day of business. This amount was borrowed on to carry the company through its first three months of operations. Interest on the loan is 1% per month and must be paid in cash at the end of each month.

The company also purchased a packing machine at a cost of $10,800 financed entirely with a long-term note which bears interest at 4% per year. No cash was paid for the machine. The principal balance of the note will be paid off in four equal annual payments on December 31st of each year. The interest on this note however is due in cash at the end of each quarter and is accrued (expensed) quarterly on the income statement. The machine has no salvage value and is expected to last for 10 years. Monthly straight line depreciation is recorded. (You will need to calculate the monthly depreciation.)

The unit purchase cost of the company’s single product, a flowerpot, is $8. The unit sales price is $18.00 Projected purchases and sales in units for the first three months (quarter) are:

                                      Purchases (units) Sales (units)        

January                        1000                               800

February                       2000                               1600

March                           1000                               1400

Totals                            4000                              3800

Note that you must calculate the ending inventory. You do not have an inventory policy and you will know exactly what your ending inventory will be in units. In this fact scenario the purchase each month is given. One way to format this budget would be Beginning inventory at cost plus purchases at cost equal goods available for sale minus units sold at cost equals ending inventory. Then in the cash payments portion you will focus on the payments for the purchases. You may want to do a units budget and then a dollars budget by multiplying the units by dollars.

It is expected that 50% of the sales will be for cash and 50% will be on credit. The credit sales will be collected as follows- 60% in the month following the month of sale and 40% in the second month after the month of sale. For example the January credit sales (half of the total sales) will be collected 60% in February and 40% in March. February credit sales will be collected 60% in March and 40% in April.

Purchases are paid as follows- half in the month of purchase and half in the month following the month of purchase. Total fixed marketing and administrative expenses for each month include cash expenses of $3,000 and depreciation on the new machine (recorded monthly). Variable marketing and administrative expenses total $3 per unit sold. All cash marketing and administrative expenses are paid each month as incurred- in cash.

Requirements:

1. Prepare a sales budget (and cash collections), purchases/inventory budget (and cash payments), general selling and administrative expense budget (and cash payments) and finally a cash budget in an Excel spreadsheet for the next three months Use formulas whenever possible. Enter your name in cell A1. Use a data section for the data.

2. Create a line chart plotting total monthly receipts and monthly disbursements over the three month period.

3. Prepare a budgeted income statement and a budgeted balance sheet using these assumptions for the three month period.

4. Once you have completely finished this budget make this a flexible budget by increasing the sales and purchases for each month by 10%. Change the variable selling and administrative to $4 per unit and note how this affects your statements and cash budget.