Problem:
Use Microsoft Excel and use formulas.
On January 1 Winter Snow begins business. This company is operated as a sole proprietorship. The company received $18,000 cash, $8,000 contributed by the owner (Taylor Snow), and $10,000 from a short-term note payable received on the first day of business. They are attempting to project cash receipts and disbursements through March 30. On March 30th the short term note of $10,000 will be due. This amount was borrowed on January 1 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 an assembly machine at a cost of $15,000 financed with a long-term note which bears interest at 8% per year. The interest on this note is due in cash at the end of each quarter and is accrued (expensed) monthly. The machine has no salvage value and is expected to last for 10 years. Monthly depreciation is recorded.
The unit purchase cost of the company's single product, an American Idol doll, Taylor is $5. The unit sales price is $16.00 Projected purchases and sales in units for the first three months are:
Purchases Sales
January 1000 600
February 1300 1200
March 1400 1400
Note that you must calculate the ending inventory. You do not have an inventory policy. One way to format this budget would be Beginning inventory plus purchases equal goods available for sale minus goods sold 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 another 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 - half in the month following the month of sale and half of them in the second month after the month of sale. For example the January credit sales will be collected half in February and half in March.
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 $2,500 and depreciation on the new machine (recorded monthly). Variable marketing and administrative expenses total $3 per unit sold. All marketing and administrative expenses are paid as incurred.
Requirements:
1. Prepare a sales budget (and cash collections), purchases 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 to see if the loan can be repaid on April 1. Use formulas whenever possible. Enter your name in cell A1. Use a data section for the data. Save the file under (yournameoption1).
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. Redo your worksheet by changing the following facts. You should only have to type in the new amounts and your worksheet if properly prepared will update. Copy your worksheet to a different sheet and then perform the following what-if analysis. Evaluate the cash flow for each suggestion. Note: Your income statement and balance sheets should automatically update. Save the file under yournameoption2.
- Put the company's two salespeople on straight commission this would reduce fixed marketing and administrative costs to $1500 per month and raise variable marketing and administrative costs to $4 per unit.