Thank you for all that y'all do

Project

Welcome to Desire, Inc! You have just been hired as the managerial account for the newly formed Desire, Inc. Desire will begin operations on October 1st, Year 1 (i.e., they did not exist before this date). Desire is a merchandising company that retails sporting goods. Your job is to prepare a master budget for Desire for the quarter consisting of October, November, and December Year 1. As part of the budgeting process, you will need to complete the following budget schedules for each of the three months of the quarter:

a. Sales Budget

b. Schedule of Cash Receipts

c. Inventory Purchases Budget

d. Schedule of Cash Payments for Inventory Purchases

e. Selling and Administrative Expense Budget

f. Schedule of Cash Payments for Selling and Admin. Expenses

g. Cash Budget

h. Pro-forma Income Statement

i. Pro-forma Balance Sheet

The following budget information and assumptions will help you complete this task.

Desire anticipates total sales to be $295,000 for October.

o 40% of sales made each month are cash sales. The rest of sales are made on account (Accounts Receivable).

o Total sales are expected to increase each month by 17%.

o Cash from cash sales is collected immediately (in the month of the sale). All cash from sales made on account is collected in the month following the sale.

o Desire predicts that total sales in January of Year 2 will be $246,000.

Desire anticipates Cost of Goods Sold for each month to be 72% of total sales for that month.

o Desire requires that ending inventory for each month be 20% of the next months expected cost of goods sold.

o Because Desire is set to begin operations on October 1st, they did not have any beginning inventory at the beginning of October.

o Desire makes all their inventory purchases on account. They will pay for 35% of inventory purchases in the month of purchase, and they will pay for the rest (65%) in the month following the purchase.

Selling and Administrative Expenses are as follows:

o Total salaries expense is $25,000 each month, and Desire pays this expense in the month it is incurred

o Desire has a sales commission expense equal to 3% of total sales for the month. They do not pay their salespeople the commission until the month following when the sales are made. o Supplies expense is 2% of total sales for the month and is paid for in the month of the sale.

o Utilities expense is expected to be $2,250 each month. Utilities are not paid until the month after the expense is incurred.

o Depreciation on store fixtures (purchase details described later) is on a straight-line basis and will be $8,375 each month.

o Rent on the store is $8,500 each month and is paid in the month it is incurred.

o Miscellaneous expenses are $1,500 each month and are paid in the month they are incurred.

Extra notes on the cash budget o Because Desire is set to begin operations on October 1st, they had no beginning cash balance at the beginning of October.

o On October 1st, Desire, Inc. collected $265,000 and issued stock to the owners.

o On October 1st, Desire purchased $450,000 worth of store fixtures (depreciation on the fixtures was described previously).

o Desire declared and paid a $32,000 cash dividend to stockholders on December 31st, Year 1.

o Desire has a policy that requires each months ending cash balance to be at least $22,000. Desires bank requires them to borrow and repay money in increments of $1000. When Desire borrows or repays money, they do so on the last day of the month. If Desire borrows money, they will pay back as much possible in the months when they exceed the $22,000 cash balance minimum.

o The bank charges Desire interest of 1% per month (non-compounding). Desire pays the interest at the end of each month when interest is incurred.

It needs to be inputted into:

F G H Budgeting Assumptions Total October sales Total January Year 2 sales Sales increase rate % Sales that are cash sales COGS as a % of sales Encing Inventory as a % of COGS Amount of Acc. Payable paid in month of purchase Amount of Acc. Payable paid in month after purchase Borrow/repay increments Interest Rate per month Cash from Iwwe stock Cash distributed as dividend Required cash cushion each month Salary Expense per month Supplies as a % of sales Commission as % of sales Monthly Rent Equipment Depreciation Utilities costs per month Misc. costs per month Cash Paid for Store Fixtures Sales Budget October November December Total otr Cash sales Sales on count Total budgeted sales October November December Total otr Schedule of Cash Receipts Current cash sales Plus collections from A/R Total collections 6. October November (Hint these do not all just get added across!) Totalar December Inwentary Purchases. Budet Budgeted cost of goods sold Plus desired ending inventory Inventory needed Less beginning inventory Required purchases on account) Schedule of Cash Payments Budget for Inventory Purchases October November December Total-Otr Payment of current month's A/P Payment for prior month's A/P Total budgeted payments Selling and Administrative Expense Budget October November December Total Otr Salary expense Sales commissions expense Supplies expense Utilities Expense Depreciation expense on store fixtures Rent expense Miscellaneous expense Total S&A expenses Oo f. November December Total otr Schedule of Cash Payments for S&A Expenses (Some cells may be zero.) October Salaries Sales commissions Supplies expense Utilities Depreciation on store futures Rent Miscellaneous Total payments for S&A expenses October November December (Hint-these do not all just get added across!) Totalar just a heading no input this line Cash Budget Beginning cash balance Issuance of stock Collections from customers Cash available Less payments For inventory purchases For S&A expenses Purchase of store fixtures Pay dividend Interest expense Total budgeted payments Cash balance before borrow/repay Financing activity Borrowing repayment) Ending cash balance just a heading-no input this line check math vertkally too F G H 101 102 BUDGETS H and I below must be completed in Blackboard, "Income Statement and Balance Sheet for Budget Project You can do them below, if it helps you. They will not be graded as part of the Excel submission part of the project. Pro Forma Income Statement For the Quarter Ended December 31, Year1 104 105 106 Sales revenue Cost of goods sold Gross margin S&A expenses Operating income Interest expense Net Income 114 Pro Forma Balance Sheet For the Quarter Ended December 31, Year Assets Cash Accounts receivable Inventory Store fixtures Accumulated depreciation Total assets I do 123 125 126 128 129 Uabilities Accounts payable Utilities payable Sales commissions payable Line of credit liability Tatal liabilities Equity Common stock Retained earnings Tatal equity Total abilities and equity 131 132 133 138 139 140 Retained Earnings (not praded, just as helpful tool) Beginning RE Balance Add Net Income Less Dividends Ending RE Balance