Welcome to Desire, Incl You have just been hired as the managerial account for the newly formed Desire, Inc. Desire will begin operations on October 19, 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%. 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 Soid for each month to be 72% of total sales for that month. Desire requires that ending inventory for each month be 20% of the next month's expected cost of goods sold. o Because Desire is set to begin operations on October 1" they did not have any beginning inventory at the beginning of October. 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 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 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. 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 14, they had no beginning cash balance at the beginning of October. o On October 1", Desire, Inc. collected $265,000 and issued stock to the owners. o On October 1", 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 31", Year 1. Desire has a policy that requires each month's ending cash balance to be at least $22,000. Desire's 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. PROJECT REQUIREMENTS Use the Budget Project Template (Excel spreadsheet) that is in the folder called "Budget Project in the "Chapter Details" area on the Blackboard page to complete the budget schedules. Schedules" through " must be completed for the Excel spreadsbeettiin portion of the project. DO NOT alter the structure of the template when filling in the budget schedules. You will input the information for your Pro-forma Income Statement and Balance Sheet schedules in the Blackboard assignment Income Statement and Biance Sheet for Budget Project". This will account for 40% of the project grade. You can retake this assignment as many times as desired until the due date. You will submit your completed Excel project template to another Blackboard assignment "Excel Template for Budget Project Submission. The Excel spreadsheet will account for the other 60% of your grade. There is a Blackboard assignment in the Budget Project folder called "Budget Project Check Answer where you can check your answers for select cells in the various schedules. This will not be graded and is just to help you check your progress. It can be taken an unlimited number of times. Both the Blackboard assignment and Excel turn in parts of the project are due at 11:59pm on Friday April 20 BONUS POINTS: Part 1: you will receive 10 extra credit points (this assignment is worth 50 points) fall the cells in the schedules use cell references, meaning there are no hard-coded cells The only cells that can be hard coded are cells that contain zero or the cells in the "Borrowing (Repayment) row in Schedule G (Cash Budget). Cell referencing for all cells can be done by hard-coding numbers in the Budgeting Assumptions area above schedule A and referencing those cells in the budget schedules. Using cell references for some but not all the cells (except for those cells mentioned which can be hardcoded) will get you only 2 extra credit points. Part 2: you can earn an additional 5 extra credit points if you can successfully code your spreadsheet so that when I change the sales increase rate in the assumptions area of the spreadsheet, all of your budget numbers automatically adjust to the new, correct budget numbers based on that different sales increase rate. This means that you will have to include coding in the "Borrow (Repayment)" row in Schedule G. When I grade your spreadsheet, I will change the sales increase rate to 21% and I will check to see if the ending cash balance for the end of the quarter (in Schedule Gy correct. There will be a question in the "Budget Project Check Answer where you can check to see if you have the correct ending cash balance for the quarter when the sales increase rate is Budgeting Assumptions Total October sales Total January Year 2 sales Sales increase rate % Sales that are cash sales COGS as a % of sales Ending 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 Issue 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 October November December T otal-Qtr Sales Budget Cash sales Sales on account Total budgeted sales October November December Total-Qtr Schedule of Cash Receipts Current cash sales Plus collections from A/R Total collections October Inventory Purchases Budget StudentTemplate November December (Hint- these do Total-Qtr October November December Total-Qtr Sales Budget Cash sales Sales on account Total budgeted sales 36 b. October November December Total-Qtr Schedule of Cash Receipts Current cash sales Plus collections from A/R Total collections October November December (Hint- these do not all just get added across Total-Qtr 46 Inventory Purchases Budget Budgeted cost of goods sold Plus desired ending Inventory Inventory needed Less beginning inventory Required purchases (on account) 50 d. November December Total-Qtr Schedule of Cash Payments Budget for Inventory Purchases October Payment of current month's N/P Payment for prior month's A/P Total budgeted payments 56 e. Selling and Administrative Expense Budget October November December Total-Qtr Salary expense Sale rommiceinns evnence Student Template 56 e. Selling and Administrative Expense Budget October November December Total-Qtr Salary expense Sales commissions expense Supplies expense Utilities Expense Depreciation expense on store fixtures Rent expense Miscellaneous expense Total S&A expenses 68 . December Total-Qtr Schedule of Cash Payments for S&A Expenses (Some cells may be zero.) October November Salaries Sales commissions Supplies expense Utilities Depreciation on store fixtures Rent Miscellaneous Total payments for S&A expenses 81 6. October November (Hint these do not all just December / Total-Qtr Cash Budget Beginning cash balance Issuance of stock Collections from customers Cash available StudentTemplate October November December Total-Qtr 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 vertically too 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 h. Sales revenue Cost of goods sold Gross margin S&A expenses Operating income Interest expense StudentTemplate 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 Sales revenue Cost of goods sold Gross margin S&A expenses Operating income Interest expense Net income 14 L. Pro Forma Balance Sheet For the Quarter Ended December 31, Year1 no input PINNA Assets Cash Accounts receivable Inventory Store fixtures Accumulated depreciation Total assets no input 128 Liabilities Accounts payable Utilities payable Sales commissions payable Line of credit liability StudentTemplate 129 Pro Forma Balance Sheet For the Quarter Ended December 31, Year 1 no input Assets Cash Accounts receivable Inventory Store fixtures Accumulated depreciation Total assets no input Inimilsin NO ON Liabilities Accounts payable Utilities payable Sales commissions payable Line of credit liability Total liabilities Equity Common stock Retained earnings* Total equity Total liabilities and equity no input Retained Earnings (not graded, just as helpful tool) Beginning RE Balance Add Net Income Less Dividends *Ending RE Balance 41