Moria and Johnny Rose own Levy Jean Company. They sell unique styled jeans all over Canada. The sales price is currently $40 per pair and they sold 5000 jeans in Q4 of 2020.
LJC BUDGET TEMPLATE NAME: Note: No credit for hard coded #s where formulas should be used. SECTION: Create formulas in Excel to add/subtract/divide and link #s from one budget to the next budget. When a formula c >> See examples of proper use of Excel formulas and Linking in cells D8 and D14 Sales Budget Quarter 4 - 2020 Quarter 1 - 2021 Quarter 2 - 2021 Sales (units) 5,000 6,000 See Part A Price See Part A Revenue $ Production Budget (units only) Quarter 1 - 2021 Quarter 2 - 2021 Quarter 3 - 2021 Sales (units) 6,000 + Desired Ending Inv See Part B = Requirements - Beg Inv = Production Direct Materials Budget - Fabric Quarter 1 - 2021 Quarter 2 - 2021 Production X Amt Fabric per pair See Part D =Production Needs + Ending Inventory See Part L = Total Needed - Beg inventory See Part L = Materials to be purchased Total Cost @ $4.50 See Part CDirect Labor Quarter 1 - 2021 Quarter 2 - 2021 Production Labor Hours Used See Part E Labor Cost per hour See Part E Total Labor Cost Manufacturing Overhead Quarter 4 - 2018 Quarter 1 - 2019 Quarter 2 - 2019 Total Overhead See Part F Selling General & Admin Quarter 1 - 2021 Quarter 2 - 2021 Total SGA See Part G Less Non Cash Expenses See Part G Cash expenses Summary Cash Budget Quarter 1 - 2021 Quarter 2 - 2021 Beg Cash Balance See Part J Cash Receipts: Collections - Prior Collections - Current Cash Disbursements: Less Purchases: Fabric No Hard Coded #s Allowed in this section. Less Direct Labor Costs Link to cells from other budgets or use forn Less Manufacturing Overhead - Prior Less Manufacturing Overhead - Current Less SGA Investing/ Financing Activities: Less Cash Dividends See Part K Less Equipment Purchases See Part K Ending Cash Balance'- Moira and Johnny Rose own Levy Jean Company (LIC). They sell unique styled jeans all over Canada. The sales price is currently $40.00 per pair and they sold 5,000 jeans in Q4 of 2020. LJC purchases 1 type of raw materials (fabric). They purchase the fabric for $4.50 per yard. They have asked us to create a master budget and summary cash budget for the next two nancial quarters (2021 Q1 and 2021 Q2). Additional Information: A. Unit sales are expected to grow 20% in eachjndem quarter in 2021. The sales price is forecasted to remain at $40.00 per pair B. Ending inventory [of nished goods) must equal 20% of the next quarter' 5 projected sales. Beginning Q1 2021 nished goods inventory is 5,000 units. The jean material is purchased for $4.50 per yard . Each unit of production requires 2 yards of fabric. Employees that sew the jeans make $15 per hour. They can make 8 units per hour. Manufacturing overhead was $4,000 in Q4- of 2020, $6,000 is forecasted for Q1 of 2021 and $7,000 for Q2 of 2021. SGA is $90,000 per quarter [$5,000 of this represents depreciation). . LJC has cash collections of 75% of revenue in the quarter the product was sold. The remaining 25% the following quarter. 1. Due to the lack of credit, LIC must pay all bills in the quarter used/purchased. Overhead is the only exception. For expenditures that are for overhead, DCC pays 40% of them' in cash' in the quarter of purchase and 60% the following quarter. . LJC had a beginning cash balance of $45, 000 (12/31/2020). K. LJC plans to pay $55, 000 in cash dividends to investors' in Q1 of 2021 and purchase $85, 000 worth of equipment in Q2 of 2021. L. L C had the followin_ beg 8; ending balances for their raw materials. FFUP =5: REQUIRED: Complete a master budget using the Excel template provided. Use of formulas and linking is required to leverage the power of Excel and connect the detailed budgets. If you are new to Excel. a link to the Excel training videos is available on Canvas to learn how to create formulas and link cells: Excel Training Videos
