8. Pocket Pieces pays quarterly dividends of $1,500. 9. Pocket Pieces will purchase additional equipment on the following schedule (depreciation is already included above): 10. Pocket Pieces has to maintain a minimum cash balance of $10,000. Any projected borrowings are assumed to be borrowed at the beginning of the quarter. Payments are made at the end of the following quarters to the extent cash is available. All borrowings and repayments are made in $1,000 increments. Interest is paid at the time of repayment. Interest is calculated at 10% per year (no compounding). Excel Dashboard After the master budget is prepared, your team is thinking that it might be helpful to Raina and Marcelo to prepare an Excel dashboard. A dashboard transforms the budget data you have into something visual that's easier for people to grasp and utilize in decision-making. After all, what's the point of preparing a master budget if Raina and Marco are lost in a sea of numbers and don't see the overall trends impacting their business, especially when it comes to revenues, expenses, profits, and cash. Follow these steps to get your team's Dashboard set up. To create a dashboard, you will need three separate sheets in your Excel file. 1. Name your first sheet (the one that has your master budget on it) with something that makes sense, such as "Budget" or "Master Budget." 2. Next, create a second sheet labeled "Chart Data." That tab is where you'll store only the data that needs to be fed into different charts for the dashboard. 3. Last, create a sheet labeled "Dashboard" where visualizations will appear. Your team should create two visualizations in your dashboard to help you communicate important information from the master budget to Raina and Marcelo. For each visualization in your dashboard, write a sentence or two below it to inform Raina and Marcelo how your group interprets the visualization. You will use the Chart Feature in Excel to create your dashboard. There are many possibilities here - so be thoughtful, explore, and have fun! There are bar charts, column charts, pie charts, line charts, scatter plots, and many more. Keep in mind that not all of them will be the best at visualizing the data. For example, a line chart is great for analyzing trends over time but a pie chart is more effective for looking at the composition of something at a point in time. If you are not sure which chart is the best choice, play around and think about it as a team. Generate a few different charts, look at them, and judge which ones make the most sense for displaying an essential part of the budget in a manner that is easily understood. When creating a visualization, you may not need to use the whole master budget at once-you'll need to focus on only the pieces you need at that time. The best way to do this is by copying and pasting the budget you are interested in (say, the income statement) in the Chart Data sheet that you created. Then, use the "Filter" option in Excel. To do that, highlight the entire budget you are visualizing (say, for should include one Excel file with the master budget and 2 dashboard visualizations, along with a sentence below each visualization that provides your group's interpretation. Here is the information you need to get started: Your team is helping to prepare Pocket Pieces, Inc.'s quarterly budget for the year 2024. Pocket Pieces, Inc. has this balance sheet at the beginning of the year: 1. Your team needs to start with the sales budget. The units are expected to sell for $50 each. Pocket Pieces sells all merchandise on credit. Historically, Pocket Pieces receives 55% of each quarter's sales during the quarter and 45% in the next quarter. 2. Pocket Pieces plans to stock ending inventory of finished goods to equal 25% of the next quarter's sales. Finished Goods inventory at 1/1/2024 is 190 units at a cost of $24.25 per unit. Pocket Pieces uses FIFO to account for finished goods inventory. 3. The units are made with a high-quality resin and paint. The units each require 5 ounces of resin at a cost of $0.75 per ounce. In addition, the units require 2 ounces of paint at $0.40 per ounce. At the beginning of 2024, Pocket Pieces had 425 ounces of resin on hand and no paint on hand. Pocket Pieces plans to keep 30% of the resin and paint required for next quarter's production in inventory at the end of each quarter. Pocket Pieces pays for all purchases 45% in the quarter of purchase and 55% in the quarter following purchase. 4. Each unit requires 0.8 hours of direct labor at $20 per hour. Wages are paid on the last day of each month for that month's work. Other costs are paid in the quarter unless otherwise noted. 5. Variable overhead is estimated at $2 per direct labor hour. Fixed overhead costs are $32,000 per year. Fixed overhead includes depreciation of $7,000 per year. 6. Variable selling and administrative costs are $1.75 per unit. Fixed selling and admin costs are $39,000 per year. (This includes $3,000 of depreciation per year.) 7. Pocket Pieces makes quarterly income tax payments of $6,000 each. The corporate income tax rate is 20% of net income before taxes (consider any overpayments or underpayments on your balance sheet). Your team was recently at a party with Raina and Marcelo where they were doing some soul-searching about whether they should keep going with Pocket Pieces, Inc. or try to get hired by a "big" firm. As accounting majors, your team knows these friends need a formal plan. You can see that Raina and Marcelo have their hearts invested in this business, so your team agreed to help them complete a master budget for the coming year. Your team even agreed to help them out pro-bono - because Here, We Do! Your team will prepare a master budget for the year 2024 for Pocket Pieces, Inc. This includes: 1. sales budget 2. expected cash collections schedule 3. production budget 4. direct materials purchases budget (one for each DM) 5. cash purchases budget 6. direct labor budget 7. manufacturing overhead budget 8. ending finished goods budget 9. selling \& administrative expense budget 10. cash budget 11. budgeted income statement 12. budgeted balance sheet Each of the budgets above for the year 2024 and should be broken down by quarter, except for the budgeted income statement and budgeted balance sheet. For example: Prepare your master budget in Excel, using formulas wherever you need to perform a calculation. No credit will be given if you do not use formulas. This project is open book and open notes. This means you may consult your friends and colleagues and any books, online sources, or notes you may have. I am here to provide guidance and answer questions about the project, but I will not check any budgets prior to submission to be fair to all groups in the class. Each student should contribute to the group in good faith. Remember that each student will fill out a self \& peer evaluation located in the Week 10 content folder on D2L. Part of your grade this quarter is your participation on this group project. Your team is counting on you! Please turn in only one project per group under the Submissions Tab on D2L by the project deadline. Be sure to include team members' names, section number and group number on your file. Your submission example the income statement), click the "Data" ribbon in the toolbar, and then click "Filter". You will see that little arrows appear next to your column headers. If you click one of those arrows, you'll be presented with a drop-down menu that you can use to filter your data and select only the line items you want to include. ACC 303 MASTER BUDGET GROUP PROJECT Pocket Pieces, Inc. Raina and Marcelo became friends during their college years and bonded over their love of art, particularly all of the public units and sculptures in Chicago. They both recently graduated from DePaul University. After graduation, Raina and Marcelo decided not to seek jobs at established organizations, but rather, to start their own small business, Pocket Pieces, Inc. Their business produces small replicas of art sculptures, sized to fit in a pocket that can be displayed on a desk or bookshelf or used as a hanging holiday ornament or a keychain. So far Pocket Pieces, Inc. produces just one product - a pocket-sized replica of a unit that can be found at St. Vincent's Circle on DePaul's Lincoln Park campus. The unit features a life-sized bronze unit of St. Vincent de Paul talking with two students. Each unit is molded using a high quality resin and then hand painted, including an inscription that reads, "Community Dignity Education." Raina and Marcelo chose this unit to launch their business because St. Vincent's Circle is one of their favorite places, always providing them a reflective, calm atmosphere. Current students and fellow alumni purchase their Pocket Piece and proudly display it on their keychain, office desk or shelf, or on their holiday tree as a token of affection for DePaul and a reminder of the Vincentian values that they hold dear, long after graduation. Raina and Marcelo, not sure about how their business would perform, thought that after a couple of years if their business was successful on a small scale within the DePaul community, they would potentially expand to produce pocket sized replicas of other art sculptures found all over the city. For now though, they sell Pocket Pieces at various locations around the Lincoln Park and Loop campuses, including the DePaul bookstores, the Student Center, and at pop-ups during large University events such as basketball games and other sporting events, the Christmas at DePaul event, graduation, and convocation. In addition to in-person sales, they also launched a website where customers can purchase 24-7. Together, Raina and Marcelo pooled assets together to fund their business. They took money from their personal savings that they had accumulated while working part time during college and they also received a couple of cash gifts from various relatives. It really paid off when they each gave a "Shark Tank" type of pitch at Thanksgiving dinner during their senior year at DePaul! Hey, they figured it was better than talking about politics. With the start-up funds, Raina and Marcelo purchased a little art studio, some equipment, materials, supplies, and paid administrative fees for items such as a business license. Raina thinks they should prepare a master budget for the upcoming year. While Raina and Marcelo are gifted artists, they are struggling a little with the accounting. They know they need to come up with a sales budget by forecasting how many units they believe they will sell in each quarter of the upcoming year. They met and agreed on the following projected sales levels: 1