ACCT$\_3500$ Group Project Page $1$LONGO FACULTY OF BUSINESS$\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_$ACCT$\_3500\_$OLA Winter $2024$HR Managerial AccountingGROUP PROJECTGRADE: $15\%$ OF THE TOTAL GRADEDUE DATE: April $04,2024,$ by midnightPROJECT TOPIC $-$ Preparing and Analyzing the Master Budget $($using Excel$)$The demand for college graduates with data analytics skills has exploded, while the tools andtechniques are evolving and changing rapidly. This case illustrates how data analytics can beperformed using various tools including Excel, Power BI$,$ and Tableau. As you analyze thiscase, you will be learning how to use EXCEL to drill down into a company$$s sales and cost datato gain a deeper understanding of the company$$s sales and costs and how this information canbe used for planning and decision$-$making.Students, please note that the penalties for cheating $($copying$/$plagiarism$)$are severe.MASTER BUDGET FACTS AND FIGURES:You have just been hired as a management trainee by Toronto$-$based Mister Cap Fashions, anationwide distributor of designer Caps. The company has exclusive distribution of the Caps,and sales have grown so rapidly over the last few years that it has become necessary to add newmembers to the management team. You have been given responsibility for all planning andbudgeting. Your first assignment is to prepare a Master Budget for the next $12$ months startingJanuary $1,2024.$ You are anxious to make a favorable impression on the president and haveassembled the information below.The company purchases the Caps from a factory in Montreal which costs the company $$5\mathrm{.}00$each $($all on account$).$ Purchases are made based on the current month$$s sales in units plus an

ACCT$\_3500$ Group Project Page $2$ending inventory to equal $30\%$ of the next month's sales in units. Purchases are paid for $40\%$in the month of purchase and the remaining $60\%$ in the following month.The Caps are sold at wholesale to other resellers as well as through own stores to individualcustomers. $70\%$ of total sales, all on account are made as wholesale for $$8\mathrm{.}00$ each and $$15\mathrm{.}00$each to the individual customers in the mall stores $(30\%$ of total sales, all in cash$).$ Recent andforecasted total sales in units are as follows:Months Units Months UnitsOctober $2023$ Actual $20,000$ January $2024$ Budgeted $25,000$November $2023$ Actual $24,000$ February $2024$ Budgeted $20,000$December $2023$ Actual $26,000$The company has found that only $40\%$ of a month's credit sales are collected in the following month,and the remaining $60\%$ is collected in the second month following the sales. Bad debts have beennegligible.The company is planning to open a new store in a shopping mall in March $2024$ and expects to increaseits sales by $10\%$ every month from March to July $2024$ and then decline by $10\%$ during August,September, and October $2024$ and afterward the sales will be unchanged.Opening the new store will cost the company $$40,000$ per month in advertising during February andMarch $2024.$ Furniture and Fixtures will cost $$80,000$ during February payable in $30$ days. The companyis planning to buy sales equipment costing a total of $$240,000$ in February, payment in $3$ instalmentsstarting March and every $3$ months thereafter. The rent for the new store will be $$6,000$ a month startingFebruary $2024.$ A new Sales Manager will be hired at a monthly salary of $$5,000$ per month and he willstart in March $2024.$ The additional costs of running the new store will be $$4,000$ per month when thestore opens. To cover all the extra costs due to the new store, the president of the company decided toprovide an interest$-$free loan of $$50,000$ to the company on February $1,2024,$ with the condition that thecompany will repay the loan in full at once whenever there is sufficient cash balance available after thebank loan is repaid.The company's monthly operating expenses are given below:Variable cost:Sales Commission $($retail stores only$)$ $$1\mathrm{.}50$ per Cap sold.Fixed Costs:Wages and Salaries $$40,000$Utilities $5,000$Insurance $1,600$Depreciation $4,000$Office rent $7,000$Miscellaneous expenses $3,500$All operating expenses are paid during the month in cash. The company pays a salary of $$8,000$ eachmonth to the owner Bobby Paul, payable on the $1$st week of next month. The company's balance sheeton December $31,2023,$ is given below:

ACCT$\_3500$ Group Project Page $3$ASSETSCash $...............................................................$ $ $20,000$Accounts Receivable $($$$80,640$ from Nov. sales; $$145,600$ from Dec. Sales...... $226,240$Inventory $(7,500$ units$).........................................................37,500$Fixed Assets Net of Depreciation $..........................................250,000$Total Assets $........................................................................$ $ $533,740==========$LIABILITIES AND SHAREHOLDER$$S EQUITYAccounts Payable $..................................................................$ $ $77,100$Salaries Payable $..................................................................8,000$Capital Stock $...........$