60.1K

Verified Solution

Link Copied!

Prepare an Excel spreadsheet with a budget for Cupcakes$-$Palooza Inc. for $2023$ using the assumptions below. Your assignment will be to produce a spreadsheet with the sales, expenses, and net income portions for $2023.$ Instructions for the spreadsheet: $1)$ Include a $3$ line header consisting of the name of the company, the title of the report, and the date period, on each sheet. $2)$ The first sheet should contain the budgetary assumptions, and the second and third sheets should contain the master budget. Name the sheets $($i$.$e$.$ don$$t just leave them as $$Sheet $1$ and $$Sheet $2.$ Whenever possible, make the assumptions on the first sheet cells that you can reference when you fill out the second sheet! $3)$ The budget is monthly for $2023,$ and the Fiscal Year is the same as the calendar year. Make each month of $2023$ a separate column in the spreadsheet. Assume this is the first year of operation, so there were no sales or expenses in $2022.4)$ Calculate a Gross Profit line $($Sales less Cost of Goods Sold$).5)$ Produce both an accrual budget and a cash budget on separate spreadsheets. For example, the second sheet can be the accrual budget and the third sheet could be the cash budget. $6)$ Break out expenses into fixed and variable expenses, and total separately $($i$.$e$.$ have a line that has $$Total fixed expenses$$ and $$Total variable expenses$).$ For simplicity, you can combine variable expenses and semi$-$variable expenses into a $$variable expense$$ category. $7)$ Calculate a Net Income Before Taxes line. Then, calculate taxes, and calculate a Net Income After Taxes line. $8)$ Make the spreadsheet easy to read and attractive looking! Be sure to underline totals and double underline the bottom line $($net income after tax$).$ Insert notations or comments as required to make the budget easy to understand. $9)$ Add a cell at the bottom of the accrual and cash budgets that totals up all Net Income After Tax for the year $($i$.$e$.2023$ Total Net Income After Taxes$).$ Assignment Assumptions: $1.$ Cupcakes$-$Palooza has a contract to sell $1,000$ cupcakes per day $(5$ days per week, year$-$round$)$ to area grocery stores. The stores pay $$1\mathrm{.}25$ per cupcake. $2.$ All sales are on credit and it collects $75\%$ of all sales in the month of the sale, $15\%$ in the month after the sale, and $10\%$ the second month after the sale. $3.$ It costs Cupcakes$-$Palooza $$0\mathrm{.}40$ in direct materials and labor to sell the cupcakes to its customers. That is the company$$s cost of goods sold. $4.$ The company pays its sales force a commission equal to $5\%$ of sales. $5.$ The company also believes that its supplies expense is equal to $$820$ per month plus $1\%$ of sales. $6.$ Advertising is $$700$ per month, Depreciation is $$2,000$ per month, and Utilities are $$1,400$ per month. $7.$ The company purchased an insurance policy covering $12$ months for $$12,000$ on January $3.8.$ All selling and administrative expenses are paid in the month they are incurred except for commissions that are paid in the month after they are earned and the insurance which is paid one year in advance. $9.$ The company is subject to a $26\%$ income tax rate. The company pays income taxes in the month after they are accrued $($expensed$).$

Answer & Explanation Solved by verified expert

See Answer